Introducing Guides:Define machine-readable EDI requirementsLearn more
Stedi
Sign inBook a demoStart building
Try Stedi free

Start building an EDI system of your own.
No credit card required!

Sign Up Learn More
Blog February 24, 2022

Introducing lookup tables in Mappings

Headshot of David Kanter
David Kanter

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.

A lookup table with information to map from a carrier code (SCAC) to a carrier's name

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:

An empty mapping in the Mappings editor

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 transformation editor next to currency, click “Lookup tables”, and “Add new”.

The configuration for mapping the currency output field within price

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.

The lookup table with countries and currencies

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 transformation editor:

The mapping function to resolve a country's currency code

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!

The test mapping shows that the country DE is mapped to the currency EUR

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:

A lookup table with address information

The example mapping for Name field would be:

A lookup function that uses the address information

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

The lookup function uses the Address field from the lookup table response

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.

Featured
Mappings
Map data into any shape

Mappings is a tool that maps JSON documents from one structure to another to integrate with any pipeline.

Try Mappings Learn more 
JSONata Playground
Experiment with JSONata expressions

Get familiar with JSONata syntax in Stedi’s playground where you can quickly edit, share, and embed examples.

Share
Previous
EDI Core: the foundation of any reliable EDI system
Subscribe

Get blog posts delivered to your inbox.

Stedi

Stedi is a developer-focused platform for building automated EDI solutions that integrate with any business system.

Sign up to get an API key and try Stedi’s pay-⁠per-⁠use products with generous free tiers.

Start building
Products
EDI CoreMappingsGuidesSFTPBucketsFunctionsStash
Follow
  1. Twitter
  2. GitHub
Backed by
AdditionBloomberg BetaFirst RoundStripeUSV
Customer AgreementService TermsPrivacy Notice

Stedi is a registered trademark of Stedi, Inc. All names, logos, and brands of third parties listed on our site are trademarks of their respective owners (including “X12”, which is a trademark of X12 Incorporated). Stedi, Inc. and its products and services are not endorsed by, sponsored by, or affiliated with these third parties. Our use of these names, logos, and brands is for identification purposes only, and does not imply any such endorsement, sponsorship, or affiliation.