Working with Calculated Merge Fields

In this article:

• Creating a Calculated Merge Field
• Testing Your Merge Field Calculation
• Adding the Merge Field to a Document Template
• Examples

Calculated merge fields allow you to use a scripting language (PHP) to perform calculations. Creating calculated merge fields is an advanced topic and requires a some understanding of computer programming and, in particular, PHP.  However, it is fairly easy to create simple calculations by performing simple math on variables. (Please note: For security reasons, only a limited subset of the PHP commands are available.)

NOTE:  To see a list of existing calculated merge fields, while viewing the Merge Field List page, filter the list on Data Source = Calculated Field.

Creating a Calculated Merge Field

A calculated merge field is typically used when you need to add a new field to a document that is based on a calculation of existing fields. For example, if you've designed a workflow for a property settlement, you may need to calculate the rates apportionment on settlement day by dividing the amount already paid by how many days the new buyer will benefit from those rates after settlement day.  This amount usually gets added to the final amount payable at settlement.  This is easily achieved with a calculated merge field.

To create a new calculated merge field:

1. In Actionstep, go to Admin > Document Assembly.
2. Click Manage in the Merge field list section. The Merge Field List page appears.
3. Click Add Calculated Field. The Edit Calculated Merge Field page appears.
4. Enter a Merge Field Name and optional Description. 
5. In the Calculation Code field, enter the calculation code. Text below the field provides additional instruction. 
6. Proceed to the next section, "Testing Your Merge Field Calculation", to test your work.

Testing Your Merge Field Calculation

Calculated merge fields inherit the environment in which they run. So, for example, if they are used in a matter, they will have access to the attributes of the matter (like matter ID, matter name, participants, custom data, etc.). If they are used in an invoice/bill template, they will have access to the invoice details (line items, tax, totals, invoice number, etc.).

You can test you merge field by selecting a Matter ID or Sale/Purchase ID (invoice) to run it against.

Once you have selected the matter or invoice data to test against by completing the fields in the Test your calculation section, you can click View Available Variables + Data to see a PHP dump of the variable names and the data they contain. See examples below on how to use this output.

Adding the Merge Field to a Document Template

Once you have created a calculated merge field, you can use it in any document template just like you would other merge fields. However, if you plan to share you template with others by publishing it to the Actionstep Marketplace or making a private app, then you will need to specify which document templates contain your calculated merge fields. This way Actionstep can package your merge fields along with each template. Using the Linked Document Template section at the bottom of the calculated merge field form, you can add a row for each template to which you want to link your merge field.

Examples

Examples in this section:

• Echo Some Text
• Add Line Breaks
• Perform Simple Math
• Work with Dates
• Add Days to a Date
• Work with Custom Data Fields
•  Use Calculated Fields in Invoice Templates
• Calculate Merge Fields in Multi-Row Data Collections

Echo Some Text

This is not particularly useful but it gives you an opportunity to see how to set the output from your calculation. Use the "echo" command followed by some text or variable name and terminated by a semi-colon to set the output from your calculation.

Whatever you output will be inserted into documents where this merge field if used. Here we simply insert the text "Hello world" into the document.  Click Run Test to see the output.

echo "Hello world";

Add Line Breaks

The new line separator is \n (backslash followed by n).

Enter the following and click Run Test:

echo "Hello world";
echo "The world says hello back";

The output will look something like "Hello worldThe world says hello back". To properly space the text, add a new line break either after the first phrase or at the beginning of the second phrase:

echo "Hello world\n";
echo "The world says hello back";

Run the test again and now the output will appear on two lines.

Perform Simple Math

In this example, let's add 1 to the matter file reference to display the next matter file reference (in the case a user were numbering them sequentially). [[File_Reference]] is the merge field that pulls the file reference of the matter for which it is generated. 

echo "Current matter file reference number = " . '[[File_Reference]]' . "\n";
$next_file_ref = '[[File_Reference]] ' + 1;
echo "Next matter file reference number = " . $next_file_ref;

Note the use of the period ('.') to concatenate the text together.

Current matter file reference number = 2345
Next matter file reference number = 2346

Work with Dates

Dates are stored in a universal notation in the database so you will need to use date functions to convert these to numbers before you can perform calculations on them, and then you will need to convert them back to strings to display them.  The online PHP manual has a lot of good information on date functions.

In this example let's say we have a property transaction matter type in which we have created some custom data elements to capture details of the settlement. You want to produce a document that includes a calculation of how many days are left until settlement. To see the available custom data variables, click View Available Variables + Data. In this example, there is a custom data field represented by the array variable name $conv_settlement_data. The first element of the array contains the value 2014-10-15 00:00:00 which is the settlement date for the matter ID we have chosen to test with.

