Workbooks Scripts

Detailed guidance about various elements of Workbooks Scripts, including the scripts environment, calling the API, desktop actions etc.

Useful information about Workbooks Scripts and the API can be found of the following links below:

• Workbooks Scripts

• Getting Started
• About PHP
• Summary and Exit Codes
• The Script Environment
• Calling the Workbooks API
• Sorting Results via the Workbooks API
• Desktop Actions via the API
• An Example Process Button

Click on the links to be taken to that section.

Workbooks Scripts

Workbooks Scripts contain the detailed instructions which are run when Processes are invoked to automate your business processes. They are written in the PHP programming language, which is a widely-used scripting language. Most, but not all, scripts will make calls to the Workbooks API. Helper functions make it simple for scripts to authenticate with Workbooks, call the API and log their behaviour.

The Workbooks API Reference specific to your database can be opened directly from Workbooks in one of two ways:

• Start > Configuration > Automation > API Reference
• Start > Configuration > Customisation > Record Types 

Once you have selected a record type, a button to open the “API Reference” is present near the top and will take you directly to the section of API documentation for that record type. From the reference guide, you can view the metadata for each record type including their Custom Fields. You can also look at the underlying column names for any Reports that you may have created.

Scripts can require (‘include’) other scripts and take parameters; using these features can make your scripts more maintainable. By defining Script Parameters you specify the options which can be used when invoking the script. If you have code or configuration data which is common to several scripts you should put these in an included script. Scripts are associated with Processes which run them; when a Process runs it creates a detailed Process Log. 

Getting Started

Since Scripts are PHP code, the traditional ‘Hello World’ script looks like this:

<?php

echo "Hello World!";

?>

Put it in the ‘Code’ field of a script and use the ‘Test’ button to run it.

About PHP

PHP is the most widely-used programming language on the web and is a general-purpose scripting language familiar to many website developers. It is interpreted and simple but most importantly, most web services offer APIs which are accessable to PHP scripts. Sites such as Wikipedia, WordPress, MailChimp and Facebook are all implemented using PHP. The Workbooks service itself is not written in PHP (it is written in Ruby) but we use this for our external API because it is so well-known and widely supported.

Summary and Exit Code

The last line of output from a Script is considered to be the summary of its execution and is highlighted in the Log. Normally you’ll use the summary to summarise the work that the Script did on the user’s behalf (‘Created 4 line items’). If you create a Process Button (create a Custom Form Layout; the Automation tab allows you to add Process Buttons) then this summary is normally shown to the user when the Process completes. If the Script generates warnings then these are the last things to be output and therefore they normally become the summary.

The exit code of a Script is important. A healthy Script should exit(0). One which should be retried later or has warnings should normally exit(1). Other codes are treated as indicating an error; if a Scheduled Process runs a Script which returns a value other than zero or one then the schedule will be disabled so it does not run again automatically. If you want to override this behaviour consider using a try…catch technique to catch errors and deal with them appropriately.

Back to top

The Script’s Environment

In addition to any included Scripts the system automatically includes the workbooks_api.php file (which contains the Workbooks API bindings for PHP) before running the Script. This acts as the ‘glue’ between your Script and the Workbooks API. In particular, three variables become available to your Script as it starts to execute:

$workbooks – this gives you access to the API and logging functions. For example, $workbooks->assertGet() is used to fetch records from Workbooks. The first time you access the API within a Script the $workbooks methods automatically arrange a login back to the Workbooks service.

$params – this is an array of parameters as defined by your Script Parameters passing in their values. (In addition to the parameters you define, a small number of internal parameters are also passed which the API binding uses and you can ignore).

$form_fields – this is an array of field names and values which is populated if you invoke your Script through a Button Process, in which case the value of every field in the current Form is passed to the Script.

Alongside these, $_GET, $_POST, $_FILES and $_SERVER are populated.

Another example:

<?php
$workbooks->log("Hello World: I was passed", array($params, $form_fields));
exit(0);
?>

This version uses the $workbooks->log() function which is the preferred way for a Script to add entries to the Log. The function takes up to three parameters (two are specified here: a string and an array). The first (mandatory) parameter is simply a string which should go into the Log. The second parameter is optional and is an expression which should be dumped (normally this uses var_export()). The third parameter is also optional:the ‘log level’ and defaults to ‘debug’: you can also specify ‘error’, ‘warning’, ‘notice’ or ‘info’. 

If you use echo() or simply output text outside of the <?php …. ?> tags then it ends up being logged at ‘debug’ level.

Before running the Script above add a couple of Script Parameters and then use the ‘Test’ button. You will be prompted for parameter values and then shown the resulting Log. If you create a Process Button for this Script you will also see a fully-populated $form_fields array.

As an experiment you could find out more about the Script’s runtime environment by running echo phpinfo(); within your Script.

Back to top

Calling the Workbooks API

Accessing the API is simple via the $workbooks variable. In general you will call $workbooks->assertGet(), $workbooks->assertCreate(), $workbooks->assertUpdate() and $workbooks->assertDelete() to fetch, create, update and delete records within Workbooks. These functions will check the response from the Workbooks Service and raise an exception if there was an unexpected response or error.

Many other functions exist in the Workbooks PHP API including versions of the above which return the response without checking for errors: $workbooks->get(),$workbooks->create(), $workbooks->update() and $workbooks->delete(). If you are interested you can take a look in the workbooks_api.php file to see how these are implemented and discover some other useful functions.

