Using the Error column in the pasTransfer mapping sheet to validate Lookup Codes

The Error column feature was added to the pasTransfer mapping sheet in September 2016 – see related blog post.

We have used the Error column to validate values in a lookup table for lookup macros used elsewhere in the mapping sheet or pasTransfer configuration.  We are using this macro to allow us to receive all of the codes that are not in the lookup tables in one pass instead of using the current lookup behavior that only returns a code when the lookup macro is applied, one by one.  So the current lookup behavior would apply the lookup macro and when the code is not in the lookup table then stop processing and throw an error with the missing code.  After updating the lookup table for the missing code you would then reprocess the job and if another missing lookup exists then another error is written to the job log.  You then need to continue adding the missing lookups one by one until all of the errors are resolved.  This is a tedious and time-consuming process depending on the number of missing codes.

We created a macro in the error column of the mapping sheet to check for values in a lookup table and return an error if the value does not exist.  The macro used in our situation is as follows:

In code:
[Case|Departments|FALSE|DepartmentCode|DepartmentID|[Attribute|DeparmentID]|WHEN||THEN|Invalid Department]

In human:
[Case|LOOKUP FORMULA - [Name of Lookup Table]|FALSE|Column in Lookup Table that has Value to return|Column in the Lookup Table that has the value we are using as the basis of the lookup|Lookup Value]|WHEN||THEN|Missing Lookup value [Field that is used as the value we are looking up] in lookup [Name of Lookup Table]]

What this means is that if the lookup macro resolves to a blank value then return the following error message in the job log:

Missing Lookup value XXXXXX in lookup “Lookup Table Name”

This message is returned for all values that are not in the lookup table.  When 5 values are not in the tables then your message would look like this in the job log:

Missing Lookup value XXXXXX in lookup “Lookup Table Name”
Missing Lookup value XXXXXX in lookup “Lookup Table Name”
Missing Lookup value XXXXXX in lookup “Lookup Table Name”
Missing Lookup value XXXXXX in lookup “Lookup Table Name”
Missing Lookup value XXXXXX in lookup “Lookup Table Name”

The benefit to this approach is clear.  If you use the Error column technique to validate your data and during execution 50 errors occur you find out about them all at once and can correct them once and try again.  If you merely use Lookup expressions within the macro-enabled destination fields in pasTransfer errors that occur are immediately fatal and halt job processing immediately upon the first occurence which can lead to iterative test-and-correct-and-test-again behavior patterns.