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.

Instead of writing conditions inside of your mapping for simple data conversions, you can now create a lookup table to automatically replace a value that your system (or trading partner) does not recognize with one that they do.

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

Let’s examine the following lookup table, which can be used to replace an internal code, like 313630, with a human-readable code your trading partner requires, like FedEx Express.

When you create a lookup table inside of a mapping, you can find and use a matching value for any of these columns. In the example above, you look up any value by internalID, by SCAC code, or by shippingMethod.

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:

Your trading partner sends you USA as the country but does not include a currency field, which is required by your system. Additionally, you work internationally so you will also receive invoices from trading partners operating out of Germany (DE) and Australia (AU). It is standard in your industry for trading partners to send invoices in their local currency, so when you need to populate your currency key, your mapping needs to look up what country the invoice is from.

USA is not a valid currency code, so you need to convert USA to USD, and eventually DE to EUR and AU to AUD. To solve this, you need a lookup table. To create one, open the fullscreen view next to currency, click “Lookup tables”, and “Add new”.

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.

Let’s create a table named Currency_Codes with two columns, Country and Currency, and populate the rows with the relevant values.

On the upper right side of your screen, you will see that the UI provides you with the JSONata snippet you need to use in your mapping. Simply copy the snippet, and click Confirm. Paste your JSONata snippet into the expression editor:

The $lookupTable JSONata function takes three arguments:

• The name of the lookup table

• The name of the column we will lookup data by

• The path to a value

Simply replace <path.to.key> with product.country, and you will see the Output preview shows USD.

Hit the Test button and swap the input value to see how it works!

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:

The example mapping for Name field would be:

To get a mapping Address, change the column at the end of the expression:

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

You can get started with lookup tables by reading the overview in our documentation, the API Reference, and the $lookupTable custom JSONata function.

There is no additional charge to create or reference lookup tables when using Mappings; you only pay for the requests to the Mappings API.