What are Best Practices for Lookup Tables?

Question

Question

Michael Gosselin · Jun 6, 2023

I am curious as to why one would use lookup tables, and what would be the best practices regarding them. We have two namespaces, and we would like to use the lookup table in each one. But, there's no clear outline of when to use them, and what are the best ways to use them?

Any help is appreciated.

Product version: IRIS 2023.1

Discussion (2)2

Log in or sign up to continue

score 1 · Answer 1 · 2023-06-06T15:47:13-04:00

Hey Michael.

A good use of a lookup table would be when working with an integration between two systems that use differing codes for the same values.

For example, you could have System A that records Sex as 1 for Male, 2 for Female, 0 for Not Known, and 9 for Not Specified, whereas System B uses M for Male, F for Female, and O for Other.

You could have a winding If/Else in a transform, or you could simply reference a lookup table in your DTL using the ..Lookup() function:

and then build up your lookup table to look like this:

As you can see, System A has more values than System B so the values for Not Known and Not Specified are being added as Other in my example.

Another example could be you needed to filter messages in a router based on a code within the HL7 message. You could add the codes to a lookup table as the key and a description as the value, and then use the Exists() function within your routing rule:

Which becomes:

score 1 · Answer 2 · 2023-06-07T09:13:59-04:00

Lookup tables are useful for 1 to 1 mapping that may need to change based on the system you are sending too. They are also helpful in filtering data. In many cases we use Lookup tables to filter down the data based on location or etc. from going to the endpoint system. In those cases, we have actually given those system users access to their own tables to maintain them without Integration's involvement.