I need to be able to lookup a value from a connected parent table – but I want the value from the parent table to be copied as a static or fixed value into a field in the child table.
For example, say there are three tables:
- ProductOptions, and
User/Customer selects “Acme Beachball” as the product. Beachballs come in three colors, which are represented by records in the ProductOptions table. The colors are Red, Orange and Yellow. Orange and Yellow both cost $12.50,but Red costs $15. I want user filling out form to be able to pick the product, then pick the option – and end up with a static value in the ItemPrice field of the Order record.
I know how to create a dependent dropdown list (as explained in this video). But that doesn’t work for me. That creates a dynamic and permanent connection between the order record and the product option record. If the price changes for red beach balls, the price values in old orders all change too. Not good.
Can’t figure out how to do this. What am I missing?