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
313630
, with a human-readable code your trading partner requires, like FedEx Express
.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:
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
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.
Currency_Codes
with two columns, Country
and Currency
, and populate the rows with the relevant values.Confirm
. Paste your JSONata snippet into the expression editor:$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
<path.to.key>
with product.country
, and you will see the Output preview shows USD
.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:
Name
field would be: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
Mappings is a tool that maps JSON documents from one structure to another to integrate with any pipeline.
Get familiar with JSONata syntax in Stedi’s playground where you can quickly edit, share, and embed examples.
Get blog posts delivered to your inbox.