Connector -- Integrating Dynamics ERP with Dynamics CRM

Connector for Microsoft Dynamics is an integration solution which is targeted specifically at the Microsoft Dynamics product family. Connector provides an out of the box integration between Dynamics CRM and Dynamics ERP solutions.

Mapping Functions: What are they and where can I find out more about them?

Mapping Functions: What are they and where can I find out more about them?

  • Comments 11

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. 1.   Go to any map and click the    icon on any field to bring up the Destination Field Mapping wizard.
  2. 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. 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.

PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the link in the Related Links section (scroll up, on right hand side) to ask on the Dynamics Communities. If you ask on the Communities, others in the community can respond and the answers are available for everyone in the future.
Leave a Comment
  • Please add 7 and 3 and type the answer here:
  • Post
  • Hello,

    I have problems to know what i have to fill at the lookup fields in mapping configuration.

    Where can i find documentation for this ?

    Thanks,

  • The documentation for Connector is provided with the product download. If that doesn't provide you with what you need, you can contact Dynamics Support for further assistance. Questions posted on http://community.dynamics.com/ also can be successful.

  • create custom function

    www.dynamics-community.at/.../NAVCRM-Dynamics-Connectore28093GetNextNo-(Series)-from-NAVe28093create-custom-Mapping-function.aspx

    Regards,

    Rene

  • Hello -

    We are using GP 2010 with CRM 2011 (upgraded to CRM 2011). One of the things we need to happen is for our Sales Reps to actually see the quantity on hand for products. Do you know if we can map to the “Available Quantity” in Dynamics GP for Products? Right now, “On Hand Quantity” is available, but that doesn’t actually give an accurate picture of the product on hand because it doesn’t account for product that’s been allocated, but not yet shipped off premise.

    I don’t see it listed when I try to go into the Stock Item: Quantities>item to set up the mapping. I’d prefer not to have to map over “Allocated Quantity” to a field in CRM and then run some kind of jScript to do that calculation on the CRM side. We want to just pull in the Actual unallocated Product that's on hand.

    Mike

  • Hi Mike,

    I know of one partner who also wanted to provide Quantity information to their sales team. That partner elected to use a Microsoft SQL Reporting Services report to regulary publish Item Quantity information to the CRM portal, exposed in an iFrame on the Item Card in CRM.

    I will still check into if you can map the Quantity Available field.

    Char

  • I'm attempting to run the initial sync for Accounts between two established programs, CRM 4.0 On-prem and AX 2009.

    The two systems use a common Account Number for customers. How do I check if an Account already exists in CRM and to update that account with data from AX? If it doesn't exist in CRM, then create a new record.

  • I am hoping that someone can help me out on this blog. I just installed the Connector between GP and CRM for the first time, and go the integration working correctly one way between customers in GP and accounts in CRM. Everything is as intended out of the box.

    However, the client has a need to do the following:

    They are a distributor of roofing supplies. They have customers that have multiple ships tos in GP. With then integration out of the box, those correctly get integrated to 'More Addresses' in CRM. However, all of their contact is with these Ship To sites. They would like to have full CRM functionality at that level of the account. Currently, there is no CRM functionality at 'More Addresses'. Does anyone have any idea on some good way to tackle this problem?

  • Mike,

    We just posted more info on working with Quantity on Hand for items. See blogs.msdn.com/.../changing-the-default-behavior-of-a-connector-integration.aspx. Hope this helps.

  • To "TheNewGuy00" -- the Connector documentation has a section on duplicate checking that should help you.

  • Char - thanks for the updates! I'll take a look at the blog post, but the SSRS option may be a viable alternative, as well. I'll let you know how it works out.

    ~Mike

  • Hi CharG,

      Thansk for your post! I have some problem with "if statement". I don't want to update Crm value when "condition is true". I wrote =If(EqualTo(Address, ""), "", Address) but update with blank

    My Logic

    If (Address = "")

       Stay CRM Address     (now update with blank)

    ELSE

       Update with NAV value

    How may i do it?

    Regards,

    Yukon

Page 1 of 1 (11 items)