Used by: Field procedures
Use this procedure step type to prorate an amount across detail lines such that the sum of the distributed amounts exactly equals the original amount.
For example, on a form to capture vendor invoices, if a vendor’s invoice only provides a total tax amount on their invoice but your company wants to show the tax for each detail line item. Use this procedure step type to prorate the total tax across the detail line items.
Note: This procedure step type can only be used with detail lines
Prorate Calculation
The amount to prorate is distributed based upon a relative comparison of a value on the detail line. Formever will sum the value across all detail lines and then prorate based upon the percentage of the value on each detail line.
In the image below the amount to distribute is ‘Vendor Invoice Total Tax’. The prorated amount will be stored in the detail line field ‘Tax’. The detail line field ‘Cost’ is used to calculate the distribution. The larger the ‘Cost’ the larger the ‘Tax’. Formever sums ‘Cost’ for all detail lines (Summed Cost). Then, for each detail line, it compares ‘Cost’ as a ratio: ( ‘Cost’ / ‘Summed Cost’ ) * ‘Vendor Invoice Total Tax’ to calculate the amount of ‘Vendor Invoice Total Tax’ to assign to the ‘Tax’ field.
The number of digits after the decimal point for the ‘Tax’ field are considered and the result rounded (Bankers rounding) to match.
After prorating all detail lines Formever sums the distributed amounts and compares with the original amount. Any difference is then distributed to one or more detail lines to exactly equal the original amount.
Typical Set-up
The above example shows a form with detail lines requiring a tax amount and cost amount. The typical vendor invoice only provides a total tax amount but not individual item tax amounts. So the form needs to be setup to accept the total tax and then distribute across the detail line items.
During data entry the ’Vendor Invoice Total’ amount and ’Vendor Invoice Total Tax’ amounts are entered on the top-level (header) of the form. Then individual line items from the vendor invoice are added to the detail lines. As each detail line ‘Cost’ is entered this triggers its field procedure ‘On every change’ which distributes the ’Vendor Invoice Total Tax’ amount across the current detail lines. The detail lines are summed and displayed below the detail lines for visual confirmation against the original amounts.
This is just one way such a form might be set up. Your company will likely want something different.
Configuration
These are the basic steps to configure this procedure step type. Formever will lead you through as you point and click.
- Layout fields on the form and detail lines
- Label the procedure step
- Add field procedures for prorating the value
Note: As shown below this may seem a long complex procedure to setup but it actually is not. It only takes a minute or two to configure. Remember there is no coding - just point and click.
Step 1 - Add Fields on the Form
This really depends upon the design of your form but using the example above add one top-level field with the amount to be distributed – ‘Vendor Invoice Total Tax’ above.
In the detail lines field, add one field with the value to be considered during prorating (‘Cost’ above) and one field to receive the prorated amount (‘Tax’ above). Remember to choose the number of digits after the decimal point appropriate for your situation. For example, in most business systems, 2 decimal digits for dollars and Euros quantities.
Step 2 - Label Definition
Give the procedure step a descriptive label. This doesn’t have to be done first. But make sure you provide a descriptive label before filing the master form.
Doing it first serves as a reminder of what you are trying to accomplish as you configure the step. Especially if there will be multiple steps in the field procedure or form procedure.
Step 3 - Add Field Procedures
You will want the prorate calculation to be performed every time the original amount to be prorated is entered or changes or when the detail line amount to prorate against is entered or changes. Set up the ‘On every change’ field procedures for those fields.
Also, if you sum the detail lines for comparison, you will need to another procedure step to update the displayed sums. This will require a top-level form definition to ‘Process a form stack‘ and accumulate the detail line field.
If the form sums the detail lines for comparison, add another procedure step to update the displayed sums. This will require a top-level form definition to ‘Operate over a form stack’ and accumulate the detail line field value to display.
