Placeholder

Customer Forum

Create Cases for Order Line Items

Jamie (Workbooks Online) Posted: 2019-08-19 13:54

Case records are available in Workbooks CRM, and can be configured to support a versatile array of business processes. Internally within the Workbooks office, we use these to track:

  • Support Cases
  • Projects
  • Customers at Risk
  • ...and so on.

Essentially, this record type can be used to provide different teams within a business a way of tracking fulfilment of a product or service that has been purchased. A common requirement that we come up against is that of Media & Publishing businesses, where they need to track the delivery of a publication, article or the like. In Workbooks, we typically advise an Order record with Line Items to track the fact that something has been purchased. From here, a Case record would be created for each Line Item. The Line Item can then be updated to indicate which Case was created allowing seamless reporting between purchase and fulfilment.

Whilst this can be manually achieved, if you have several Line Items, this can quickly become a manually intensive process. With the Workbooks Process Engine, we have provided a simple automation script in our Script Library, which takes a Workbooks Report of Order Line Items and automatically creates a Case record for each Line. The resulting Case Reference is then updated automatically by the script to streamline this process end-to-end.

This guide gives details on how to deploy such a process to your database...

Step 1: Create Custom Fields 

Firstly, we need to create a few Custom Fields that will drive our process. These are as follows:

# Field Name Data Type Record Type Additional Information
1 Process Failure Checkbox Order  
2 Process Failure Message Multi-line Text Order Using a Multi-line text box ensures that you can store all errors that might be generated as this type of field can store around 65,000 characters including new lines. Do not use a standard text field, as this can only store 255 characters and doesn't allow for new lines
3 Case Reference Dynamic Linked Item (DLI) Order Line Item

Build a simple report on "Cases" that has columns for:

  • Case Id
  • Case Object Reference

Setup this field to use the Case Object Reference as the display value

As an additional step you may wish to configure your Form Layouts to show these fields, or you may wish to simply build reports to expose the data. See Step 2 below for further information on Reports needed to drive this process.

Step 2: Create Workbooks Reports

You need to build 2 separate Workbooks Reports to drive this process. We have provided templates with the required fields to drive this process already added. We'd recommend that you use these as a starting point, and then add additional columns to drive your Order > Case field mappings as required (see Step 3 for further information on field mappings).

You can deploy both of these Reports by clicking Start > New > Report > Create Report from a Template Report, then choose the Report you want to create from the list below:

  • Report 1: Order Line Items that need Cases - this is the main report that the Scheduled Process will use to identify Orders that need processing. You will need to make a few changes to this report to customise it for your database:
    • Add a Report Criteria as follows:
      • Process Failure = FALSE (this ensures that any Orders that failed to process are removed from this report so that the process can continue to operate even if there was an issue. NOTE: Failures are dealt with in Report 2 below).
      • Line Items > Case Reference IS BLANK (this ensures that any Order Line Items that were successfully processed are removed from the Report)
    • Add any custom columns to the Report. These can then be mapped to the Case records that are created when this process runs and is configured in Step 4 below.
    • This post assumes you have a DLI to People records to track the Contact for an Order, you can of course use a different field if you wish but the Report Columns must have the data specified below, otherwise the script will fail to create a Case or not populate fields as you may expect. If you do not currently have a Person DLI in place on your Order records, see here for an example. Add 3 x new columns as follows:

#

Report Column Location

Report Column Name

1

