Discussions

 View Only
  • 1.  Case Formula not working

    Posted 12-27-2019 15:17
    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
    ------------------------------


  • 2.  RE: Case Formula not working

    Posted 12-27-2019 15:27
    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
    ------------------------------



  • 3.  RE: Case Formula not working

    Posted 12-27-2019 15:31
    It is actually fruit names and I thought of that so I went to the master table and copied them directly from there.

    ------------------------------
    Jeannette Jeannette
    ------------------------------



  • 4.  RE: Case Formula not working

    Posted 12-27-2019 15:37
    I don't see anything wrong with your formula so it should be working.

    But if you are desperate, you could rewrite the formula like this

    IF(
    Contains([Commodity], "apples", [Pallet Count]*[Price per Unit],
    Contains([Commodity], "peaches", [Pallet Count]*[Price per Unit],
    [Case Count]*[Price per Unit])



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Case Formula not working

    Posted 12-27-2019 16:32
    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
    ------------------------------