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. However, it is possible to update as many fields as required on a given record all from the same report and process. Multiple processes would need to be set up if you need to update different Record Types.

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 (otherwise the process will loop over the same records over and over again). 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. If you are in View mode, this can be found by going to the relevant report view and clicking on the information 'i' button. If you are in Edit mode, the 'i' button will not give you the correct id; instead, go to the 'Automation' tab of the relevant view and click on the 'API Reference' button.  Here you will see the Dataview Id a few times, within the API URLs:

  

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

Single Field Mapping

If you are only setting one field from the report chosen in the Data View Id parameter, then you also need to set the following two parameters to configure field mappings from the report, to the Workbooks record you wish to update:  

• Record Field Name: The field name (not label) of the field that needs to be updated. e.g. cf_customer_invoice_paid_date. NOTE: make sure the field has not been set to Read-only on the field itself, otherwise the process cannot update it. If you want it to be read-only, this must be done on the form layout, not the field.
• 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'

Multiple Field Mappings

If you want to set multiple fields from the same report on a given record, then the Record Field Name parameter should be configured slightly differently. You also do not need to set the Report Column for new value parameter - this can be left blank.

• Mode: Set this to "multi".
• Record Field Name: This parameter needs to be set in the format RECORD_FIELD_NAME:REPORT_COLUMN_NAME. Each field mapping needs to be separated by a new line e.g. cf_case_date_processed:new_date_processed
  • As above, the RECORD_FIELD_NAME needs to be the field name (not label) of the field that needs to be updated. e.g. cf_customer_invoice_paid_date. NOTE: make sure the field has not been set to Read-only on the field itself, otherwise the process cannot update it. If you want it to be read-only, this must be done on the form layout, not the field.
  • The REPORT_COLUMN_NAME can be obtained as per the Record Id and Record Lock Version parameters above.