The API allows you to operate on up to 100 records at a time. You can operate on more records by specifying ‘_start’ and ‘_limit’ to your code. The example in the below section “Sorting Results via the the Workbooks API”, gives an example of this.

Each time these functions are called the Log contains the full request/response details for that interaction with the Workbooks service; you will also see additional log records for the side-effect calls to the login and logout Workbooks APIs.

Back to top

Sorting Results via the Workbooks API

The Workbooks API allows you retrieve records and sort the order in which they are returned within the logs. Within the $find_people filter array that is passed into one of the functions above (assertGet, assertUpdate etc), specify ‘_sort[]’ and ‘_dir[]’ as a comma separated array as shown below.

<?php
//We are using Sort/Direction here to order the results that we return from Workbooks.
//NOTE: that we've passed in an array of fields to sort by. Workbooks will sort in the order that they are passed in.
$find_people = [
'_start'               => '0',
'_limit'               => '500',
'_sort[]'              => ['main_location[town]', 'id'],
'_dir[]'               => ['ASC', 'ASC'],
'_ff[]'                => ['name', 'main_location[town]'],
'_ft[]'                => ['bg', 'ne'],
'_fc[]'                => ['a', NULL],
'_select_columns[]'    => [
'id',
'lock_version',
'name',
'main_location[town]',
]
];
$workbooks->log('About to fetch some people', $find_people);
$people = $workbooks->assertGet('crm/people', $find_people);
$workbooks->log('People found', $people['data']);
?>

Notice that in the example, we have sorted by the town first and then the ID, rather than sorted by the ID then the town. If any of the fields that you are sorting by are unique, you may get obscure results returned. Therefore, you should sort by the non-unique value first. Using the example, the results will return all of the people whose name starts with ‘A’ and they have a town specified. It will group all of the towns together in alphabetical order (first sort parameter) and then sort them further by the ID (second sort parameter) giving the below results in our logging:

Back to top

Desktop Actions via the Workbooks API

The Workbooks API also includes a number of useful functions that allow you to interact with the Workbooks User Interface from a Process script.

Display a Message: There a number of ways to display a message to the user whilst using PHP, the most common being “echo”. It’s worth referring to the PHP manual for the most up to date functions on how to do this. Alternatively, there are two functions specific to the Workbooks API which will allow you to display a message with an “OK” button to the user when your script runs. These are:

<?php
//Change "Title" and 'Body' to your desired text
$workbooks->desktopMessage('Title', 'Body');
?>

OR

<?php
//Again, change "Hello World" to your desired text
$workbooks->log('Hello World!');
?>

If multiple output messages are created, the last one generated from your script will be displayed.

Open a Record: A script can be written to open a record within the Workbooks User Interface. When the script is executed, the window will open in the current session. To do this, include one of the following functions in your script:

<?php
//Open a record by specifying its Object Reference
$workbooks->desktopOpenRecordByObjectRef('PERS-1');

//Open a record by specifying its ID and Type.
$workbooks->desktopOpenRecordByIDAndType('Private::Crm::Organisation', 1);
?>

Notice that it is possible to open the record by specifying just the Object Reference or if you prefer you can specify the ID. If you use the ID, you must also specify the record’s type in the format Private::Crm::Organisation or Private::Accounting::Quotation. The types for other records can be found by opening the API Reference, here.

Open a Window: A script can also open another window in Workbooks such as a Web Process to prompt for additional information or a blank record that the user must fill in the details and save to your CRM system.

<?php
//Open a window within the current Workbooks session.
//Always specify the ID of the web process.
$workbooks->desktopOpenWindow(array('controller' => '/automation/web_actions', 'action' => 'showframe', 'id' => 1));
?>

Change to another Tab: It is possible to implement a workflow using scripts if required. Within the current window that the script is being executed from, you can make your script move to another tab such as an iFrame to prompt your users to complete the next part of your business process. To do this, include the following function in your script:

<?php
//Change tab in the current window
$workbooks->desktopActivateTab('Activities');
?>

Close Window: A script can close the current window when it is executed if required. To do this include the following function in your script:

<?php
//Closes the current window where you are executing the script from.
$workbooks->desktopCloseCurrentWindow();
?>

Open an External URL: A script can open up another tab in the browser and load another website if required. To do this, include the following function in your script:

<?php
//Opens the provided url in a new browser tab from the desktop.
$workbooks->desktopExternalUrl($url);
?>

An Example Process Button Script

Here is an example which calls the API to fetch and then delete up to 100 line items from an order.

<?php
//Delete line items if the document has any.
//Invoke this from a Process Button on an Order record.
//Look for line items which match the current document
$select_order_line_items = array(
'_ff[]' => 'document_header_id',
'_ft[]' => 'eq',
'_fc[]' => $form_fields['id'],
'_select_columns[]' => array(
'id',
'lock_version',
)
);
$response = $workbooks->assertGet('accounting/sales_order_line_items.api', $select_order_line_items);
$delete_order_line_items = $response['data']; // $delete_order_line_items is now an array, each element has an 'id' and 'lock_version'.



if (count($delete_order_line_items) > 0) {
$workbooks->assertDelete('accounting/sales_order_line_items.api', $delete_order_line_items);
echo count($delete_order_line_items) . " line items deleted\n";
exit(0);
}
else {
echo "No line items deleted\n";
exit(1);
}
?>

Many further examples can be found in the Script Library: go to Configuration > Automation > Workbooks Scripts and Script Library is a tab on the list of Scripts.

Back to top