Controlling Number Formatting When Using Formulas in Print Templates and Reports

Overview

When you add a numerical or currency field in a print template or report, the value is formatted as defined in the source table. For example, imagine that your Contracts table contains an Annual Contract Value ($contract_value) field and you are including it in a Companies table print template. If the Annual Contract Value field is configured to show a $ and two decimal digits in the Contracts table, that is what will show within the Companies table print template.

However, if you are using calculation formulas, the output will ignore the field’s display settings entirely. For example, suppose you want to insert the total contract value over five years into a Contracts table print template. And suppose the Annual Contract Value for a specific contract is $85,000.00. If you simply use $formula($contract_value * 5), the print template will output 425000.0.

Solution

If you want to ensure that the output of a formula has a specific format, you can use the format() function to do so. Using this function, you can define a specific field from a specific table before the formula. Agiloft then uses the display settings for the defined field to format the output of the formula. The syntax for using this function is:

format("tablename.fieldname", formula)

Example

In the second scenario above if you use $formula(format("contract.contract_value",$contract_value * 5)) instead of $formula($contract_value * 5), the print template will output $425,000.00.

Note that it can be complex keeping count of the parentheses that are needed. There should be the same number of open parentheses as there are closed when you are done.