Title
Lookup two variables - FMPro 6 - having a mental block
Post
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 ...
etc.
... 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. :(
Amy
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.