Sage EDI Integration

Electronic Data Interchange (commonly known as EDI) is essentially the standard method when it comes to exchanging files in modern industry. EDI can be found in industries ranging from manufacturing, warehousing and logistics to healthcare and pharmaceuticals. Through EDI, businesses have a process for exchanging their business documents with business partners.

One big part of EDI is processing and storing the necessary data contained within the business documents. This is what the Arc application does for you. And it's built to work comprehensively with Sage 50 UK to process Sage EDI integrations.

This article will provide a walk-through for configuring Arc to receive a Purchase Order, inject it into Sage 50 UK as a Sales Order, retrieve a Sales Invoice from Sage 50 UK, and send it to the trading partner.

Setting up the Sample

To begin, download and install the 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 Arc. For Windows installations, this will likely be C:\Program Files\CData\Arc. Please consult the documentation for the Java build of Arc for Linux/Unix/Mac installations.

Next, modify the X12 document in the sample to refer to existing items in your Sage 50 UK Company. Use a text editor to open the 850.x12 file located in the Send subfolder of the X12_Wayne_Tech_IN folder, find the placeholder Trading Account Reference FILLER_ACCOUNT_REF and commodity Product Codes FILLER_PROD_CODE_1 and FILLER_PROD_CODE_1 and replace them with an actual Trading Account Reference and commodity Product Codes from your Sage 50 UK Company.

Once you have filled in the X12 document with actual values from your Sage 50 UK Company, you need to ensure that you have configured your connection to Sage 50 UK properly.

Connecting to Sage 50 UK

If you have not already, you will need to install the CData ODBC Driver for Sage 50 UK (you can download a free, 30-day trial). If you just downloaded and installed the provider, please restart Arc before proceeding.

With the ODBC Driver installed, the next step is to configure your connection to Sage 50 UK as an ODBC data source. You can create a new DSN from the ODBC administration tool for your operating system or configure your connection when you install the driver. For information on configuring the connection to Sage 50 UK, refer to the online Help documentation for the ODBC driver.

Lastly, click the Ports tab and select the Sage 50 UK Database Connector from the Connector drop-down menu. On the Settings tab, you can configure your connection to your Sage 50 UK instance. Click the ODBC radio button for the Connection Type and select the DSN you just configured from the drop down menu for the Data Source Name (DSN). To confirm your connection, you can click the Test Connection button.

Document Translation / Data Integration Walk-Through

With the inbound X12 document and connection to Sage 50 UK properly configured, you are now ready to begin the walk-through.

Translate an X12 Document to XML

As we work through the sample, we start with the assumption that your trading partner has just sent you an 850 document (Purchase Order) in X12 format. This document is sitting in the Send folder of the X12_WayneTech_IN Connector, ready to be processed. When you select the document and click the Send button on the Send tab of the X12_WayneTech_IN Connector, Arc will translate the standard X12 format into an XML format and place it in the Send folder for the MAP_850_IN Connector.

Translate an XML Purchase Order to an XML Sales Order

The MAP_850_IN Connector is a scripting connector designed to translate the 850 document (in XML) from the X12_IN Connector into a Sales Order (in XML) that can be processed by the Sage 50 UK Database Connector.

Now that you have the XML file for the 850 document (a Purchase Order), you can use the script and template included in the sample to translate the document into a Sales Order formatted for use by the Sage 50 UK Database Connector. The XML elements used in the mapping template are derived directly from the column listing for the Sales Order table in Sage 50 UK, as exposed by the ODBC Driver (see the online documentation).

How the Translation Works

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

1. Navigate to the Settings tab on the MAP_850_IN Connector.
2. Find <api:set attr="db.conn" value="DSN=CData Sage50UK Source;"/> in the script and set your DSN.
3. Click Save Changes.

Performing the Translation

To translate the XML 850 document, simply select the 850.xml document in the Send folder of the MAP_850_IN Connector and click Send. This will create a new document, SALESORDER_TIMESTAMP.xml, and place it in the Send folder of the Sage 50 UK Connector.

Push a Sales Order to Sage 50 UK and Retrieve a Sales Invoice

Now that you have a Sales Order in the Send folder for your Sage 50 UK Connector, you can inject the Sales Order into Sage 50 UK, generate a new Sales Invoice from Sage 50 UK, retrieve the new Sales Invoice, and prepare it to be sent out to the trading partner.

Push the Sales Order to Sage 50 UK

To insert the Sales Order into Sage 50 UK, select the SALESORDER_TIMESTAMP item in the Send folder and click Send. This connector uses the CData ODBC Driver to add the Sales Order, ready for processing, to the Sage 50 UK account.

Generate a new Invoice

To generate a new Sales Invoice, open your Sage 50 UK company and find the Sales Order that you just inserted. Once you have found the Sales Order, you can click Complete to walk through the process of creating the Sales Invoice that you will ultimately send back to your trading partner.

Retrieve the Sales Invoice from Sage 50 UK

Once the Sales Invoice has been created in Sage 50 UK, you can trigger the Sage 50 UK Connector in Arc to retrieve it and place it, 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 SALESINVOICES Output Template: On the Settings tab for the Sage 50 UK Connector, click the Edit button in the row for the SALESINVOICES template in the Output Templates table. Replace the date in the WHERE clause of the selectQuery attribute with the date that falls just before the time you created the new Sales Invoice for the Sales Order.

After you edit the SALESINVOICES template, you can click the play button to execute the template to retrieve the newly created Sales Invoice and create an XML document 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 Sage 50 UK, as exposed by the ODBC Driver (see the online documentation).

Translate an XML Invoice to an XML 810 Document

Like the MAP_850_IN Connector, the MAP_810_OUT Connector is a scripting connector. In this case, the connector is designed to translate a Sales Invoice in XML based on its Sage 50 UK 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 Sage 50 UK Database 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 SALESINVOICES_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 Arc and CData Software, Sage 50 UK EDI integration is made fast, simple and as easy as possible. Try it today to seamlessly automate your business document exchanges with your partners. For more information on Arc, visit our home page and for more information on the Sage 50 UK ODBC Driver available from CData Software, visit the CData Software website. You can also see a full list of the data sources CData Software connects to with ODBC Drivers. Download your free, 30-day trials and start controlling your business document exchange today!