Skip to main content
Question

Case Formula not working

  • December 27, 2019
  • 0 replies
  • 31 views
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
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+24
Is the spelling correct for "Commodity1", maybe missing a space perhaps



------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
Mark Shnier. Your Quickbase Coach
Forum|alt.badge.img+19
  • DonLarson
  • Qrew Cadet
  • December 27, 2019
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
------------------------------
Terms & ConditionsAccessibility statement