5 Replies Latest reply on Feb 5, 2015 8:40 AM by EwanMacGregor

    If function problem



      If function problem


      I've put together a table of products that we sell into the reseller market. Currently there are two manufacturers products listed.

      I want to give our resellers different discounts depending on which product.

      In the table I have a trade price field. I want to say If the product comes from manufacturer x then the discount is say 30%. If the product is from manufacturer y then the discount is 40%.

      I have tried to put an initial calculation in the Trade Price field for one of the manufacturers

      If ( manufacturer ; Yellow;30;0)  however this doesn't work. I get an error message saying. I think for the second manufacturer I need a nested If.

      I would appreciate some help here.




        • 1. Re: If function problem


          The problem may be with your syntax.  "If ( manufacturer ; Yellow;30;0)" isn't correct if that is exactly how you typed it.

          The syntax is IF(Test, Result1, Result2) or IF(Test; Result1, Result2) - you can use either a "," or a ";" to separate the arguments.

          Your example then would be "If ( manufacturer = Yellow;30;0)" replacing the ";" between manufacturer and yellow with an "=".

          The "Test" compares two conditions on each side of the "=" sign (you can use fields, formulas, and functions as part of the conditions), if the results is True then Result1 is performed, if the result is False then Result2 is performed).



          If you have more than binary outcome (either this or that) instead of thinking of "nested ifs" think about use a CASE calculation.

          The Case syntax is "Case ( test1 ; result1 {; test2 ; result2 ; ... ; defaultResult} )" - now what's that mean in English.  :)

          A case statement is basically a series of "IFs", the first one that returns TRUE has the resutl performed and the calculation stops.

          Let's say you have a various manufacturers that get different discounts so using a case statement you get:


          Manufacturer = "Red";10;

          Manufacturer = "Green";20;

          Manufacturer = "Yellow";30;

          Manufacturer = "Blue;40;


          In the above case calculation the value 10, 20, 30, or 40 (in that order) is returned if the Manufacturer is Reg, Green, Yellow, or Blue (in that order).  If the Manufacturer value were to be "Pink" then the value of 0 (the default) is returned since Pink doesn't match any of the tested for values.





          • 2. Re: If function problem

            Hi Mark,


            Thanks for getting back to me on this. Unfortunately it didn't help. I've attached a screen shot.

            There are several manufacturers in this field and I add their names from a look up table when adding a product. Would this cause a problem?



            • 3. Re: If function problem


              Enclose Ram Mount in quotes ( "Ram Mount" )

              When text is typed, if it is not enclosed quotes the system thinks you are referencing a field name.


              IF(Manufacturer = "Ram Mount";30;0)



              Just wondering, do you have multiple Manufacturers?  If you have multiple manufacturers wouldn't it make more sense to assign a "Manufacturer Grade" on their record.  Those with Grade "A" get 30%, those with Grade B get 25%, those with Grade C get 20%, etc., etc.

              If you are using actual manufacturer names and percentages, then if you add a new manufacturer you have to update the calculation.  If you want to change the % you have to change the calculation.  On the other hand if you use a Grade System you just that manufacturer to a new grade level.



              • 4. Re: If function problem

                Hi Mark, it worked! Thanks.

                Next step to add the different discount for different suppliers.

                You've lifted a dark cloud, it was driving me nuts!

                • 5. Re: If function problem

                  I like your grade idea. I only currently have 3 manufacturers but this makes sense going forwards. Thanks.