Contact > Id (the Person's Id)

Contact Id

2

Contact > Email (the Person's Email Address)

Contact Email

3

Contact > Telephone (the Person's Telephone Number)

Contact Telephone

  • Report 2: Orders with Processing Errors - this report can be used as an exception report to identify Orders that have failed to process. If this happens you will need to review the failed Orders and either fix them, or raise the issue with Workbooks Support. Once the issue has been resolved, set the Process Failure checkbox to FALSE to re-process the Order. To create this report, take a copy of the 1st Report above and change the Report Criteria to the following:
    • Process Failure = TRUE (the process automatically sets this field to TRUE if there was a failure either creating a Case, or updating the Order Line Item).
    • Case Reference (remove this criteria)

Step 3: Create Email Template

If this process fails, your admin users need to be notified that there was an issue so that the Order record can still be processed. If you are using the Scheduled Process mode (Step 4 below), an email will be sent with details of the error to a specified email address.

We provide a basic template which should be suitable to manage this process. This template can be deployed to your Workbooks database by clicking Start > Marketing > Templates > New Template. Select "A Specific Report Type", then choose Orders. Select the Template called "Cases from Order Line Items - Error Handling". Within the template, you may wish to change the following:

  • Email Template Name: Either leave this as the default, or rename to something more appropriate
  • From: Select an appropriate email account for this email to be sent from. An email account will need to be configured on your Workbooks database, before you can select it here. See here for further details.
  • To: Enter in the email addresses of any recipients that should be notified of a process failure.
  • Subject: Either leave this as the default or rename to something more appropriate. The @object_ref@ placeholder will be replaced with the Order Object Reference e.g. ORD-123 when an email is sent
  • Body: We have provided some basic text. You may wish to write your own or leave this as the default. At a minimum, you will need to replace the "REPLACE_WITH_PROCESS_NAME" text to the name of the Process that will be configured in Step 4 below.
NOTE: Don't change the placeholder for @script_error@, the automation replaces this text automatically with the problem detected. If this is removed, you will not see what went wrong with your process upon receiving this email.

You will need to check the Sharing Permissions of this Template to ensure that it is shared with the user that the Scheduled Process below will be running as. Typically we would recommend that any automated processes run as the "Automation" user. Click the sharing padlock on the Email Template and add a new row as follows to share the template:

Take note of the name that you saved the Email Template with, you will need this for Step 4 below, whilst configuring the Scheduled Process Parameters.

Step 4: Create a Scheduled Process

From Start > Configuration > Automation > Scheduled Processes, create a new Scheduled Process called "Create Cases from Orders" and set the following options on the Process:

  • Name: Create Cases from Orders
  • Script: Script Library > Create Cases from Order Line Items
  • 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
Remember to revise the default Scheduled Process frequency to meet your business needs and potential Order/Case volumes.

Once you have configured the above, click "Save" to reveal a Parameters tab where you will need to configure the following:

# Parameter Name Parameter Description Parameter Value
1 case_field_mappings Columns added to the report called "Create Cases from Order" can be used in this parameter to map columns from the Report to a Case when it is created See below.
2 data_view_name The name of the report view that this script will monitor when it runs Set this to the name of the 1st Report created in Step 2 above, concatenated with the Report View Name. In this example, the value should be set to "Order Line Items that need Cases:Scheduled Process" where "Scheduled Process" is the name of the Report View within the Report called "Order Line Items that need Cases"
3 failure_email_template_name The name of an Email Template that will be sent out to a specified recipient if this script encounters an error Set this to the email template configured in Step 3 above
4 order_id_column The report column that holds the ID of a Order record Leave blank (this is only needed for a Button Process as per an optional Step 5, below)
5 processing_batch_size The number of records that will be processed in one run of this script Set to a suitable number based on your volumes, if this parameter is not set, the script defaults to 5 Line Items at a time. We'd recommend no more than 100 in one run for optimal performance
NOTE: There are 3 Parameters where the name starts with "dev_only" available on this Scheduled Process which should be left intentionally blank. Do not set these, as the script is written to trigger an error for debugging purposes if these parameters are set.

Step 4a: Setup Case Field Mappings:

Field mappings from an Order to a Case record are configurable to suit your business needs, and can be configured by setting the parameter for "case_field_mappings". Before attempting to edit the Parameter, add the column to the 1st Report configured in Step 2.

This mapping should be in the following format as follows:

REPORT_COLUMN_NAME:CASE_FIELD_NAME

For example, let's assume we want to map a column called Company Brand from our Order to a Case record. In the 1st Report, add a new column called Company Brand. Then in the parameter, add the following value:

company_brand:cf_case_brand

NOTE: All mappings must be in lower case. For Report Columns, also replace spaces with underscores from the column name, otherwise the mapping will not work.
NOTE: cf_case_brand can be obtained by looking at the API Reference for Case records available under Start > Configuration > Automation > API Reference, then selecting Cases. From here, use CTRL/CMD + F to look for the FIELD LABEL, then copy and paste the FIELD NAME column to your parameter.

Each mapping should be separated by a new line (i.e. pressing return on your keyboard)

Step 5: Create Button Processes (optional)

From Start > Configuration > Customisation > Orders, select Form Layouts and then the appropriate Form Layout. On the Automation tab, click New Process Button and configure the options as per below:

  • Button Text: Create Cases for Line Items (change as appropriate)
  • Script Location: Script Library
  • Script: Create Cases from Order Line Items
  • Enable: True
  • Run As: Automation
  • Position: First (ensures this is a Button Process, not an On Save Process)

Once you have saved the Process, a new tab called "Parameter Values" appears. Configure the Parameters here in a similar way to the Scheduled Process but with the following values:

# Parameter Name Parameter Description Parameter Value
1 case_field_mappings Columns added to the report called "Create Cases from Order" can be used in this parameter to map columns from the Report to a Case when it is created See above (Step 4a). If you have already configured the Scheduled Process, it's likely these mappings will be the same and can be copied from the same parameter on the Scheduled Process to this process.
2 data_view_name The name of the report view that this script will monitor when it runs Set this to the name of the 1st Report created in Step 2 above, concatenated with the Report View Name. In this example, the value should be set to "Order Line Items that need Cases:Button Process" where "Scheduled Process" is the name of the Report View within the Report called "Order Line Items that need Cases"
3 failure_email_template_name The name of an Email Template that will be sent out to a specified recipient if this script encounters an error Leave this blank. When running the Button Process, errors are output as an on screen message and no email is sent.
4 order_id_column The report column that holds the ID of a Order record Within the 1st Report created above, click the "Automation" tab within the "Button Process" View. The API Reference will open in you browser. Use CTRL/CMD + F to find a Column called "Order Id". Copy & Paste the value from the Field Name column to this Parameter - it will look something like this "ord_id_60759".
5 processing_batch_size The number of records that will be processed in one run of this script Set to a suitable number based on your volumes, if this parameter is not set, the script defaults to 5 Line Items at a time. For a Button Process, the lower the number the better as this type of process can only run for 60 seconds. Therefore, 5 should be suitable, however it may mean you need to click the Process Button more than once to create all Cases for an Order.

You may have more than one Form Layout in your database. Therefore, if you want the button to show on different Form Layouts you will need to repeat Step 5.