by Jerod Johnson | January 25, 2022

Automating the Accounts Payable Process in 6 Easy Steps

Digital transformation across accounting brings both challenges and opportunities. With the rise of cloud-based accounting software, financial data is more valuable, in-demand, and proliferated than ever before. But amid this digital transformation, there still remain many tasks that are performed manually, including invoice processing and expense approval.

CData provides data connectivity solutions that make it simpler to integrate accounting data with other apps and platforms, enabling the automation of accounts payable (AP) tasks and processes. In this article, we walk through using CData to set up an automated expense approval process with FreshBooks. Not using FreshBooks? No problem. CData offers connections to over 250 data sources, including all major accounting software platforms like NetSuite, Xero, Sage, QuickBooks, and more.

Register for our free webinar (Thursday, February 10th at 2pm ET) to learn more about digital transformation in accounting software!

Example: AP Automation with SQL SSIS and CData

SQL Server Integration Services (SSIS) is a platform for building enterprise-level data integration and data transformations solutions. CData SSIS Tasks & Components allow you to easily automate accounting processes by building data flows that incorporate business logic on top of live accounting data - in this case, FreshBooks.

Start by Getting Connected

The first step to working with Freshbooks is to create a new app in the FreshBooks Developer console for authenticating. Configure the app by selecting scopes and setting the Redirect URI (e.g. https://localhost:33334). Once configured, make note of the Redirect URI, Client ID, and Client Secret for use in the connection through the CData SSIS Tasks & Components.

Creating a Data Flow

Begin by creating a new Integration Services project and adding a Data Flow Task to the workspace. In this Data Flow Task, we will read Expenses and Staff data to build an approval process for the expenses. In this example, our business has defined four different levels of expense that are either automatically approved or must be approved by a specific manager.

CData FreshBooks Source Components

Add four CData FreshBooks Source Components to the Data Flow Task – one to read the Expenses data and three to retrieve data for each manager required for approval. For these source components, create a CData FreshBooks Connection Manager and set the following connection properties:

• Initiate OAuth: Set to "GETANDREFRESH"
• OAuth Client Id: Set to the Client ID from your FreshBooks App
• OAuth Client Secret: Set to the Client Secret from your FreshBooks App
• Callback URL: Set to the Redirect URI from your FreshBooks App

Click the "Test Connection" button in the Connection Manager to connect to your FreshBooks company using OAuth authentication.

The source companents to retrieve data for the managers should use custom SQL commands to read only the data for the specific manager, using the manager's username. For example:

SELECT * FROM Staff WHERE Username = 'ManagerA'

Conditional Split Component

Add a Conditional Split Component to separate the expenses by value, using business logic defined by your company. Each result will be used alongside a manager's email address to build an expense approval notification.

Sort & Merge Join Components

After splitting the expenses and reading the manager data, we need to join the data together to build notifications. Merge Join Components require the inputs to be sorted, so the next step is to add Sort Components to each output. The left source should hold the expenses, while the right source holds the staff, joining on the AccountingSystemId.

To configure the notification, we need the Amount and Notes from the Expense and the FirstName, LastName, and Email from the manager.

Destination Components

In this example, we use a Flat File Destination Component to store the data for use in a mail merge, but you could use any destination component to help build your notifications.

Completed Data Flow

Below, you will find a snapshot of the complete data flow, showing how each component is used.

To make it easier for you to get started with your own AP automation project, we've created a data flow template to get you started. To implement your own expense approval notification flow, download a copy of our SSIS project.

Flexible Integration for AP Automation

CData's connectivity makes it easier for any accounting professional to automate tedious and error-prone tasks, like expense approval, invoice processing, and more. By automating these tasks, accounting and finance teams can focus on tasks and operations that drive and grow business.

To learn more about how digital transformation in the accounting profession and how customers are using CData to get the most out of accounting data, join us for our free webinar on Thursday, February 10th, at 2pm (EST).

Register Now

Download a free trial of the CData FreshBooks connectors and gain easier access to your data today. Or get a free, personalized demo from CData integration specialists and learn how to better connect and integrate your accounting data.