... 
 [$conv_settlement_interest_rate] => Array
 (
 [1] => 0
 )

[$conv_settlement_settlement_date] => Array
 (
 [1] => 2014-10-15 00:00:00
 )
... 

You could simply output the contents of this variable as follows:

echo $conv_settlement_settlement_date[1];

To calculate the days between today and the settlement date, use the strtotime() and date() functions to normalize the dates and then perform some calculations as follows:

// First, make sure we drop all the hour components of our dates and convert them to timestamps (seconds since 1970)
$today = strtotime(date('Y-m-d', strtotime('now')));
$settlement = strtotime(date('Y-m-d', strtotime($conv_settlement_settlement_date[1])));
 
// now compare them
if ($settlement <= $today) {
 $days = 0;
} else {
 // convert seconds to days
 $days = ($settlement - $today) / 60 / 60 / 24;
}
echo "There are " . number_format($days, 0) . " day(s) left until settlement";

Add Days to a Date

If you want to know what the date would be 5 days after a certain date, use something like this:

$date = '[[Insert-your-Merge-Field-Here]]';
$date = date('Y-m-d', strtotime('+5 days', strtotime($date)));
echo $date;

If you want to add only working days, add in any weekend days (day numbers 6 and 7) like this:

$date = '[[Insert-your-Merge-Field-Here]]';
$date = date('Y-m-d', strtotime('+5 days', strtotime($date)));
while (date('N', strtotime($date)) >= 6) {
 $date = date('Y-m-d', strtotime('+1 day', strtotime($date)));
}
echo $date;

Work with Custom Data Fields

As you see in the previous examples, you can use custom data fields in your expressions. To make this even easier, there are two special variable names which give you direct access to single-row and multi-row data collection fields.

Access Single-Row Data Fields

$singleRowData['data-collection-name']['data-field-name']

In the date example above, we could use this alternative syntax to access the settlement date:

$settlement = date('Y-m-d', strtotime($singleRowData['conv_settlement']['settlement_date']));

Access Multi-Row Data Fields

$multiRowData['data-collection-name'][0…n]['data-field-name']

Use Calculated Fields in Invoice Templates

You can use calculated fields inside invoice templates. Just remember to treat each merge field as a string and enclose it in quotes. However, keep in mind that many merge fields used in invoice templates will give a result as a currency which means that when you do a calculation where the numbers are in currency, it will not work. To fix this, you will first have to strip the $ symbol and any commas that appear so that you just get the numbers (i.e., you want to change $1,234.00 to 1234.)

Use the following to convert a currency into a plain number:

floatval(str_replace(array('$', ','), '', '[[SP_TotalInclusive]]'));

It might be easier to understand if you defined each variable that you are going to use in your calculation and then run the calculation. In the example below, we are trying to calculate the tax on an invoice by taking the invoice total inclusive of tax:

[[SP_TotalInclusive]]

Less the total of the invoice excluding tax:

[[SP_TotalExclusive]]

The first two lines strip the dollar symbol, commas and decimal points from the value of each and defines them so they can be used in the calculation on the third line.

The last line then displays the result of the calculation but also converts it back to being displayed as a currency.

$Total_Inc = (floatval(str_replace(array('$', ','), '', '[[SP_TotalInclusive]]')));
$Total_Exl = (floatval(str_replace(array('$', ','), '', '[[SP_TotalExclusive]]')));
$calculation = $Total_Inc - $Total_Exl;
echo '$' . number_format($calculation, 2);

Example: Returning the total of an invoice minus any payments applied on the created date:

$Total_Inc = (floatval(str_replace(array('$', ','), '', '[[SP_TotalInclusive]]')));
$Other_Payments = (floatval(str_replace(array('$', ','), '', '[[SP_OtherPaymentTotalAppliedOnInvoiceCreatedDate]]')));
$Trust_Applied = (floatval(str_replace(array('$', ','), '', '[[SP_TrustTotalAppliedOnInvoiceCreatedDate]]')));

$calculation = $Total_Inc - $Other_Payments - $Trust_Applied;

echo '$' . number_format($calculation, 2);

Calculate Merge Fields in Multi-Row Data Collections

If you want to find the most recent value in a multi-row data collection, you can use:

$last = '(no data entered)'; 
foreach ($multiRowData['data-collection-name'] as $row) {
 $last = $row['data-field-name'];
}
echo $last; 

For this to work, use lowercase when entering in the data collection name and the data field name. 

Related Articles:

• Merge Fields Overview 
• Understanding Merge Field Options