Whether you are setting up an ongoing integration or preparing for a one-time migration, there will be situations where a ‘single-select’ or a similar field type’s data in one system is not what the other system accepts. The list values may be very similar but not the same in both systems. Contextually as a user you know what values equate to each other, but if they aren’t identical then how do you consistently automate this in your integration or migration?
To do this, we use a method often referred to as a data translation, a lookup, or a cross reference table. Regardless of what it’s called, this method of translating values always follows the same basic structure and principle.
The Structure
To start, you will need a table of data containing at least two columns. Each column represents one of your systems and contains all the selectable options for the ‘connected’ fields from each system. Each row in the table is where you connect the related list value options between the columns. Connecting the related values in the rows is the most important part of the table since this is how you define which values translate to each other.
The Principle
Once you have your table assembled, you are ready to put it to use translating that field’s data between each system. This part often makes more sense with a visual explanation, so let’s start with a basic example scenario.
- In this scenario we have two systems which we are integrating with each other. We’ll call them ‘System A’ and ‘System B’.
- Within this integration we are mapping list fields (i.e. drop-down select or single-select) to each other between the two systems. We’ll call them ‘Field A’ and ‘Field B’ respectively.
In the illustration below, we have built our table that connects our list values for the field in each system:
Now let’s say our integration was to sync 4 records from ‘System A’ to ‘System B’, and the below list represents the value from ‘Field A’ on each of these 4 records:
Our integration would then take the Field A value in each record from System A and find a match in the Field A column of our table we built before. Once that match in the Field A column is found we then take the Field B value from the same row and use that for the new field value that we will sync to the 4 corresponding records in System B. The illustration below illustrates this by showing you the original values from System A on the left and then the new translated values going to System B on the right.
The Takeaway
Knowing how to translate your data between systems is exactly like what translators do to communicate a message from one language into another. So, mastering the structure and principle behind translating your data is how you or your integration becomes the translator and ensures that your data never gets lost in translation.
Need help getting data from one system into another?