5 Replies Latest reply on Jan 6, 2016 2:19 PM by keywords

    Calculation Field Issues




      I've been having a strange issue with a chain of calculation fields, and I'm having trouble finding a solution on my own. There are a group of interconnected fields, and the whole chain is kind of complex, so I won't lay it all out here, but I can give any additional information that would be helpful. The issue is that some unstored number calculation fields display as 0 when that's not their actual value. They sometimes do this temporarily, and if I click in a field on the layout they'll update and display the correct amount. I've also noticed that these same fields display the correct values in the data viewer while showing a 0 value in the interface. I'm able to replicate the problem most often when I have two windows open with the same layout, but different records. If I click on a field in one window, the other window will switch to displaying 0's for the fields in question. I've also only seen this issue when the fields are displayed in a portal. I've noticed the issue with a field called jobs::total_grand, that calculates the total of an invoice including tax. Total_grand is an unstored calculation field based on a field from a related table (lines::total) and another calculation field from the same table (jobs::total_tax).


      jobs::total_grand=Round(lines::total+total_tax;2) - unstored calculation, number

      jobs::total_tax =jobs::tax_rate*lines::total_taxable - unstored calculation, number

      lines::total=Total of price_extended - summary

      lines::total_taxable=Total of price_extended_taxable - summary

      lines::price_extended=Round(lines::quantity*lines::price;2) - unstored calculation, number

      lines::price_extended_taxable=Case(products::taxable=1;Round(lines::quantity * lines::price;2);"") - unstored calculation, number


      Any help is much appreciated.



        • 1. Re: Calculation Field Issues

          The whole stack is bound to be slow and tricky because everything is heavily dependent on summaries and other calcs that cannot be stored. However, at the very least you'll need to address the following:

          •     lines::total and lines::total_taxable are summary fields, so to access their data in your jobs::total_grandand jobs::total_tax calcs you need to use GetSummary().

          • 2. Re: Calculation Field Issues
            Stephen Huston

            Give some though to removing the unstored calcs and replacing calcs such as Invoice Grand Total with a stored number field which is set via a script trigger when the invoice record is committed. Then there is no latent update time when viewing the grand total of the invoice, or when searching invoices by total.


            It can take a while to adapt to not using unstored calcs, but the performance is worth the change. One can use the Evaluate function to update a stored number based on edited fields in the same table, removing the need for unstored calcs among a record's own fields, and then use script triggers onRecordCommit to update totals in related tables, especially when editing Invoice line items via a portal and updating tax and totals on the invoice.


            This can speed up things like sales reports by as much as 10x to 100x over a networked system. Also removes all the refresh issues you described.

            • 3. Re: Calculation Field Issues

              Totally agree.

              • 4. Re: Calculation Field Issues

                Thanks for the suggestions. I went ahead and changed the total_grand and total_tax fields to use the GetSummary function.


                Stephen, can you clarify a little more for me? Are you saying that total_grand shouldn't be a calculation at all, just a number field that calculates using the on record commit script trigger?

                • 5. Re: Calculation Field Issues

                  Yes, use the same calculation, but instead of making the field itself a calc field, make it a number field with an auto-enter calculation. The benefit of this approach is speed—once the value is set it can be indexed, and doesn't need to be altered unless the values it references are changed, in which case you use a script trigger to update it.