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.com 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.com ODBC driver allows an ODBC compliant application such as Extreme Translator to query and update Salesforce.com. As EDI is a supported format for Extreme Translator, you can use these components to generate EDI messages from Salesforce.com data and vice versa.

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

Salesforce.com Quote > Salesforce.com ODBC Driver > Extreme Translator > EDI X12 850 Purchase Order

Before you create the map in Extreme Translator that transforms Salesforce.com data to EDI, you need to download, install and license the Salesforce.com ODBC driver. Then create an ODBC data source that connects to your Salesforce.com 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.com 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, type "Salesforce.com Quote".
  5. In the SQL select statement box, type:
    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.com 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.com 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.com 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 Saleforce.com 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.com data.

    For example, C:\SalesforceQuote.edi.

  17. On the Project menu, choose Run.