Mapping Microsoft Dynamics NAV OptionStrings and Microsoft Dynamics CRM OptionSets

  • Article

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.