Introducing the all-new TIBCO Community site!

For current users, please click "Sign In" to reset your password and access the enhanced features. If you're a first-time visitor, we extend a warm welcome—click "Sign Up" to become a part of the TIBCO Community!

If you're seeking alternative community sites, explore ibi, Jaspersoft, and Spotfire.

Jump to content

Mapping Option Set Value in Dynamics CRM

Manoj Chaurasia

Recommended Posts

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.






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

Link to comment
Share on other sites

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()

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.



Link to comment
Share on other sites

  • 2 years later...
  • Create New...