Lookup Price based on multiple criteria
Hey all, i'm very new to Filemaker so please forgive me if there is an obvious answer to my issue.
I'm customising FM Starting Point in Filemaker Pro 10 (mac) for use in my organisation.
All fairly simple except for the pricing of products. we make custom products and the pricing is dependant on 4 factors Type, Finish, Height & Width.
Height and Width of custom parts are entered in invoice line items.
As the size combinations are infinate (down to the millimeter) we use pricing brackets. Combinations of height and width price brackets create a single price dependent on which type and finish is used.
I have this all in a table as follows:
TYPE (4 OPTIONS)
FINISH (8 OPTIONS)
MAX HEIGHT (10 OPTIONS)
MAX WIDTH (10 OPTIONS)
the result is a table with 3200 prices in it.
How do i get Filemaker to select the correct price based on multiple criterea & rounding up of height and width?
Each invoice line item has Type, Finish, Height and Width defined.
so I need to perform a lookup for the UNITPRICE field, that searches the PRICING table FIRST matching by Type, THEN matching by Finish, then rounding up to max height (next highest), then rounding up to max Width (next highest). to tell me the item price.
I've tried creating multiple self defined relationships for the table but either i'm doing it wrong or its the wrong approach.
Your help is much appreciated.