Dynamics Connector Retrieve the Primary and Secondary Address CountryId for AX Customer

In CRM, account addresses are stored in two places. In the account entity there are two sets of address fields. Additionally, there is a customeraddress table that stores these two addresses and any additional addresses for the account.

If you have created a custom country field, it will exist on the account entity, but not on the customeraddress entity. Unfortunately, you can't create custom relationships with the customeraddress entity so it gets pretty complicated.

Unfortunately, I'm not familiar with AX, the Dynamics Connector, or how you have configured either of these, so I can't offer any specific advice. But to simplify matters, you should consider copying the value from your custom Country field into the standard Country/Region field so that the standard text field always contains the same value.

Hi Shaun!

I am facing the same problem.

Is it possible by You to give me the code of custom mapping function to save a bit of time?

Thanks in advance.

Lotar

Hi Lotar,

I used the below mapping functions to retrieve the Primary and Secondary CountryRegion id's.  

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Dynamics.Integration.Mapping;
using Microsoft.Dynamics.Integration.Mapping.Helpers;
using Microsoft.Dynamics.Integration.Mapping.Helpers.CrmAx;
 
namespace MappingFunctions
{
    [MappingHelper]
    public class CustomMappingFunctions : LocalizedMappingHelper
    {
        /// <summary>
        /// This mapping function will find the first address marked as primary and return the countryid.
        /// </summary>
        /// <returns>
        /// A string (address countryid).
        /// </returns>
        [MappingFunctionCategory(Category = "Custom Functions")]
        [MappingFunction(Description = "Gets the CountryRegionId for the first AX address marked as primary.")]
        public static string GetPrimayCountryRegionId(Dictionary<string, object>[] dirPartyRecords)
        {
            if (dirPartyRecords != null)
            {
                Dictionary<string, object> dictionary = dirPartyRecords.FirstOrDefault<Dictionary<string, object>>();
                string key = "PostalAddressView";
                if (dictionary != null && dictionary.ContainsKey(key) && dictionary[key] != null)
                {
                    Dictionary<string, object>[] array = (Dictionary<string, object>[])dictionary[key];
                    for (int i = 0; i < array.Length; i++)
                    {
                        Dictionary<string, object> dictionary2 = array[i];
                        if (dictionary2.ContainsKey("IsPrimary"))
                        {
                            bool? flag = CrmAx2012Helper.ConvertToBooleanFromYesNo(dictionary2["IsPrimary"].ToString());
                            if (flag.GetValueOrDefault() && flag.HasValue)
                            {
                                return (GeneralMappingHelper.GetValueFromDictionary("CountryRegionId", dictionary2).ToString());
                            }
                        }
                    }
                }
            }
            return null;
        }
 
        /// <summary>
        /// This mapping function will find the address at the supplied index and return the countryid.
        /// </summary>
        /// <returns>
        /// A string (address countryid).
        /// </returns>
        [MappingFunctionCategory(Category = "Custom Functions")]
        [MappingFunction(Description = "Gets the CountryRegionId for the AX address in the collection at the supplied index.")]
        public static string GetSecondaryCountryRegionId(Dictionary<string, object>[] addressRelationships, int index)
        {
            if (addressRelationships != null)
            {
                Dictionary<string, object> dictionary = addressRelationships.FirstOrDefault<Dictionary<string, object>>();
                string key = "PostalAddressView";
                if (dictionary != null && dictionary.ContainsKey(key) && dictionary[key] != null)
                {
                    Dictionary<string, object>[] array = dictionary[key] as Dictionary<string, object>[];
                    if (array != null && array.Length >= 2 && array[index].ContainsKey("IsPrimary") && array[index]["IsPrimary"] != null)
                    {
                        bool? flag = CrmAx2012Helper.ConvertToBooleanFromYesNo(array[index]["IsPrimary"].ToString());
                        if (!flag.GetValueOrDefault() && flag.HasValue)
                        {
                            return array[index]["CountryRegionId"].ToString();
                        }
                    }
                }
            }
            return null;
        }
    }
}

Hi Shaun!

You really helped me a lot!

As thank i am sending You my new code built upon yours.

The new function gives the value of any choosen property of address for inputted RecId which You can get by functions GetPrimaryAddressId or GetSecondaryAddressId. The map then can look like this:

=GetAdressPropertyForRecId(Organization, GetPrimayAddressId(Organization), "City")

The code is:

 using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Dynamics.Integration.Mapping;
using Microsoft.Dynamics.Integration.Mapping.Helpers;
using Microsoft.Dynamics.Integration.Mapping.Helpers.CrmAx;

