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 map a set of source data into multiple records in Workbooks, and create or relate records based on matching rules. The Process Engine lets you run scripts on a schedule, or when a record is created, updated or deleted.

You can use the two together to automatically retrieve files from an external data source and import them into Workbooks. You can also use them to export a Workbooks Report and store it on an SFTP/FTP server, so your external system stays up to date with Workbooks data.

Example Usage

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

Say you want to collect payments for Contracts for a given renewal year through an external payment system. You can build a Workbooks Report that finds Contract Line Items where the End Date is in the current year, month or day, and where the status is one you’re interested in, for example “Not Renewed”. If you wish, a separate process could automatically create these Line Items as an Invoice in Workbooks.

Using the Workbooks SFTP/FTP Export Process, you can export this report automatically on a schedule, for example once a day. Each time, the process generates a CSV file and stores a copy on the server. Your external payment system can then pick up this data to update the members who still have a payment outstanding. Workbooks Reports are dynamic, so as long as the criteria are set up correctly, members will drop off the report once they’ve paid for their renewal.

When a payment is received, the external system can use a similar approach in reverse. This time, the external system generates a CSV file of payments, and the Workbooks Process retrieves it from the server. The script then automatically imports the payments received in the last 24 hours using the Import tool.

The key thing with this kind of process is making sure each system can store the other system’s record ID. In this example, you’d want your external payment system to store the Workbooks Object Reference (for example, “CON-1”) from the Contract record. You’d also need to create a Custom Field in Workbooks to hold the external payment system’s ID, so you know which records require payments.

Key Process Fields

These are the main settings you’ll configure on the Process. For the full list, see the Setup Guide.

• Process Type: Choose Import to retrieve a file from the server and import it into Workbooks, or Export to export a Workbooks Report to the server as a CSV file.
• Report Name (export only): The report showing the data you want to export and store on the SFTP/FTP server.
• Import Template (import only): The Import Template that determines how the file is imported.
• File Name: For an export, the name of the file that’s created. For an import, the name of the file created by the third party that the process retrieves. You can include Liquid syntax to insert dynamic content like today’s date, for example membership_export_{{ 'now' | date: "%Y-%m-%d" }}.csv.
• File Name DateTime Format: When a file is processed, the date and time of processing are appended to the file name.
• Import Folder name / Export File to Directory/Folder: The folder on the server that Workbooks monitors for new files to import, or exports new files to.
• Reportable Import Results: Select “Yes” if you want the results of automated imports to be stored and made reportable. This is useful for tracking which records were updated, which failed, and so on.

When a user runs the Process manually (using the green Run button), the Process Engine creates a new Process Run record, which triggers the configured Script.

Further Usage Examples

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

• Integrating with a third-party accounting package to sync Invoices and payments, so you can manage credit control directly in Workbooks.
• Synchronizing Orders and Line Items from a website stored in an external system, giving Customer Service teams better visibility and improving reporting on product issues.
• Syncing 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 lets you run a full customer success program 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.
• Logging Automated Import Results: Understand how to capture and report on the outcome of automated import processes using Workbooks reporting tools.