In your Scribe workbench dts package you usually need to map a dropdown (or picklist) to another dropdown or optionset (as in Dynamics CRM). This is a common requirement as part of data migration and data integration projects to link between drop down menus in source system to those corresponding to the target system.
For example, the source system (assume it’s a file) has Salutations values as:
The target connection on the other hand (assume it’s Microsoft Dynamics CRM 2011 system), has option set values as follows:
To achieve this mapping between the id and values of both source and target systems, there are a number of approaches and methods as listed below:
Method 1: Use a cross reference (Xref.ini) file for mappings. This is the standard approach (I claim) for mapping two optionsets in Scribe Insight. All you need to do is create a new file, call it anything such as XREF.INI. Within this file, build all your mappings as follows:
As you can see in the file, there are two sections. You can have as many sections as you want all in one file. Each section will map two drop down menus together. The first section, Salutation_Code, maps Mr (id=1 in source file) to Mr (id = 1000000000 in target CRM).
Once you add your mapping section in the file, you can then write a formula to cross reference the value on the target to the source. The formula for the Salutation target field can be something like in this example: FILELOOKUP(S7, “XREF.INI”, “Salutation_Code” )
The following screenshot shows a sample forumula:
What will happen is that, based on the source value (in our case s7), the corresponding salutation in the cross reference file will be inserted to the target
More details can be found on Scribe Insight Online help here: http://community.scribesoft.com/helplibrary/mergedProjects/Insight/Formulas/Functions/FILELOOKUP.htm
Method 2: Map and crossreference drop downs and pick lists using Scribe Work bench formulas
In this method, you either create all your option set values in the target Dynamics CRM system to have the same id as the source (for example: 1=1) or you do a formula to manually do the mapping. This could work in cases where there is two or three options but otherwise, it gets too complicated for no real benefit.
The formula can be something like this:
In other words, if the source = 1 (Mr), then set the target = 100000000. Else, if source = 2 (Mrs), then set target = “100000001”. Otherwise, leave target blank.