Have you found yourself needing to map custom OptionString fields from Dynamics NAV into custom OptionSets in Dynamics CRM? And have you found that if you have blank or unassigned values in either system that this can be difficult to do? If so, then this post is intended to help you out. One of our field engineers who has assisted several customers in preforming this type of mapping has come up with this matrix for mapping NAV OptionStrings to CRM OptionSets (picklists) when there are "blank" or unassigned values involved.
ENVIRONMENT: Dynamics CRM 2011 - Dynamics NAV 2009 R2 – Connector for Microsoft Dynamics v2
SCENARIO A: Dynamics CRM OptionSet equal to Dynamics NAV OptionString without default blank value in Dynamics NAV and “Unassigned Value” in Dynamics CRM
Dynamics CRM Field Name : CRMOption
Dynamics CRM OptionSet Names: One,Two,Three
Dynamics CRM OptionSet Values: 100000000, 100000001, 100000002
Dynamics NAV Field Name : NAVOption
Dynamics NAV OptionString : One, Two, Three
Mapping details :
NAVOption : CRMOption\Name
CRMOption\Name : NAVOption
SCENARIO B: Dynamics CRM OptionSet without default “Unassigned Value” with Dynamics NAV OptionString with default blank value
Dynamics CRM Field Name : CRMBlocked
Dynamics CRM OptionSet Names : none,Ship,Invoice,All
Dynamics CRM OptionSet Values : 100000000, 100000001, 100000002, 100000003
Dynamics NAV Field Name : NAVBlocked
Dynamics NAV OptionString : ,Ship,Invoice,All
NAVOption: If(EqualTo(Blocked\Name, “none”), “_blank_”, Blocked\Name)
CRMOption\Name: If(EqualTo(Blocked, “_blank_”), “none”, Blocked)
NOTE : You could also use CRMOption\Value and use a map with nested If like - If(EqualTo(Blocked,”Ship”), 100000001,If(EqualTo(Blocked,”Invoice”)… etc.
SCENARIO C: Dynamics CRM OptionSet with default “Unassigned Value” with Dynamics NAV OptionString with default blank value
Dynamics CRM Field Name : CRMBlocked
Dynamics CRM OptionSet Names : ,Ship,Invoice,All
Dynamics CRM OptionSet Values : (SQL) NULL, 100000000, 100000001, 100000002
NAVOption: If(GreaterThan(Blocked\Value, 0), Blocked\Name, “_blank_”)
THIS IS WORKING ONLY PARTIALLY : IT IS NOT POSSIBLE TO ASSIGN THE DEFAULT VALUE “Unassigned Value” BACK FROM NAV TO CRM
This is due to the fact that default “Unassigned Value” OptionSet Name has got a SQL NULL Value and there is no way for the Dynamics CRM 2011 adapter to create a NULL on a OptionSet Value. Even DefaultValue() function won’t work since it would pass a 0 (zero) value and not a SQL NULL with a consequent integration error logged by Connector for Microsoft Dynamics.
Our recommendation, then, is to create a Dynamics CRM OptionSet field like the one described for SCENARIO B if you have a bi-directional synchronization.
pls share same thing for ax
@Rajesh - this is very different for Microsoft Dynamics AX since that product has a completely different notion of how Enums are handled (and it is even slightly different for 2009 verses 2012). I suggest working with support if you should need to do this for Microsoft Dynamics AX.
Any chance you can release the CRM Connector source code? I would like to fix the option set bug as described in section C.
@Joel - No. Connector for Microsoft Dynamics source code will not be released externally.
@thehetz, thanks for the response. I do want to clarify I meant the Instance Adapter, not the Connector. I assume the answer is the same though. If you know when an updated Instance Adapter will be available please let us know. Thanks again.
@Joel - The answer is the same for the Instance Adapter. There currently are no plans to release an updated instance adapter. If that plan changes a post will be made to this blog announcing that.