1 Reply Latest reply on Feb 14, 2011 8:33 AM by philmodjunk

    Lookup two variables - FMPro 6 - having a mental block



      Lookup two variables - FMPro 6 - having a mental block


      I'm trying to add capabilities to an old relational database in Filemaker 6 (PC) and I've got brain-block on what should be a common procedure.

      I'm trying to lookup the US Postal Service Priority Mail postage rate based on two variables: weight of the package, and the postal zone of the shipment. I've created a separate Priority Mail.fp5 database that consists of:

      Record ID     Weight    Zone 1    Zone 2    Zone 3    etc.

      1                  1           $4.80      $4.85      $4.95     ...

      2                  2           $4.90      $4.99      $5.38     ...


      ... up to 70 pounds. I imported the data from the USPS website at http://pe.usps.gov/prices/csv/PriorityMail_Retail.csv

      So there are 70 records.

      The Order Entry database contains a field called Calculated Package Weight which is an integer weight in pounds. It also contains a field called Postal Zone which is a number from 1-8.

      I've created a relationship in the Order Entry database thus:

      Order Entry.fp5::Calculated Shipping Weight = PriorityMail.fp5::Weight

      and that's where I'm having "Coder's Block."  How do I grab the correct zone from the Priority Mail rate datebase that I've made? I thought I'd have an epiphany this weekend and shout "Eureka!" but it didn't happen. SO here I sit staring at my screen and thumbing through the FMP User Guide.

      Could some kind soul point me in the right direction? I think I must be taking a wrong approach. :(


        • 1. Re: Lookup two variables - FMPro 6 - having a mental block

          Look up all the zones into separate fields, then use a calculation field with a case statement to select the correct cost from the looked up "zone" fields. That's the easiest method given your table structure here.

          Case ( Zone = "Zone 1" ; Zone1Lookup ;
                    Zone = "Zone 2" ; Zone2Lookup ;
                   // and so forth.

          If you had each combination of zone and weight in a separate record, you could try a different approach that just looks up the one zone based cost for a given weight.