QuickBooks Online EDI Integration

Electronic Data Interchange (commonly known as EDI) is essentially the standard method when it comes to exchanging files in modern industry. Since its inception in the 1980s, EDI can be found in industries ranging from manufacturing to health care. Through EDI, businesses have a process for exchanging their business documents with their trading partners.

While having a process for standardized document exchange is beneficial for a business, there is one step that can often go overlooked: generating or retrieving the necessary data for the business documents. This is what the CData Arc application does for you.

This article will provide a walk-through for retrieving Invoices from QuickBooks Online and sending them to the trading partner.

Setting up the Sample

To begin, download and install the CData Arc application (free, 30-day trial). Then download the sample project and extract and copy the contents to the "data" folder in the installation location for CData Arc. For Windows installations, this will likely be C:\Program Files\CData Arc. Please consult the documentation for the Java build of CData Arc for Linux/Unix/Mac installations.

Next, you need to ensure that you have configured your connection to QuickBooks Online properly.

Connecting to QuickBooks Online

With CData Arc installed, the next step is to configure your connection to QuickBooks Online through a QuickBooks Port. In CData Arc click the Ports tab and select the QuickBooks Connector from the connector drop-down menu. On the Advanced tab, you can configure your connection to your QuickBooks Online instance. To connect to QuickBooks Online, navigate to the Settings tab and click the Connect button. This will open a new browser window and perform the OAuth flow to authenticate.

Document Translation / Data Integration Walk-Through

With the connection to QuickBooks Online properly configured, you are now ready to retrieve an Invoice.

Retrieve a Sales Invoice

As we work through the sample, we start with the assumption that you have Invoices in your QuickBooks Online instance, ready to be sent to the trading partner.

Retrieve the Sales Invoice from QuickBooks Online

With existing Invoices in QuickBooks Online, you can trigger the QuickBooks Connector in CData Arc to retrieve them and place them, prepared to be translated and sent to the trading partner, in the Send folder for the MAP_810_OUT Connector. To do this, you will need to first edit the Invoices Output Template: On the Settings tab for the QuickBooks Connector, click the Edit button ( ) in the row for the Invoices template in the Output Templates table. You can clarify exactly which Invoices to retrieve by specifying a customer (via CustomerRef) or a transaction date (via TxnDate) (or both) in the WHERE clause of the selectQuery.

After you edit the Invoices template, you can click the Execute button ( ) to execute the template, retrieve the Invoices, and create XML documents ready to be processed in the MAP_810_OUT Connector. The XML elements used in the mapping template are derived directly from the column listing for the Sales Invoices table in QuickBooks Online, as exposed by the QuickBooks connector.

Translate an XML Invoice to an XML 810 Document

The MAP_810_OUT Connector is a scripting connector, designed to translate an Invoice (in XML based on its QuickBooks Online structure) into XML that relates to the X12 format.

Because you already have the XML for the Invoice, you can use the script and templates included in the sample to translate the document into an XML format that is more closely related to the 810 document expected by your trading partner.

How the Translation Works

The TranslationScript.rsb file maps the values at specific XPaths in the XML document from the QuickBooks Connector to new fields, which will be used to create a new document based on the 810 template file.

Performing the Translation

To translate the XML Invoice, simply select the INVOICES_TIMESTAMP.xml document in the Send folder of the MAP_810_OUT Connector and click Send. This will create a new document, 810_TIMESTAMP.xml, and place it in the Send folder of the WayneTech_810_Out Connector.

Translate an XML 810 Document to an X12 810 Document

The last piece of the sample is the process of translating the 810 in XML format into X12 format. This is accomplished by clicking on the 810_TIMESTAMP.xml file on the Send tab for the X12_WayneTech_OUT Connector and clicking Send.

It is at this point that the 810/Invoice is ready for transport to your trading partner. In a real-world, end-to-end integration, you would then use a communication connector like AS2 or SFTP to transmit the file to the trading partner.

More Information & Next Steps

As you can see, with CData Arc, it is possible to automate your business document exchange and integrate directly with QuickBooks Online. For more information on CData Arc, visit our home page. Download your free, 30-day trial and start controlling your business document exchange today!

Download & Install Sample

Already running CData Arc? This sample will setup CData Arc with the data and connector configurations referenced in this article: