Skip to content


Translating Excel Text to CRM Picklist (Option Set) Value as destination Data Type

Aaron asked 6 years ago
I am looking for a best practice here as I cannot find an example from the eOne templates.
I am bulk creating from Excel to Dynamics CRM 2011. In CRM my entity has multiple Picklists (NOT ENTITY LOOKUPS). Imagine a list of Cities with matching CRM value pairs such as Dallas/1, Chicago/2, New York/3, etc.
From Excel, I want someone to enter “Dallas” an SmartConnect will lookup the CRM value for “Dallas” when it creates the entity, “1” in this example. The only way I can achieve this is the have a SmartConnect “Translation” Table which allows me to re-create the name/value pairs. The downside to this is the obvious maintenance and inevitable trouble with getting CRM and SmartConnect out of sync.
What do the SmartConnect Gurus recommend as a best practice?
Aaron answered 6 years ago
Patrick – Thanks! that was extremely helpful.
I have a followup question regarding local variables. Since I am importing from Excel, I don’t want to defile my ObjectType and ApptributeType in the source data (Excel Columns) as the document you linked to suggested (the document assumed a SQL import with constants in the SQL).
I attempted to make a local constant by defining an Attribute of type “Function” and with the code ‘Return “attribute_name”‘ however while it validated, there was an error at runtime which I assume is while this was not recommended in the document.
Is there another way to define a constant as a source (other than a Global Variable for all of SmartConnect)?

If you would like to submit an answer or comment, please sign in to the eOne portal.