Workbooks Scripts

Workbooks Scripts


Put common code in a single Script and Include it from others.

Related Blog Posts

Workbooks Script

Workbooks Scripts

Workbooks Scripts contain the detailed instructions which are run when Processes are invoked. 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: put it in the 'Code' field of a script and use the 'Test' button to run it:

Hello World  

Or, with a little more clutter the equivalent:

  echo "Hello World\n";    

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.

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.

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.

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.

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. 

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  //   (document_header_id eq $form_fields['id'])  // and return their 'id' and 'lock_version' fields  $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 > Scripts and Script Library is a tab on the list of Scripts.