AnsweredAssumed Answered

Lookup Price based on multiple criteria

Question asked by makeitcount on Feb 15, 2010
Latest reply on Apr 7, 2011 by lpnoires

Title

Lookup Price based on multiple criteria

Post

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:

TABLENAME: PRICING 

TYPE (4 OPTIONS)

FINISH (8 OPTIONS)

MAX HEIGHT (10 OPTIONS)

MAX WIDTH (10 OPTIONS)

PRICE 

 

the result is a table with 3200 prices in it. 

 

THE QUESTION:

 

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. 

Outcomes