Custom Mapping: Lookup Tables

This functionality allows you to provide values for string replacement on attribute mapping. When you create a lookup table, the lookup.txt file will be created in …\UnitySync-v2.x\connections\YourConnectionName\.

Your lookup table entries should be in this format:

Source Value|Replacement Value

Wild cards

Wild cards may be used to indicate single (?) or many (*) characters. Not to be confused with the ? used in the custom mapping syntax.

Custom mapping to utilize lookup:

To utilize the Lookup table, you must use Custom Object Mapping. The mapping for an attribute that uses the lookup table should be in this format:

DestinationAttribute=?^SourceAttribute^?

Enclosing the Source Attribute variable in ? characters indicates that lookup will occur. All lookup entries are tested against all lookup mappings.

In this example lookup table, we are ensuring the consistency of Destination values for Company and Department attributes.

Example Lookup Table:

DirWiz|Directory Wizards
Dept1001|Sales
Dept2*|Administration
*VIP*|VIP Offices

Example mapping to replace values based on the lookup table:

Company=?^Company^?
Department=?^Department^?

Results:

• Source objects with a Company/Dept value of “DirWiz” will sync to the Destination with a value of “Directory Wizards”
• Source objects with a Company/Department value of “Dept1001” will sync to the Destination with a value of “Sales”.
• Source objects with a Company/Department value starting with “Dept2” will sync to the Destination with a value of “Administration”.
• Source objects with a Company/Department containing “VIP” will sync to the Destination with a value of “VIP Offices”.

By default, If no match is found by Lookup, the original Source value will be synced to the Destination. So, in this example, if the Source data already includes Directory Wizards for the Company value, it will be synced as is. Note, however, that any other version of the Company name, for example Dir Wizards, will also be synced as is because we have not included this potential value in the lookup table. Thankfully, if you notice this happening, it is easy to simply add another line to the lookup table to account for this and it will be corrected on your next sync.

Alternatively, instead of allowing the fall through to the original value, you can insert a default value (i.e Not Listed) by using a single * wildcard. This must be the last rule in the lookup table.

*VIP*|VIP Offices
*|Not Listed

Another good example of a lookup table is the Country to Country Code Lookup Table.