Select a default value from an xref table if there is not an exact match from within a map

Document created by pete_mccoy1.3055586893899446E12 Employee on Feb 18, 2015Last modified by dave_lesshafft on Mar 1, 2016
Version 2Show Document
  • View in full screen mode
Using a Xref table that has multiple rows/columns, want to select from that table based on a Primary Key and a Secondary Key, pull exact match if it exists, but if no exact match exists, pull a default row, based on Primary Key
In your xref table create a default row, with a the value of the Primary Key, and a unique value in your Secondary Key column.  (The word Default, and '*')
In your map, create a User-Defined function.  In the User-Defined function, set up 2 Xref look-ups.
The first Xref look-up will be based on the Primary Key and Secondary Key from the inbound profile, with the Secondary Key being passed to the second Xref look-up, and the Primary Key being passed to the second look-up from the inbound profile.
In the second Xref look-up, set the default value for the inbound Secondary Key equal to the unique entry created for the default row in the Xref table.
If the look-up returns a row based on the exact match, the Secondary Key will be populated, and the second Xref look-up will return the values based on an exact match.
(This is what occurs in the first Xref look-up as well.)
If there is no match for the first Xref look-up, the Secondary Key that is used in the second look-up will be a null, and therefore take on the default value, and then produce an exact match of the Default row.