I needed to translate Unit Codes for Purchase Orders going out and Shipping Notices coming in between the codes used by the external partner to the codes used by the client.
After reading BizTalk Pattern: Mapping Reference Data, I decided to give the Xref Reference Data a try
I found another blog Cross Reference Functoids Description and Example in BizTalk Server that went into a bit more detail
And finally the following blog which clarified that I would be best off using the Get Common Value and Get Application Value functoids rather than the Id Cross Referencing as it would allow me to 1) It’s a many-to-one mapping 2) Give better performance as it would be cached.
Note: There are length limitations to the values you can use for Application, Value and Common Value as they are defined as nvarchar(50), not null in the database.