Mapping Microsoft Dynamics NAV OptionStrings and Microsoft Dynamics CRM OptionSets

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 

SCENARIOS 

SCENARIO A: Dynamics CRM OptionSet equal to Dynamics NAV OptionString without default blank value in Dynamics NAV and “Unassigned Value” in Dynamics CRM

Example:

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 :

1. From Dynamics CRM to Dynamics NAV

           NAVOption : CRMOption\Name 

1. From Dynamics NAV to Dynamics CRM

          CRMOption\Name : NAVOption

SCENARIO B: Dynamics CRM OptionSet without default “Unassigned Value” with Dynamics NAV OptionString with default blank value

Example:

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 

Mapping details:

1.
From Dynamics CRM to Dynamics NAV

            NAVOption: If(EqualTo(Blocked\Name, “none”), “_blank_”, Blocked\Name) 

1.
From Dynamics NAV to Dynamics CRM

            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

Example:           

Dynamics CRM Field Name : CRMBlocked           

Dynamics CRM OptionSet Names :  ,Ship,Invoice,All

Dynamics CRM OptionSet Values :  (SQL) NULL, 100000000, 100000001, 100000002 

Dynamics NAV Field Name : NAVBlocked           

Dynamics NAV OptionString :  ,Ship,Invoice,All 

Mapping details:

1.
From Dynamics CRM to Dynamics NAV

            NAVOption: If(GreaterThan(Blocked\Value, 0), Blocked\Name, “_blank_”) 

1.
From Dynamics NAV to Dynamics CRM

            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.

Comments

• Anonymous
  July 30, 2014
  pls share same thing for ax

• Anonymous
  July 31, 2014
  @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.

• Anonymous
  October 28, 2014
  Any chance you can release the CRM Connector source code?  I would like to fix the option set bug as described in section C.

• Anonymous
  October 28, 2014
  @Joel - No.  Connector for Microsoft Dynamics source code will not be released externally.

• Anonymous
  October 28, 2014
  @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.

• Anonymous
  October 28, 2014
  @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.