Translating Salesforce data into EDI format

Electronic Data Interchange (EDI) is a data format that enables organisations and businesses to exchange data. EDI is used in all major industries: health, retail, automotive, finance, and so on. EDI is a standard that is governed by the Accredited Standards Committee (ASC X12). EDI releases contain a set of message types. Example message types are invoices, purchase orders, and healthcare claims.

Salesforce does not have an in-built means of exchanging data with EDI applications. However it is possible to use a third-party application to translate Salesforce object values into EDI messages and vice versa. One such application is Etasoft's Extreme Translator.

Extreme Translator supports ODBC databases as a source or target. The Salesforce ODBC driver allows an ODBC compliant application such as Extreme Translator to query and update Salesforce. As EDI is a supported format for Extreme Translator, you can use these components to generate EDI messages from Salesforce data and vice versa.

The example in this blog generates an EDI Purchase Order from a Salesforce quote:

Salesforce Quote > Salesforce ODBC driver > Extreme Translator > EDI X12 850 Purchase Order

Before you create the map in Extreme Translator that transforms Salesforce data to EDI, you need to download, install, and license the Salesforce ODBC driver. Then create an ODBC data source that connects to your Salesforce instance. On 64-bit Windows, you need to configure a 64-bit ODBC data source. To do this, use the 64-bit version of Microsoft ODBC Data Source Administrator, which is located in Control Panel. (On some versions of Windows, there is both a 32-bit and a 64-bit version of ODBC Data Source Administrator located in Control Panel, however their architecture is clearly labelled if this is the case.)

  1. In Extreme Translator Mapper, right-click the input pane (on the left-hand side of the window). Choose Add > Database from the pop-up menu.

    The Add Database dialog box is displayed.

  2. In the ODBC data source box, type the name of your Salesforce ODBC data source. Choose OK.
  3. In the input pane, right-click the database icon. Choose Add > SQL Query from the pop-up menu.

    The SQL Select Query dialog box is displayed.

  4. In the Name box, enter Salesforce Quote.
  5. In the SQL select statement box, enter: 
    SELECT QUOTE.SHIPPINGCITY AS SHIPPINGCITY,
QUOTE.SHIPPINGCOUNTRYCODE AS SHIPPINGCOUNTRYCODE,
QUOTE.SHIPPINGPOSTALCODE AS SHIPPINGPOSTALCODE,
QUOTE.BILLINGCITY AS BILLINGCITY,
QUOTE.BILLINGCOUNTRYCODE AS BILLINGCOUNTRYCODE,
QUOTE.BILLINGPOSTALCODE AS BILLINGPOSTALCODE,
PRODUCT2.NAME AS NAME,
QUOTELINEITEM.QUANTITY AS QTY,
QUOTELINEITEM.TOTALPRICE AS PRICE,
CONTACT.FIRSTNAME AS FIRSTNAME,
CONTACT.LASTNAME AS LASTNAME
FROM PRODUCT2
RIGHT JOIN QUOTELINEITEM
ON PRODUCT2.ID = QUOTELINEITEM.PRODUCT2ID
RIGHT JOIN QUOTE
ON QUOTE.ID = QUOTELINEITEM.QUOTEID
LEFT JOIN CONTACT
ON QUOTE.CONTACTID = CONTACT.ID
WHERE STATUS = 'Accepted'

    This SQL query retrieves information from various Salesforce objects for the quote: the customer name, the billing and shipping address, the line items, and so on. As we are going to generate a purchase order, the query only retrieves quotes that have been accepted. In our Salesforce instance, we have enabled State and County Picklists, which adds ISO country codes for the billing and shipping address to the Quote object. This is the required format for countries in the EDI Purchase Order Transaction Set (850).

  6. Choose the Read Fields button, and then choose OK

    The input pane should contain the following fields, which will be translated into an EDI format purchase order.

  7. Right-click in the output pane. Choose Add > Message.

    The New Message dialog box is displayed.

  8. In the Translation section, choose Based on template, and then choose OK

    The Templates dialog box is displayed.

  9. In one of the x12 templates sets, choose 850 PO Purchase Order, and then choose Import.

    The Template Wizard dialog box is displayed.

  10. In the Item Type list, choose Output. Choose OK.

    The purchase order fields appear in the output pane.

  11. In the input pane, select SHIPPINGCITY.
  12. In the output pane, select this field: 
    X12 850 Purchase Order
  ISA
    GS
      N1
        N4
          City Name - 1
  13. On the Edit menu, select Map / Unmap

    This creates a mapping between the Salesforce data field and the target EDI message. When you run the translator, Extreme Translator Mapper will use the value in the SHIPPINGCITY column to populate an EDI message.

  14. Map the remaining Salesforce fields.

    The resultant map will be:

    Input Output
    SHIPPINGCITY 
    X12 850 Purchase Order
  ISA
    GS
      N1
        N4
          City Name - 1
    SHIPPINGCOUNTRYCODE 
    X12 850 Purchase Order
  ISA
    GS
      N1
        N4
          Country Code - 4
    SHIPPINGPOSTALCODE X12 850 Purchase Order ISA GS N1 N4 Postal Code - 3
    BILLINGCITY 
    X12 850 Purchase Order
  ISA
    GS
      N1
        N4
          City Name - 1
    BILLINGCOUNTRYCODE 
    X12 850 Purchase Order
  ISA
    GS
      N1
        N4
          Country Code - 4
    BILLINGPOSTALCODE 
    X12 850 Purchase Order
  ISA
    GS
      N1
        N4
          Postal Code - 3
    NAME 
    X12 850 Purchase Order
  ISA
    GS
      P01
        Assigned Identification - 1
    QTY 
    X12 850 Purchase Order
  ISA
    GS
      P01
        Quantity Ordered - 2
    PRICE 
    X12 850 Purchase Order
  ISA
    GS
      P01
        Unit Price - 4
    FIRSTNAME 
    X12 850 Purchase Order
  ISA
    GS
      N1
        N2
          Name - 1
    LASTNAME 
    X12 850 Purchase Order
  ISA
    GS
      N1
        N2
          Name - 1
  15. In the output pane, select X12 850 Purchase Order.
  16. In the Properties tab, in the DataPath field, enter the name of the EDI file that Extreme Translator Mapper file will generate from the Salesforce data.

    For example, C:\SalesforceQuote.edi.

  17. On the Project menu, choose Run.