Customer Forum

Update a Field using results from a Report - replace Report Cells with a field you can report on

Alix (Workbooks Online) Posted: 2020-02-06 15:57

It is possible to use Bulk Actions or Imports to update records in the database. However, Bulk Actions only allow you to set the same value across all records, and if set up on a schedule, it must be the same value every time it runs. Imports allow you to set different values across each of the records, but must be run manually.

With the Workbooks Process Engine, we have provided a simple automation script in our Script Library, which takes a Workbooks Report and automatically updates a field on a record for each row on the report, using a value provided by the report. The process can update any record type, and any field on that record type, thanks to the parameters on the script.

This script can be very useful in lots of different scenarios, e.g.:

  • Update a Status on an Organisation when a Contract Status changes - e.g. set to Expired or Renewed.
  • Update a Paid Date field on an Invoice when it has been fully paid.
  • Update a Text field or Multiselect Picklist on People with a list of items they are interested in based on the products they have purchased.

This can be used to replace Report Cells, as the value will stay up to date, but unlike Report Cells, can be reported on. By doing this, it will make reporting easier, as you can report on the field, rather than having to drill through to related records and calculating the results.

The script is restricted, in that it can only update one Record Type, and one field on that Record Type. Multiple processes would need to be set up if multiple fields and/or multiple Record Types require updating.

Step 1: Create the report

To use this script, you first need to set up the report that will provide all the details on what needs updating. How you build this report depends on how you want to use the script. Speak to Workbooks Support if you require help.

The only required columns are:

  • A column containing the Id of the record that needs updating.
  • A column containing the Lock Version of the record that needs updating.
  • A column that contains the new value to be entered into the field. This can be a normal column or a calculated column, as long as the data type matches that of the field.

There must be one row per record that needs to be updated, and there must be a criteria that constrains the report to only show the records that need updating. This would normally be done with a calculated column and criteria that compares the current value of the field to the new value, and excludes the row if they are the same.

Step 2: Set up the Process

Create a new Scheduled Process and set the following options on the Process:

  • Name: a clear description of what is being updated
  • Script: Script Library > Update Field from a Report
  • Run As: Automation
  • Enabled: TRUE
  • Log Level: Set to "Debug" and set the date to be 1 week from now. This will allow any initial setup issues to be investigated and resolved easily

The following Parameters then need to be filled in:

  • Dataview Id: The Id of the report view from which to fetch the records to process. This can be found by going to the relevant report view (in View mode, not in Edit mode) and clicking on the information 'i' button.
  • Endpoint: The endpoint for the record type that is being updated, e.g. crm/cases or accounting/sales_orders. This can be found by going to Configuration > Automation > API Reference. There is a column for 'Endpoint' (note, the ".api" part is not needed)

  • Record Id: The API name of the report column that contains the Id of the record to be updated. This can be found by opening the column in edit mode of the report, and opening the 'Advanced' tab.:

  • Record Lock Version: The API name of the report column that contains the Lock Version of the record to be updated. Found as per 'Record Id'.
  • Record Field Name: The field name (not label) of the field that needs to be updated. e.g. cf_customer_invoice_paid_date
  • Report column for new value: The API name of the report column that contains the new value to be added to the field specified in 'Record Field Name'. Found as per 'Record Id'
  • Batch Size: The number of rows to fetch from the report at a time, defaults to 10. Improves the performance of the process by reducing the number of times that the report is fetched in each process run. The process will fetch another batch if it still has time left, so the Maximum runtime of the process also needs to be set accordingly. This will depend on the time taken to process a single batch.