5 Replies Latest reply on Mar 20, 2016 3:37 PM by alquimby

    Creating a Calculation Field

    Linda

      I need to compute a payment based on values in 4 different fields and would like to put the result in a calculation field in the same record.  I was thinking that I could create the payment field as a calculation field.  The calculation is:

       

      If field1 = "yes" then add $25 to payment field

      If field2 = "yes" then add $25 to payment field

      if field3 = "yes" then add $25 to payment field

      if field4 = "yes" then add $50 to payment field


      The calculation is cumulative.  All 4 fields could be equal to "yes".  If they are, then the payment field would equal $125.  All Ifs need to be evaluated and only add to the payment field if the If is true.  Is this possible to do in a calculation field?  If so, how is it written?  I have tried the above with multiple Ifs and I get an error message saying that an operator is missing.


      thanks everyone.


      Linda

        • 1. Re: Creating a Calculation Field
          RickWhitelaw

          What you say is true only if the payment field starts out as $0.00. Is this the case? Or can the original amount be let's say $100.00 or some other figure before the additions?

          • 2. Re: Creating a Calculation Field
            Linda

            The payment field starts out as 0.  I am mainly interested in how/if I can put this calculation into a calculation field.  Is so, how is it written?

            Thanks,

            Linda

            • 3. Re: Creating a Calculation Field
              Vaughan

              New Payment Field =

               

              Payment Field +

              If( Field1 = "yes" ; 25 ) +

              If( Field2 = "yes" ; 25 ) +

              If( Field3 = "yes" ; 25 ) +

              If( Field4 = "yes" ; 50 )

              • 4. Re: Creating a Calculation Field
                Linda

                Thank you, Vaughan, that is what I was looking for.  I will give that a try.

                 

                Thank you, Rick, for responding, as well.

                 

                Linda

                • 5. Re: Creating a Calculation Field
                  alquimby

                  Linda,

                   

                       Vaughan's calculation is correct. And this is fine if the $25 and $50 added will never change. But if they do change, the only way to show the new add ons is to change the calculation, which changes all previous invoices, bills, or whatever you are doing.

                   

                       If you are just starting out with this database a more flexible way to proceed is like in the attached. Create 2 global fields for your add ons, then put those global fields in the payment total auto enter number field (not a calculation type). In the attached, after records 1 and 2 were created, I changed the g_second_add value from $50 to $55 before creating record 3. As you can see that did not change anything in records 1 and 2. The field1_add, field2_add, etc., fields are not necessary––I just put them in for demo purposes.

                   

                  Al Quimby