Calculated Merge Fields

Modified on Tue, 05 Sep 2023 at 09:49 AM

One key factor that makes Actionstep so powerful is the extent to which we allow our users to customize their systems. While most of our customization features don't require any understanding of programming or scripting languages, there are a few features within the program which allow you to manually manipulate things like HTML and PHP. These subjects are considered advanced topics and as such customers who use them should do so with the understanding that troubleshooting issues with HTML and PHP is outside of the scope of support. 

 

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. 

 

Note: For security reasons only a limited subset of the PHP commands are available.

 

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 have 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.

 

Creating a new calculated merge field

From the top navigation bar go to Admin > Document Assembly > Merge Field List. This will show a list of all available merge fields in the system.  

Click the "Add Calculated Field" button above the list to create a new calculated merge field.


Note: If you just want to see existing calculated merge fields, filter the list on "Data Source" = "Calculated Field".

 

 

Testing your merge field

Calculated merge field will inherit the environment in which they run. So for example if they are placed in a matter they will have access to the attributes of the matter (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 a matter or invoice to test against you can click on "View Available Variables + Data" to see a PHP dump of the variable names and the data they contain.  If you are not used to PHP then this will look scary, but it really isn't that bad (see examples below on how to use this output).

 

Linking your merge field to document templates

Once you have created a calculated merge field, you can use it in any document template just like any regular merge field. 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 tell Actionstep which document templates contain your calculated merge fields.  This way Actionstep knows to package up your merge fields along with each template.  In 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

 Note: All statements in PHP must end in a semi-colon


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 the "Run Test" button to see the output.

echo "Hello world";


Add line breaks

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

Try this and then click "Run Test":

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

The output should look something like "Hello worldThe world says hello back".  Probably not what you intended. Add a new line 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 be on two lines - much better!

 

Some simple math

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.


Output

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


Working 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 lots of good information on date functions.

Note: You cannot use objects (OOP) in calculated fields. Instead you should use the php function like date() or strtotime() etc

 

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 on "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];
Note: The use of the array subscript "1" between square brackets.


To calculate the days between today and the settlement date we 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";


Adding some days to a date

If you wanted to know what the date would be 5 days after a certain date try this:

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


If you wanted to add working days only then 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;


Working with custom data fields

As you saw 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.

 

Accessing single-row data fields

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


So 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']));

 

Accessing multi-row data fields

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

 


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 and when trying to do a calculation where the numbers are in currency will not work. First you will have to strip out the '$' symbol and any commas that appear so that you just get the numbers (ie, you want to turn '$1,234.00' into '1234'.)

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

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


It would would be easier to follow if you defined each variable that you are going to use in your calculation then run the calculation. In the example below we are trying to calculate the tax on an invoice but 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 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);

Calculated Merge Fields in multi-row data collections

There are some options for multi-row data collections already without having to use a calculated merge field. See the Calc option for merge fields.


If you are after 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; 

Be aware for this to work you should use lower case when entering in the data collection name and the data field name. 

 

Related Articles:

- Merge Fields

- Merge Field Options

- Conditional Merge Field Statements

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article