Mapping Functions: What are they and where can I find out more about them?
When integrating data between Microsoft Dynamics CRM and the Microsoft Dynamics ERPs, there is always going to be a need to adjust the “shape” of the data so that it can be successfully integrated between the two systems. You may need to change the format of the data or possibly aggregate multiple source fields to properly form the accurate value that is to be written into the destination system. Let me give you some examples:
Type Change:
In the “Customer to Account” map for Microsoft Dynamics GP 10 to Microsoft Dynamics CRM, there is a field named “State” that represents, you guessed it, the Account state (active or inactive). In CRM, the values for this state field are determined by the AccountState Enumeration which has the values 0 (Active) and 1 (Inactive). The problem here is that the source Customer entity from Microsoft Dynamics GP 10 has a field named “Active” which is a Boolean type (true or false). In order to map the “Active” source field to the “State” destination field requires the use of a mapping function:
=If(Active, 0, 1)
Here is the documentation around the If mapping function:
Declaration: If (condition, trueValue, falseValue) : T
Description: Returns ‘trueValue’ if the specified ‘condition’ is true; otherwise, returns ‘falseValue’.
Format Change:
In the same “Customer to Account” map for Microsoft Dynamics GP 10 to Microsoft Dynamics CRM, mapping the “All Addresses\Customer Address\Main Phone” destination field from the “All Addresses\Customer Address\Phone 1\Phone Number” source field is even more interesting. This is because the source field comes out of Microsoft Dynamics GP with no formatting applied. Therefore, if we directly mapped the source field to the destination field we would get something like this in CRM: 9995551234
Wouldn’t it be nice if we could actually get this phone number formatted more appropriately? Say like: (999) 555-1234. Ah, but we can with the help of Mapping Functions!
Below is the complete mapping for the source field to the destination field:
=If(GreaterThan(Length(All Addresses\Customer Address\Phone 1\Phone Number), 10), Replace(Replace(All Addresses\Customer Address\Phone 1\Phone Number, "[^0-9]", ""), "(\d{3})(\d{3})(\d{4})(\d{0,4})", "($1) $2-$3 Ext. $4"), Replace(Replace(All Addresses\Customer Address\Phone 1\Phone Number, "[^0-9]", ""), "(\d{3})(\d{3})(\d{4})", "($1) $2-$3"))
Here are the definitions for the different mapping functions used above. I will leave it as an exercise for the reader to understand how this all works. J
Declaration: If (condition, trueValue, falseValue) : T
Description: Returns ‘trueValue’ if the specified ‘condition’ is true; otherwise, returns ‘falseValue’.
Declaration: GreaterThan (value1, value2) : Boolean
Description: Returns true if ‘value1’ is greater than ‘value2’; otherwise, false.
Declaration: Length (text) : Int32
Description: Gets the number of characters in ‘text’.
Declaration: Replace (text, pattern, format) : String
Description: Replaces all strings in ‘text’ that match the specified regular expression ‘pattern’ with a specified replacement string ‘format’.
Of course, if you take a look at the previous post: Integrating e-mail address from Dynamics CRM to the Internet Addresses in Dynamics GP 2010, you will see that mapping functions played a key role in making this scenario possible.
So I am sure by now you are asking yourself, “How do I find out more about the available mapping functions in the Connector?”
To see all the functions available with Connector:
- 1. Go to any map and click the
icon on any field to bring up the Destination Field Mapping wizard. - 2. Then, choose the “Use a function” option. This will take you to a window where you can scroll through the available mapping functions or filter the available mapping functions by selecting a “Function category:”.
- 3. Under the list of mapping functions, you will find an area where the definition of the selected mapping function will be displayed.
Hopefully this makes sense to you and you find the experience similar to other Microsoft products.
If you have a question or recommendation; drop us a comment on the blog or maybe even give us a suggestion on Microsoft Connect. Either way, we’d love to hear from you.