The Cross Reference table is a data structure used to replace a runtime lookup computation with a much simpler lookup operation. The Cross Reference Table is easy to use and requires no coding. The processing speed gain can be significant, because retrieving a value from memory is much faster than performing a database or other Connector lookup.
The Cross Reference table is comprised of a set of data elements (or values) that are organized using a model of horizontal rows and vertical columns.
Some common uses:
- Simple value translation between System A and System B, such as item codes, unit of measures, status codes, or any other type of code
- Reusable translations (for example, US State abbreviations)
- Switch/case logic (simple if/else)
- Atom-specific Map default values
- Atom-specific Connection default values (Start Shape criteria)
- Parametrize any Process step with Atom-specific values for deployment to multiple locations or customers
How It Works
The Cross Reference Table allows you to retrieve one or more "Values" based upon the value of one or more "Lookup References", however within the Cross Reference Table component configuration, you do not designate the Lookup Reference(s) or Value(s). These are chosen when you reference the Cross Reference Table within a Map Function or other Step Parameter. This allows the same column(s) to operate as either the Lookup Reference or the Value, depending on the context.
For example, suppose you are syncing customer address information between two applications. Application A requires the State to be a two character abbreviation and Application B requires the full name of the State. Because this translation is static and cannot be determined programmatically, you would use a Cross Reference Table with two columns: "Abbreviation" and "Name". When mapping the data from Application A to Application B, "Abbreviation" would be the Lookup Reference and "Name" would be the output Value but when mapping the data from Application B to Application A, "Name" would now be the Lookup Reference and "Abbreviation" would be the output Value.
Sometimes a combination of values is necessary to determine the desired output. In these cases, you can choose multiple Lookup Reference columns and the unique combination of those will return the single output Value.
In the event that more than one row matches the given Lookup Reference values, the first match will be returned. If no match is found, a null value will be returned.
 | If you're familiar with writing SQL queries, you can think of the Cross Reference Table functionality like a simple SELECT query with one or more columns returned and one or more WHERE clauses criteria:
SELECT Reference 2 FROM Cross_Reference_Table WHERE Reference 1 = <Input Value> |
Configuration
To create a new Cross Reference Table:
- Click
New in the Component Explorer of the Build Tab
- Choose Cross Reference Table as the Type
- Enter a name for the Cross Reference Table
- Choose the folder in which to save the Cross Reference Table
- Click OK
To enter data into the table, click the Add (
) button to add a new row and type in the values for each column. Click or use the Tab key on your keyboard to move between columns. You can add an unlimited number of rows. You can define the number of columns (between two and six) and rename the column headers by clicking Update Table Definition.
Alternatively, you can also populate the cross reference values by importing a local CSV file. Click Import from CSV and follow the prompts. You will choose how many columns (up to six) from your source file to import, as well as whether the imported rows should overwrite or append to any existing values in the cross reference table.
The icons above the columns allow you to:
- Add a row after the last row in the table
Create an exact copy of any selected rows
Delete any selected rows
Select all rows in the table
Deselect all rows in the table
Update Table Definition- Unable to render embedded object: File (atomspecificsettings.png) not found. Enable Atom-specific values. This option allows you to specify references for specific Atoms. Once this is enabled, it cannot be undone.
Cross Reference Table lookups are most often performed in Map Functions (under Lookup category) but can also be used as parameter values in all Process steps that use parameter configuration such as Connectors (including the Start Shape), Decisions, Document Properties, Messages, Program Commands, and Exceptions.
Examples
The Cross Reference Table shown below is an example of reusable translations. The information contained in the table pertains to the US State abbreviations and other state codes.
One Input Value and One Output Value
The Cross Reference Table can be used to accept one input value and produce one output value. The example below shows a Cross Reference Table lookup within a function. In addition to setting up the function, you would need to map all input elements from the source profile to the input values in the function. You will also need to map all output values from the function to the output elements in the destination profile.
Example Scenario
- System A uses the State values to refer to the states.
- System B uses the State abbreviations.
When mapping from System A to System B, you will need to translate the value in State to the State Abbreviation value. The SQL Select statement for the Output_Element would be 'select State_Abbreviation from Cross_Reference_Table where State = Input_Element'. If the State=Alabama in System A then the State Abbreviation='AL' for System B. 'AL' is the value that would be returned in the output.
More than One Input Value and More than One Output Value
The Cross Reference Table can be used to accept more than one input value and produce more than one output value. The example below shows a Cross Reference Table lookup within a function. In addition to setting up the function, you would need to map all input elements from the source profile to the input values in the function. You will also need to map all output values from the function to the output elements in the destination profile.
Example Scenario
- System A uses the State values and the Code values to refer to the states.
- System B uses the State Abbreviation values and the FIPS State Code to refer to the states.
When mapping from System A to System B, you will need to translate the value in State to the State Abbreviation value. The SQL Select statement for the Output_Element would be 'select State_Abbreviation from Cross_Reference_Table where State = Input_Element'. If the State=Alabama in System A then the State Abbreviation='AL' for System B. 'AL' is the value that would be returned in the output.
When mapping from System A to System B, you will also need to translate the value in Code to the FIPS State Code value. The SQL Select statement for the Output_Element would be 'select FIPS State Code from Cross_Reference_Table where Code = Input_Element'. If the Code=ALAB in System A then the FIPS State Code='01' for System B. '01' is the value that would be returned in the output.
Advanced Configuration: Atom Specific values vs. Default values
This feature can only be used if your account has been set up to allow for more than one Atom. It is an Advanced feature that cannot be reversed. The Atom Specific Cross Reference Table shown below allows you to specify which values are generated based on the Atom that is running the process. If an Atom does not need a specific configuration, you can use the Default value in the Atom column. You can select the Atom by clicking the Choose Atom icon. The information contained in the table pertains to the US State abbreviations and other state codes.
Example Scenario
- System A uses the State values to refer to the states.
- System B uses the State Abbreviation values to refer to the states.
When mapping from System A to System B, you will need to translate the value in State to the State Abbreviation value. The SQL Select statement for the Atom Specific Output_Element would be 'select State_Abbreviation from Cross_Reference_Table where State = Input_Element and Atom=<Atom Name> Atom=<Default Atom>.
If the State=Alabama in System A and the Atom=doc.boomi.com then the State Abbreviation='AL' for System B. 'AL' is the value that would be returned in the output.
If the State=Alabama in System A and the Atom=<Default Atom> then the State Abbreviation='ALA' for System B. 'ALA' is the value that would be returned in the output.
This page
Other pages
Account
