Introducing lookup tables in Mappings

This post mentions Stedi’s EDI Core API. Converting EDI into JSON remains a key Stedi offering, however EDI Core API has been superseded by Stedi Core, an event-driven EDI system that allows you to configure integrations without being an EDI or development expert.

When building B2B integrations, it is expected that your trading partner will send you data in a format that is incompatible with your system. Or they may require you to send the data in a format that your system doesn’t produce.

Lookup tables make it easier to deal with those scenarios; they are designed for developers that want to write and maintain as little code as possible.

Where lookup tables fit in

Lookup tables are generic and can be used as part of any data mapping exercise, regardless of where in your pipeline you are using Mappings.

Say you have a trading partner that is sending you CSV files, you can:

• Use Functions to convert the CSV into JSON
• Use Mappings to transform that JSON into a shape that you need, and
• Use a lookup table to transform the values of that JSON to what your system needs

In another example, if you are generating EDI and need to change the values that come from your custom JSON API to something that is required on the EDI document, you can:

• Use Mappings to transform that JSON into a JEDI file
• Use a lookup table to change the values from your system to the values your trading partner requires
• Send JEDI to the EDI Core translate API to get EDI back

Lookup tables in action

Let’s assume that you are ingesting an invoice, and you want to map the following source JSON…

{
  "product": {
    "id": "QL-5490S",
    "country": "USA",
    "price": "500"
  }
}

…to the following target JSON:

{
  "product_number": "QL-5490S",
  "price": {
    "currency": "USD",
    "amount": 500
  }
}

Once you upload your target and source to the mappings editor, you will see this:

When creating a new lookup table, you can enter the table values manually or upload a CSV file with all values that you’d like to populate in your lookup table.

• The name of the lookup table
• The name of the column we will lookup data by
• The path to a value

Lookup tables are flexible. They can be as simple as two columns or be expanded to include multiple columns. In the example above, you could also look up a country by its currency (or by any other column in the table).

Complex transforms with lookup tables

Lookup tables can be used for more complicated transformations, like if you need to use one source value to populate multiple destination fields.

Let’s say a trading partner only sends a location code that represents where your product needs to be shipped to. Your current fulfillment software requires a full address for validation and rate shopping. You can create a lookup table with multiple columns so that each required data point is satisfied.

Let's create the following lookup table:

Now that this lookup table and logic are complete, any data coming in with just an address code will expand to a full address to meet your data requirements.

Build your first lookup table