7 Replies Latest reply on Nov 9, 2010 8:19 AM by FentonJones

    relating columns and rows

    RicBruce

      Title

      relating columns and rows

      Post

        I am quite new to Filemaker.  I have lots of excel spreadsheets which are like the following.  If someone could point the way in which I can relate the cells to there respective row and column.  I wish it was a simple formula but as the part gets larger it is cheaper. This is only an example of the spreadsheet.

      width->

      1

      2

      3

      1

      10

      19

      26

      2

      18

      26

      42

      3

      27

      39

      58

      Thanks

        • 1. Re: relating columns and rows
          RickWhitelaw

          It's a different way of thinking . . . that is, using a relational database like Filemaker as opposed to a spread sheet. As you read the manual, experiment and learn, think of the "rows" as records of a table, and "columns" as fields in the table. Sorry I can't be of more assistance given your example.

          RW

          • 2. Re: relating columns and rows
            RicBruce

            I am sorry.  I was unclear in my post.  I already know how import the pertinent excel files.  I just dont know how to set up a script or calculation to access the data.  For example,  if I enter 1 for width and 3 for length I should receive an answer of 27.  How do I enter this into my database?  I have approx. 200 such spreadsheets to enter.  I am just unfamiliar in how to have the database access the info in a particular cell in the manner specified above.

            Thanks,

            Ric

            • 3. Re: relating columns and rows
              david583

              You need calculation fields.

              It looks like each product has a different multiplier depending on it's width?

              You could use a case function. i.e.

              case(

              product1= width1 ; product1 base price * multiplier1;

              product1= width2 ; product1 base price * multiplier2;

              product1= width3 ; product1 base price * multiplier3 ;

              etc;etc;

              DefaultValue )

              You would need this for each different product as a calculated field.

              Case statements can have many choices, not limited to 3, which makes them much easier than nested if statements.

              You could go a step further and nest some case statements to test for the product. That way you could enter the product into one field, it's width into another and the third field should give you a price.

              Hope this Helps

              David

              • 4. Re: relating columns and rows
                RicBruce

                Thanks for the responses.  I have posted an actual pricing guide.   I would like to enter a product type in a field.  Enter the width and length and get a price.  If I enter 26 x 38 it returns a price of $94.85.  It rounds 26 to the next column which is 30 and goes down to 42 (which is 38" rounded to the next row of 42). 

                Size 24" 30" 36" 42" 48" 54" 60" 66" 72" 78" 84" 90" 96" 102" 108" 114" 120"
                30" 56.00 74.20 92.40 100.80 108.50 122.50 137.90 149.45 154.00 161.70 172.20 182.00 193.20 224.35 235.20 245.70 256.55
                36" 65.45 84.35 102.55 112.35 121.10 140.70 157.15 170.45 175.35 182.00 191.80 204.40 215.60 249.20 260.05 270.90 281.40
                42" 75.95 94.85 112.70 124.60 134.75 157.50 177.45 187.25 197.05 205.45 217.00 232.40 245.35 281.75 292.60 303.45 314.65
                48" 84.00 105.00 123.55 137.20 149.45 172.55 193.20 211.40 221.55 230.65 243.60 262.85 277.20 316.75 327.60 338.45 349.30
                54" 92.40 113.40 134.40 149.80 163.10 191.80 212.80 234.85 248.15 259.35 274.75 292.25 311.15 354.20 365.05 375.90 386.75
                60" 101.85 123.20 146.30 161.35 178.15 209.30 231.00 254.45 269.85 281.75 299.25 319.20 337.75 383.60 394.45 405.30 416.15
                66" 110.25 132.65 156.80 173.95 195.65 229.25 253.05 279.65 297.50 310.10 327.95 348.60 408.80 461.30 472.15 483.35 494.20
                72" 120.75 141.75 167.65 188.30 213.15 249.20 274.40 297.15 315.35 329.35 348.95 373.10 445.90 502.60 513.45 524.30 540.05
                78" 126.35 144.90 172.20 193.90 218.75 255.15 281.40 306.95 337.40 352.80 374.15 399.70 483.00 543.20 554.05 603.40 626.15
                84" 135.10 155.40 183.40 207.90 235.90 269.50 298.55 327.95 362.25 378.70 403.90 486.15 519.75 584.15 596.40 633.85 712.25
                90" 142.10 164.50 194.25 220.85 249.55 284.90 322.35 348.25 383.95 399.70 489.65 523.60 556.85 602.70 631.75 663.60 742.35
                96" 147.35 169.75 200.20 229.95 260.05 299.25 329.35 372.40 408.80 489.65 529.20 569.45 609.70 633.85 678.30 719.25 776.30
                102" 168.00 193.20 228.20 261.10 297.50 345.10 378.70 436.10 479.85 563.15 607.60 651.00 704.90 738.50 772.10 798.35 828.45
                108" 172.90 198.80 235.90 268.45 308.00 359.10 393.40 459.90 507.50 585.55 629.65 659.75 716.10 760.90 791.00 821.10 869.75
                114" 178.50 204.40 243.25 275.80 318.15 373.45 408.10 484.05 535.50 608.30 633.85 678.30 723.45 765.10 798.35 840.00 892.15
                120" 183.75 209.65 250.95 283.50 328.65 387.45 422.45 507.85 563.15 619.50 648.55 701.05 746.20 802.55 851.20 881.30 914.90
                • 5. Re: relating columns and rows
                  david583

                  Not quite how I thought, but it should work.

                  case(

                  30"= 24 ; 56.00;

                  30"= 30 ; 74.20;

                  30"= 36 ; 92.40 ;

                  etc;etc;

                  DefaultValue )

                  It would be long winded by the time you do all your products though.

                  Is there a consistant math formula to derive the price? That would make things simpler.

                  David

                  • 6. Re: relating columns and rows
                    FentonJones

                    I think 4 tables: Width, Length, and Prices are "reference" tables, with the data you posted above; then one actual "data" table. Width and Length have the 1st row and 1st column you have above, respectively; really just one field in each. Prices is like a join table, with 3 fields; Width, Length and Price. It is all the data combinations of Width and Length tables, and the Price for each combo; like your matrix above.

                    The main function of the Width and Length tables is to "round up" the actual width and length you enter in the data entry table, into another "rounded" field for each. You'd could use a Lookup, with "If no match, use next higher"; or it could be an auto-enter calculation using the Lookup() function, which has the same option. So 26 is looked up as 30, 38 as 42.

                    You then have 2 values which together match an entry in the Prices table. The records in the Prices table could be created by a Loop taking the 1st value of Width and the 1st value of Length, creating a record; then the 2nd value of each, etc.. You could use a relationship on a serial id, or just GetNthRecord.

                    The same thing could be done via a (long) Case calculation. But I think the 2 tables is a better method, as it is easier to see and modify.

                    The data entry table will have a relationship to the Prices table based on BOTH Width and Length rounded. So 30 AND 42 would match that entry in the Prices table, returning 94.85. 

                    I think that would work. I'd create an example file, but it's tub time here in California :-] I'm not sure whether just entering data would lookup its rounded number, then also the price; it might need more triggering. But maybe it would work straight off.

                    • 7. Re: relating columns and rows
                      FentonJones

                      Here is an example of what I said above. It turns out that the final Price was an auto-enter, with [ ] "Do not replace existing value" unchecked. It also needed to test that both the width and length had a value before trying. 

                      I validated the Width and Length fields in the Entry table so you couldn't enter anything higher than what I had Prices for. You can remove/change that if you add more.

                      Lookup_Price_Matrix.zip