namespace MappingFunctions
{
    [MappingHelper]
    public class CustomMappingFunctions : LocalizedMappingHelper
    {
        /// <summary>
        /// This mapping function will return the value of choosen Property of address for given RecId.
        /// </summary>
        /// <returns>
        /// A string (Address Property Value).
        /// </returns>
        [MappingFunctionCategory(Category = "Custom Functions")]
        [MappingFunction(Description = "Gets the value of choosen Property of AX address for given RecId.")]
        public static string GetAdressPropertyForRecId(Dictionary<string, object>[] dirPartyRecords, Nullable<long> RecIdIn, String PropertyName)
        {
            if (dirPartyRecords != null)
            {
                Dictionary<string, object> dictionary = dirPartyRecords.FirstOrDefault<Dictionary<string, object>>();
                string key = "PostalAddressView";
                if (dictionary != null && dictionary.ContainsKey(key) && dictionary[key] != null)
                {
                    Dictionary<string, object>[] array = (Dictionary<string, object>[])dictionary[key];
                    for (int i = 0; i < array.Length; i++)
                    {
                        Dictionary<string, object> dictionary2 = array[i];
                        if (dictionary2.ContainsKey("RecId") && dictionary2["RecId"].ToString() == RecIdIn.ToString())
                        {
                           return (GeneralMappingHelper.GetValueFromDictionary(PropertyName, dictionary2).ToString());
                        }
                    }
                }
            }
            return null;
        }
    }
}

Hi Lotar,

Glad the code was helpful and thanks for sharing your enhanced function. What a good idea to reuse the existing Connector functions.

Although I have found issue with the standard function =GetSecondaryAddressId(Organization, 1) and would be interested to hear if your also encounter the same issue. The function only looks for addresses in the second location in the array of addresses. In our testing this is not always the case and the secondary address can be the first address in the XML returned from AX service. This causes the CRM secondary address integration key in CRM to not be populated.

This seems to happen when first creating an Account in CRM with primary and secondary addresses, submitting for integration, record integrates to AX and then integrates back to CRM.

An example of the Organization XML being returned from the AX Customer Service. Note the secondary address is the first address.

  <Organization xsi:type="AxdEntity_Organization_DirOrganization" class="entity" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <LanguageId>en-nz</LanguageId>
        <Name>Shaun test 1 26/11/12</Name>
        <NameAlias>Shaun test 1 26/11/1</NameAlias>
        <PartyNumber>14322</PartyNumber>
        <RecId>5637182515</RecId>
        <RecVersion>1</RecVersion>
        <ABC>None</ABC>
        <OrganizationName class="entity">
          <Name>Shaun test 1 26/11/12</Name>
          <RecId>5637163347</RecId>
          <RecVersion>1</RecVersion>
          <ValidFrom>2012-11-26T01:46:18Z</ValidFrom>
          <ValidTo>2154-12-31T23:59:59Z</ValidTo>
        </OrganizationName>
        <PostalAddressView class="entity">
          <Address>St 2.1
St 2.2
Auckland 1011</Address>
          <CountryRegionId>NZL</CountryRegionId>
          <IsLocationOwner>Yes</IsLocationOwner>
          <ISOcode>NZ</ISOcode>
          <IsPrimary>No</IsPrimary>
          <IsPrivate>Yes</IsPrivate>
          <Location>5637185756</Location>
          <LocationName>SECONDARY</LocationName>
          <Party>14322</Party>
          <PartyLocation>5637181443</PartyLocation>
          <PostalAddress>5637171459</PostalAddress>
          <RecId>5637181443</RecId>
          <Roles>Delivery</Roles>
          <Street>St 2.1
St 2.2
Auckland 1011</Street>
          <TimeZone xsi:nil="true"></TimeZone>
          <ValidFrom>2012-11-26T01:46:20Z</ValidFrom>
          <ValidTo>2154-12-31T23:59:59Z</ValidTo>
        </PostalAddressView>
        <PostalAddressView class="entity">
          <Address>St 1
St 2
Dunedin 9016</Address>
          <CountryRegionId>NZL</CountryRegionId>
          <IsLocationOwner>Yes</IsLocationOwner>
          <ISOcode>NZ</ISOcode>
          <IsPrimary>Yes</IsPrimary>
          <IsPrivate>No</IsPrivate>
          <Location>5637185757</Location>
          <LocationName>PRIMARY</LocationName>
          <Party>14322</Party>
          <PartyLocation>5637181444</PartyLocation>
          <PostalAddress>5637171460</PostalAddress>
          <RecId>5637181444</RecId>
          <Roles>Business</Roles>
          <Street>St 1
St 2
Dunedin 9016</Street>
          <TimeZone xsi:nil="true"></TimeZone>
          <ValidFrom>2012-11-26T01:46:21Z</ValidFrom>
          <ValidTo>2154-12-31T23:59:59Z</ValidTo>
        </PostalAddressView>
      </Organization>