3 Replies Latest reply on May 14, 2016 2:27 AM by erolst

    Conditionally change calculation field based on "If" statement.

    RyanAshton

      Hello guys,

       

      So i have a product and services list.  Most of the products and services are supplied by other companies and I just add a % in the "HKDJ_%" field and include it in the cost estimate.  However, there are some services (and in the future, products) which are and will be supplied by my company "HKDJ Limited".

       

      I have a "Supplier" field where the supplier name can go.  I also have an "HKDJ_Item" calculation field [Cost_Price * HKDJ_%] and also a summary field called "HKDJ_Total" that adds all the "HKDH_Items' together.

       

      The problem is that when my company (HKDJ Limited) is the "Supplier", then obviously I don't put a percentage on top of the "Cost_Price" field and consequently, the "HKDJ_Total" field result is inaccurate.

       

      Basically, if the "Supplier" = "HKDJ Limited" then I want the "HKDJ_%" to be 0 (zero) (and it can't be changed) and I want the "HKDJ_Total" = "HKDJ_Item * Quantity", else I want "HKDJ_%" to = what was originally entered and I want "HKDJ_Total" = "Cost_Price * Quantity"

       

      I hope that makes sense.

       

      I tried it out myself (see below), but the fields are displaying "?".

       

      Screenshot 2016-05-14 15.46.34.png

        • 1. Re: Conditionally change calculation field based on "If" statement.
          bigtom

          Which table are you evaluating the calculation from?

          • 2. Re: Conditionally change calculation field based on "If" statement.
            RyanAshton

            Those calculation fields are in "Table_Estimate_Line_Items".

            • 3. Re: Conditionally change calculation field based on "If" statement.
              erolst

              RyanAshton wrote:

              I have a "Supplier" field where the supplier name can go.

              1. Aren't you using IDs for your relationships? If not, you should!

               

              RyanAshton wrote:

              Screenshot 2016-05-14 15.46.34.png

               

              2. You don't define a calculation in a calc field in terms of that field itself. (Unless you're trying to hack a recursive function.)

               

              Also, you can't set (change the value of) another field here: in calcField = someOtherField = someValue, that latter part doesn't work as an assignment, but is simply an expression that evaluates to True or False, i.e. 1 or 0.

               

              For a calc field that multiplies fields a and b, write a * b, not calcField = a * b; it's implicit that you want to set that field (in fact, if you look more closely, it says so above the calculation area : fieldName = ).

               

              3. You shouldn't reference prices, markups and similar, time-in-point dependent data; rather, look them up and store them in a regular field.

               

              You could try

               

              Case (

                IsEmpty ( Self ) ; // especially on record creation

                  Supplier::HKDJ% ; // put in a value of 0 for your own Supplier record

                Case ( // here we know it is not empty

                  Supplier::id = yourOwnID ;

                    0 ; // always reset for own

                    Self // keep for others

                )

              )

               

              untested