Manoj Chaurasia Posted September 5, 2017 Share Posted September 5, 2017 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 Link to comment Share on other sites More sharing options...
Jeff Levetin Posted September 5, 2017 Share Posted September 5, 2017 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 More sharing options...
Manoj Chaurasia Posted September 5, 2017 Author Share Posted September 5, 2017 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 More sharing options...
Jeff Levetin Posted September 5, 2017 Share Posted September 5, 2017 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. Link to comment Share on other sites More sharing options...
Manoj Chaurasia Posted September 5, 2017 Author Share Posted September 5, 2017 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. Link to comment Share on other sites More sharing options...
Duryodhan Swain Posted February 19, 2020 Share Posted February 19, 2020 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now