3 Replies Latest reply on Feb 27, 2015 3:48 PM by philmodjunk

    Calculation field - set to not re evaluate

    MT_2

      Title

      Calculation field - set to not re evaluate

      Post

      I have a calculation based on four fields

      Annual Premium - manual entry - number field

      API - calculation - number field using (case to work out if provider X is selected which value to set in API field

      API Calculation - calculation field - number field using case to work out a percentage based on which provider selected

      commission amount is a calculation based of the four above fields

      Commission Field (Annual Premium*API*API Calculation) = commission amount

      All of the above works fine but I will need to amend the API Calculation % in the future and don't want the old values to change/recalculate and I am trying to work out the best way to do it.

      I would appreciate any suggestions!!!

      Thanks in anticipation of your help

      MT

        • 1. Re: Calculation field - set to not re evaluate
          philmodjunk

          To make rate and price changes that only affect new records use a related table and an auto-enter field option to copy over the rate or price. You can then change the value in the related table without the value changing in existing records. 

          • 2. Re: Calculation field - set to not re evaluate
            MT_2

             

             

            thanks for your reply

            Do you mean put all four fields in a related table or just 1.2,3 or 4 of them?

            Thank you

            MT

            • 3. Re: Calculation field - set to not re evaluate
              philmodjunk

              Let me give you a simple example and then you can see if it generalizes to your more complex issue.

              Say you have this setup:

              Invoices--<LineItems>----Products

              Invoices::__pkInvoiceID = LineItems::_fkInvoiceID
              Productds::__pkProductID = LineItems::_fkProductID

              A Portal to line items is use to list items sold on a given invoice. When you select a product in the _fkProductID field on a LIneItems record, this links the record to a specific record in products. One of the items in Products is the unit price charged for that item. You could define a calculation field like this to compute the line item cost:

              Qty * Products::UnitPrice

              But then, if there's a price change, all your invoices will recalculate and the totals on them will not reflect the the actual purchase amounts because the calculations no longer use the unit price in effect at the time the transaction was completed. Sound familiar?

              The solution:

              Define a UnitPrice field in LineItems. From Manage | Database | Fields, open field options for this field and set up either a Looked up value or a calculation to refer to the UnitPrice field in the Products table. This will copy over the unit price value from products into the line items table. If you later change the price for that product, the value copied over remains unchanged and all the price based totals calculated on your existing invoices remain unchanged. But when you add a new line item record to an invoice, the new price is copied over.

              So any field in your calculation that is subject to such changes should be set up to copy over values in similar fashion. Note that you can copy over individual values or set up an auto-enter calculation that computes and stores a computed total. Both options can be the "best option" in different circumstances.