Define the relationship between LineItems and Pricing as:
LineItems::Type = Pricing::Type
LineItems::Finish = Pricing::Finish
LineItems::Height ≤ Pricing::MaxHeight
LineItems::Width ≤ Pricing::MaxWidth
On the Pricing side of the relationship, sort the records by MaxHeight and MaxWidth - both descending. This will make the closest matching record the first related record - and the source for the price lookup.
Ha! how easy was that.
Worked a treat...Many thanks for your help!
Lookup Price based on multiple critera : Having read previous various other posts I concluded , for my personal purpose, that I needed, besides my product table enclosing in it item code and the price per unit, another table enclosing the various quantities discounts for each code of item, (one line of record with code of item, quantity minimum, quantity maximum, discount).
In my line items table, with the relationship many to one with the products table, a look up gives me the relevant informations about the item, including it's price. Works fine.
To get, in my line items table, the quantity discount and compute the correct extended price based on the quantity, I guessed I need a relationship based on the item code between the line items table and the quantity discount table, allowing the use of a look up. Drawing the relationship I get, between the line item table and the quantity discount table, a many to many relationship.
Putting it to word, no show of quantity discount in the line items table, and looking back at my readings did not gave me answers.
What would you suggest keeping the two tables, table product, and table quantity discount, to make it work ?