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
------------------------------
Original Message:
Sent: 12-27-2019 15:16
From: Jeannette Jeannette
Subject: Case Formula not working
I have a formula set up:
Case([Commodity],"Commodity1",([Pallet Count]*[Price per Unit]),"Commodity2",([Pallet Count]*[Price per Unit]),([Case Count]*[Price per Unit]))
Basically I have a table to table relationship set up where "Commodity" and "Price" is pulled from the child table to the master table. So, once the commodity is selected it fills in the price. To make sure that the total does not change if the price is changed in the future, I set up a snapshot field of the "Price" lookup field named "Price per Unit".
For all commodities except for 2, the total is determined by "Case Count" * "Price per Unit". For the other 2 commodities the price is "Pallet Count" * "Price per Unit".
For some reason it is not recognizing the 2 commodity names and is only determining the total using "Case Count" * "Price per Unit" no matter what commodity is selected. I have this same formula used in other tables and it is working fine.....not sure what I am doing wrong in this one.
If someone has any suggestions I would appreciate it.....need this to work ASAP.
------------------------------
Jeannette Jeannette
------------------------------