Jeanette,
I am confused by your statement that
- "Commodity" and "Price" is pulled from the child table to the master table
Do you mean that the child table inherits these from the Master?
If so I suggest adjusting your architecture with one more table called Pricing Strategy
LU means that these are Look Up fields in the Relation Ship
Case Count is a Numeric Field in Orders
Pallet Count is a Numeric Field in Orders
Now put two records into the Pricing Strategy Table
RID 1 Case Count
RID 2 Pallet Count
Now all of your Products will have one of those Pricing Strategies
In the Orders table your formula becomes
Case ( [Related Pricing Strategy],
1, [Case Count] * [Price per Unit Snapshot]
2, [Pallet Count] * [Price per Unit Snapshot] )
The beauty of this is that SPELLING DOES NOT MATTER. Nobody cares anymore if it is Potato, Potatoe, potato or Potats.
Also you have great flexibility to expand later as other strategies.
If you are doing a reverse look up of some kind and driving values from a child table to a parent, please ignore all of this.
------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------