PDF Field Calculation Order Matters
Incorrect calculation order can produce incorrect results
Fortunately, there’s an easy fix that allows the form designer to configure the calculation order correctly by moving calculated fields up or down to correct the order.
I have responded to a lot of inquiries from form designers over the years about about why simple math calculations in their PDF fillable forms don’t seem to work. They will say things like:
“I tripled-checked my calculations and they are correct, but the form is not calculating properly.”
Or
“It’s calculating correctly, but only after I change another field value. It always seems to be one step behind. I’m so frustrated.”
Frustrated indeed. The good news is, that frustration quickly evaporated once they applied the simple fix of which they were previous unaware.
PDF is Not Excel
PDF fillable forms with calculated fields have a specific calculation order, unlike an Excel spreadsheet, which intuitively uses the calculated values of other cells referenced by them in their own calculations (Sorry. I know that sentence is a mouthful. It basically means you can’t mess up the calculation order in Excel.) If the calculation order in a PDF form is not correct, it can result in incorrect calculation results.
So what is the calculation order of a PDF fillable form?
The calculation order will set itself based on the order that calculations are added to fields. So if you create five fields with calculations, the order of those calculations will be the order those calculations were added. If you later decide to add a field with a calculation in which any of those five fields use its result in their calculations, you will have incorrect results, as the new field will calculate sixth, after the five have already run and won’t contribute to their results until after the fact. Confused? A specific example should clear it up.
Example
Suppose you have five fields in an invoice form named Subtotal.1 through Subtotal.5 and a sixth field called TOTAL, that is calculated as the sum of all five Subtotal fields. So far there is only one calculated field, "TOTAL", so there are no calculation order issues.
TIP: If you create the Subtotal fields using Right-click Create Multiple Copies to produce the period and numbered suffixes in the field names, you only have to select the Subtotal field in the Sum of window of the calculation tab of the field properties as I wrote here, instead of all five fields individually.
Now suppose Subtotal.1 is subject to sales tax at a rate of 10% and you are asked to create a pre-tax field, and calculate Subtotal.1 as 110% of the pre-tax field value. Because you added the calculation to Subtotal.1 after you added the calculation to TOTAL, Subtotal.1 will calculate after TOTAL, which will cause issues. When the pre-tax value is added, Subtotal.1 will calculate correctly, but it will not be included in TOTAL because its calculation runs after TOTAL has already calculated. Once another field value changes, and the calculations run again, it will correct itself because the Subtotal.1 value did not change, so its correct value will be included in the TOTAL field. If the pre-tax value is ever changed, the new calculated Subtotal.1 will not be included in the TOTAL field and problem will restart.
Easy Fix
Fortunately, there’s an easy fix that allows the form designer to configure the calculation order correctly by moving calculated fields up or down to correct the order. The calculation order of the example is TOTAL followed by Subtotal.1, but it should be Subtotal.1 followed by TOTAL. The user can either move Subtotal.1 up in front of TOTAL, or move TOTAL down, behind Subtotal.1:
In form editing mode, select Set Field Calulation Order… from the More dropdown of the right navigation panel (“new” Acrobat disabled).
Select the field to move, then click up or down.
