I suppose that your 'order amount'-field is of the type 'number'.
You could set the 'auto-enter' option of that field to 'calculated value'.
The calculation could then be to retrieve (=function 'lookup') the appropriate 'set-price' from the related 'product'-table based on a selection made with a simple 'if'-function.
Ticking the box 'do not replace existing values ...' would have the additional benefit that prices on past orders will not change if you change your price in the product file.
Let me know if this doesn't make sense.
Thanks for the post...an interesting one...
If real life is as simple as you describe it (no sets of 4,5,7,,,300,,,45000, etc.) and there are few products, you could do something as direct as having a "PricePer" field be a calculation field with nested If statements. This is not easy to maintain, so I would only consider it if there are very few permutations.
Another way is to double-relate two tables. If your "Orders" table were joined with your "PricePer" table by both Orders:: ProductName AND Orders::Qty, there would/should/could be a record in the PricePer table that gave a Price per unit.
If you want widgetA, Qty=1 it would join to a record, but if you want WidgetB with a Qty=3, it would join to a different record because the relationship is dependant on both fields.
Does that make sense to you? Your Orders::Product Name would of course be a value list based upon Products::Name field to avoid typos and possibly risk breaking the links to PricePer...
Absolutely wright Ninja,
i understood from the posting that only 3 different prices were possible and didn't want to complicate things ;o)
Hi Ninja, Hi St Peter,
Thanks for your input!
I solved the problem with "auto-enter Calculation"; my calculation looks like this:
Case ( Order_Quantity="1"; Products::Retail_Price_1_Set;Order_Quantity="2";Products::Retail_Price_2_Set;Order_Quantity="3";Products::Retail_Price_3_Set)