Page tree
Skip to end of metadata
Go to start of metadata

One key factors 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 Templates" / "List of Merge Fields". This will show a list of all available merge fields in the system.  

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

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

Testing your merge field

Calculated merge field will inherit the environment in which they run. So for example if they are placed in an action they will have access to the attributes of the action (action ID, action name, participants, custom data, etc). If they are used in an accounting invoice template then they will have access to the invoice details (line items, tax, totals, invoice number, etc).

You can test you merge field by selecting an action ID or sale/purchase ID (invoice) to run it against.

Once you have selected and action 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 fields.  However if you plan to share you template with others by publishing it to the Actionstep App Store 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 you want to link your merge field to.

Examples

Tip: 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 maths

Let's add 1 to the action ID to display the next action ID.  [[Action_ID]] is a standard merge field so it can be refereneced in the calculation by using it inside single quotes as follows:

echo "Current action id = " . '[[Action_ID]]' . "\n";
$next_action_id = '[[Action_ID]] ' + 1;
echo "Next action id = " .  $next_action_id;

Run a test. The output should look something like

Current action id = 25
Next action id = 26

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

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 action 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 action ID we have choses 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 currencey 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);
 

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


  • No labels