Mapping Option Set Value in Dynamics CRM

[Manoj Chaurasia]

How do I map option set values using scribe online in Dynamics CRM.

So assuming my source field from SQL server as a field called Province with values.

 

ON

AB

QC

 

and so on this is a string value,

in dynamics CRM the Province Field as

 

ON ..but the integer value is 10001

AB.. integer value 10002

QC.... integer value 10003

 

 

Can I map the Label value, display name , instead of the integer value

 

this is just using 3 examples in the option set but in reality it is more.

 

So am guessing I use the formula if to cast the value

 

IF(province='ON', 10001, IF (province='AB', 10002)

but what if the province value has over 20 values and also will the formula slow down the integration.

 

Is there any other way to map the Option-set value using scribe

[Jeff Levetin]

Hi @Adeyemi Lawal,

 

With Scribe Online In CRM, you can map the label value / display name directly into the picklist numeric field.

 

province: sqlentity.province

 

 

For CRM to CRM mappings:

 

In the Connector parameters, check "Include Picklist Display Names"

In the Create or Update block, map the _displayname to the field. Here's an example

 

statecode: contact.statecode_displayname

 

 

If the displayname is not valid, it will throw an error.

 

 

Other options:

 

Store the integer value mappings in a .csv file and use CSVLOOKUPVALUE (for on premise agents)

Use a Lookuptable for cloud agents

Use the function CHOOSEBYVAL()

[Manoj Chaurasia]

Thanks @Jeff Levetin but it is expecting an integer not a string

So if I match the string to the display name I get and error.

what have done so far is to try this two logic

IF(contact.StateProvince="ON","171160000","171160001") or IF(contact.StateProvince="ON","171160000","")

ON as integer 171160000 and QC is 171160001

the first one is if the province is ON it sets the option set to ON else it sets it to QC, this is just a test.

The other is if its ON it sets the option set to ON else it leaves it blanks.

this two scenario work perfectly. But am wondering if there is an if else as am expecting at least 13 province value.

I will consider CHOOSEBYVAL then it might do the trick

[Jeff Levetin]

Hi @Adeyemi Lawal,

 

You're correct that it's expecting an integer type. But you can map a string to the field, as long as it is one of the options.

[Manoj Chaurasia]

Thanks @Jeff LevetinI used the choose by and it worked, so your answer was spot on.

 

CHOOSEBYVAL(contact.StateProvince, "ON", "171160000", "QC", "171160001", "NS" ,"171160002" ,"NB", "171160003", "MB","171160004", "BC", "171160005", "PE","171160006", "SK","171160007","AB","171160008", "NL","171160009", "NU","171160062","171160010")

Thou it was hard-coded it did the trick.

 

Regards.

[Duryodhan Swain]

Yes we can do iside scribe, 

 

More-> LookUpTable->Add you options (value & text)

 

Ex:

 

Table Name: Table123

 

Formula: LOOKUPTABLEVALUE1("Table123","text") -->to get value 

 

LOOKUPTABLEVALUE2("Table123",value) -->to get text