Automated Imports or Exports using an SFTP/FTP Server

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, or when a record is created, updated or deleted.

These two techniques can be used together to implement an automated solution for retrieving files from an external data source and importing them into Workbooks. Alternatively, they can be used to export a Workbooks Report and store it on an SFTP/FTP Server, allowing your external system to stay up-to-date with 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.

Key Process Fields

• Report Name (for export processes only): If you have chosen “Export” in the “Process Type” picklist, you will need to configure a report that shows the data you wish to export and store on the SFTP/FTP Server.
  • Import Template: If you have chosen “Import” in the “Process Type” picklist, you will need to choose an Import Template that determines how the file will be imported.
  • Import/Export Filename: The name of the file you wish to import from or export to. You can include Liquid syntax to insert dynamic content like today’s date. E.g. membership_export_{{ 'now' | date: "%Y-%m-%d" }}.csv.
  • SFTP/FTP Folder: The folder on the server where the file will be read from or saved to.
  • Log Results: Select “Yes” if you want Workbooks to log the results of each Import. This can be useful for tracking which records were updated, which failed, etc.
  • Trigger: When a user carries out a manual run of this Process (via the green “Run” button), the Process Engine creates a new Process Run record. This then triggers the configured Script to execute.

Further Usage Examples

This type of process is flexible and can be used in a wide range of scenarios. For example:

• Integrate with a third-party accounting package to sync Invoices and payments, allowing for credit control management directly in Workbooks.
• Synchronise Orders and Line Items from a website stored in an external system, giving Customer Service teams better visibility and improving reporting on product issues.
• Sync project budget burn data from a third-party project management tool into a Workbooks Custom Record to drive billing automation.

Bringing all of this data together enables you to run a full customer success programme from within Workbooks, powered by integrated data from across your business.

Further Reading

• Pre-requisites Guide: Learn about the server setup requirements, user access, and folder structures needed before configuring import/export processes.
• Setup Guide: Step-by-step instructions for configuring your SFTP/FTP automation within Workbooks, including how to schedule jobs and test connections.
• Logging Automated Import Results: Understand how to capture and report on the outcome of automated import processes using Workbooks reporting tools.