Scribe Insight cross-reference drop-down and pick-list mapping approaches (option sets in Dynamics CRM)

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:

id-value
1-Mr
2-Mrs
3-Ms

 

The target connection on the other hand (assume it’s Microsoft Dynamics CRM 2011 system), has option set values as follows:

Value-Label
100000000-Mr
1000000001-Mrs
1000000002-Ms

 

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:

[Salutation_Code]
1=100000000
2=1000000001
3=1000000002

 

[Title_Code]
1=Owner
2=President
3=Manager
4=Executive Director
5=Principal

 

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:

IF(S7=”1″,”100000000″,IF(S7=”2″,”100000001, “”))

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.

3 Replies to “Scribe Insight cross-reference drop-down and pick-list mapping approaches (option sets in Dynamics CRM)”

  1. Hi, good article and really helpful.

    I have one issue. On one of my picklists, the litteral ‘=’ character is part of the value label. See below. I tried enclosing the string value with double quotes but it doesn’t work. Any idea ?

    [Frequency]
    “1x 4x/month”=3
    “>=4x/week”=4
    Once=5

    1. I haven’t dealt with an equal sign in a string, but when I need to reference a non-alpha/numeric character, I use CHAR() in a formula. If your list is only those 3 values, you could also do a formula and use LEFT to only evaluate the 1st character.

Please comment or leave feedback