Two of the most powerful parts of Workbooks are the Import tool and the Process Engine. The Import tool lets you define mappings from a set of source data into multiple records in Workbooks, and to create or relate records according to matching rules. The Process Engine lets you run scripts on a schedule, on change of a record (such as creation, record updates or deletions).

These two techniques are used together to implement an automated solution for retrieving files from an external data source and importing that into Workbooks or alternatively, can be used to export a Workbooks Report and store that on an SFTP/FTP Server so that your external system can be kept up-to-date from Workbooks data.

Example Usage

In Workbooks, we can store membership and entitlement data on the Contract record. On the Contract itself, we can track the Customer (Member) which is a Person record (or it could be an Organisation!) and on the Contract Line Items, we track the products they have purchased, the price they paid and the renewal dates/status.

Imagine, we’re looking to collect payments for the Contract for a given renewal year via an external payment system. We can write a Workbooks Report to identify Contract Line Items where the End Date is in the current year, month or day and where the status is of interest e.g “Not Renewed”. A separate process could be written to automatically create these Line Items as an Invoice in Workbooks if you wish.

Using the Workbooks SFTP/FTP Export Process, we can automatically export our new report periodically on a schedule – for example, once per day. The process will generate a CSV file each time and store a copy on the server. This data is then available for processing by our external payment system to update the members that still have a payment outstanding. Workbooks Reports are dynamic, thus as members pay for their renewal, they will no longer show on our report if we setup the correct criteria.

When a payment is received, our external system could use a similar concept to the above. Except this time, the external system generates the CSV file (of payments) and the Workbooks Process will take a copy from the server. The script will automatically import the payments data received in the last 24 hours using the Import utility.

The key thing to note with the above process, is that we ensure that both systems are able to share the other system’s record ID. For instance, in the example, we would want to make sure that our external payment system can store the Workbooks Object Reference e.g. “CON-1” from our Contract record. We’d also need to create a Custom Field to hold our external payment system’s ID so that we know which records require payments.

Further Usage Examples

This type of process works well in lots of different scenarios. Whilst an example has been provided, this process is not limited to the given example. For instance, have you thought about the following?

• Integration with a 3^rd party accounting package to sync Invoices and the amount paid to track outstanding payments and manage credit control directly within Workbooks
• Synchronise Orders & Line Item details of products ordered from a website that are stored in a third-party system to give visibility to a Customer Service team. This would enable you to enhance management reporting across a product range and analyse issues raised across your product suite.
• Synchronise burnt project budget from an external budget/project management system to a Workbooks Custom Record to drive billing automation

All of this data together could then be used to drive a customer success program from within Workbooks using data from all your business’ systems!

Further Reading

Now that you have read how you can use SFTP/FTP to automate your Import/Export Processes, take a look at the following guides to start using this functionality:

1. Pre-requisites Guide

2. Setup Guide

3. Logging Automated Import Results