Using a GP Web Service Extension in the Connector for Microsoft Dynamics GP

Chris Roehrich - Click for blog homepageThe Connector for Microsoft Dynamics provides a powerful integration platform that can be customized and extended with the CRM Connector SDK and Web Services for Microsoft Dynamics GP SDK tool sets.  The CRM Connector for Microsoft Dynamics GP page in PartnerSource has various downloads to get you started with the CRM Connector including the SDK download.   You can find the GP Web Services SDK download here.  Additionally, the Dynamics GP Web Services SDK page on MSDN contains the same reference information found in the download page and is available to those without accessing PartnerSource or CustomerSource and can be helpful for quick access.  

One of the most powerful features of the CRM Connector is that you can customize the existing maps using out of the box functions or write your own functions using the guidance of the Connector SDK and Visual Studio.  In addition to writing new functions to extend maps, you can write your own Adapters to integrate from non-ERP systems like a folder on the file system holding XML or Excel files.   So you can leverage all of the powerful features of the Connector like retry logic, email notifications, and event logging in your own integration.   You can setup your integration to run when you want using the Connector client UI.  It's a very impressive set of features that you get for free when using the Connector as your integration platform.

The Map Limitation

We had a recent support incident where the customer was using the CRM Connector to integrate Accounts from Microsoft CRM to Dynamics GP using the default Account to Customers map.    One of the requirements was to bring over the email address for the customer record.  Since the version of Dynamics GP was 10.0 in this case, we are dependant on the GP Web Services object model.  In version 10.0, the email address for a customer is a property of the InternetAddresses type which is a read-only type.  This read-only limitation with this specific property is not the case for Dynamics GP 2010 Web Services.   However, the concept here can be applied to limitations that can be found in Dynamics GP Web Services 2010.   Since GP Web Services has a limitation here, then the perception is that the Account to Customer map has a limitation.  So we need to overcome the limitation with the Account to Customer map not being able to integrate a Customer internet address, specifically an email address.

Since the InternetAddresses type is read only in version 10.0, one method to integrate this data would be to write an Extension for GP Web Services using the guidance we provide in the GP Web Services SDK.  Then expose this custom Extension in a custom CRM Connector function that the Account to Customer map will use.

The GP Web Services Extension Assembly

Every object in GP Web Services that inherits from the BusinessObject type has a property called Extensions which is a collection of Extension types.  An Extension type has two properties, ExtensionID (string) and DocExtension (XmlElement).   Each BusinessObject thus can have a collection of XML structured data (in the form of a XmlElement) associated to it.   What makes using Extensions powerful is that you can define your XML structured data how you want it and leverage the Eventing that is in GP Web Services.  The guidance from the GP Web Services Programmers guide on leveraging Extensions that I used is here.

The SY01200 in the company database is the Internet Addresses table that will be updated.   This table stores the email address in the INET1 field for a given Master_ID and ADRSCODE combination.  The Master_ID will be the Customer Number (CUSTNMBR).   So the pieces of data that we need to properly update the table are Master_ID, ADRSCODE, and INET1.   Knowing this information provides the starting point of what the DocExtension for the Extension should look like.  The ExtensionID will be "CustomerEmail" and the DocExtension XML format for the Extension will look like something like the following:

    <CustomerEmail>
      <CUSTNMBR>AARONFIT0001</CUSTNMBR>
      <ADRSCODE>PRIMARY</ADRSCODE>
      <EMAIL>email@email.com</EMAIL>
    </CustomerEmail>

In the code for the class library that will process the Extension, a public static class and method must be used.  Note - In your project in Visual Studio, add references to the Microsoft.Dynamics.Common, Microsoft.Dynamics.Common.Types, and Microsoft.Dynamics.GP.BusinessLogic assemblies located in the C:\Program Files\Microsoft Dynamics\GPWebServices\WebServices\Bin\ folder.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Dynamics.Common;
using Microsoft.Dynamics.GP;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

 

namespace EmailAddressExtension
{
public static class EmailAddressProviderExt
{
// Declare private variable of type Microsoft.Dynamics.Common.Connection
private static Connection connection;

        public static void CreateCustomerEmail(object sender, BusinessObjectEventArgs e)
{
bool found;
int rowsAffected;

            string custnmbr;
string adrscode;
string email;

            string updateStatement;
string insertStatement;

            Customer customer;
Extension CustomerEmailExtension = new Extension();

            if (e.BusinessObject.GetType() == typeof(Customer))
{
customer = (Customer)e.BusinessObject;
// Look at the Extension list passed along
found = false;
foreach (Extension ext in customer.Extensions)
{
if (ext.ExtensionId == "CustomerEmail")
{
CustomerEmailExtension = ext;
found = true;
break;
}
}
if (found == true)
{
// Found an extension, so it should be processed
XmlElement customerEmail;
customerEmail = CustomerEmailExtension.DocExtension;

                    XmlNodeList nodeList;
nodeList = customerEmail.ChildNodes;

//Customer Number
custnmbr = nodeList[0].InnerText.ToString().Trim();

//Address Code
adrscode = nodeList[1].InnerText.ToString().Trim();

//Email Address
email = nodeList[2].InnerText.ToString().Trim();

// Get the connection to the database for the current company
connection = Connection.GetInstance();

                    // The SQL statement to update SY01200 table for the Customer (Master_Type = 'CUS')
updateStatement = "UPDATE SY01200 SET INET1='" + email + "' WHERE Master_Type = 'CUS' AND Master_ID = '" + customer.Key.Id + "'" +
"AND ADRSCODE = '" + adrscode + "'";

// Create the SQL connection
SqlCommand command = new SqlCommand(updateStatement);
SqlConnection sqlConnection = new SqlConnection(connection.GetConnectionString(e.Context.OrganizationKey));
command.Connection = sqlConnection;

// Open the SQL connection
sqlConnection.Open();

// Execute the SQL statement
rowsAffected = command.ExecuteNonQuery();

                    if (rowsAffected == 0)
{
// The row did not exist, so try creating it.
insertStatement = "INSERT SY01200 (Master_Type, Master_ID, ADRSCODE, INET1, INETINFO) VALUES ('CUS','" + customer.Key.Id + "','"
+ adrscode + "','" + email + "', '')";
command.CommandText = insertStatement;
rowsAffected = command.ExecuteNonQuery();
}

// Close the SQL connection
sqlConnection.Close();
}
}
}
}
}

After you compile the code, the assembly will be copied to the GP Web Services installation location.   For version 10.0, this would be the C:\Program Files\Microsoft Dynamics\GPWebServices\WebServices\Bin folder.   For version 2010, this would be the C:\Program Files\Microsoft Dynamics\GPWebServices folder.  The last step is to edit the BusinessObjectsFile.config file in the \ServiceConfigs folder by adding a new DictionaryEntry section.  This allows the custom extension code to fire off when the GP web services event Created or Updated happens for the Customer business object.  This will be added to the BusinessObjectsFile.config file: 

<DictionaryEntry>
<Key xsi:type="xsd:string">Microsoft.Dynamics.GP.Customer</Key>
<Value xsi:type="BusinessObjectConfiguration">
<Event>
<EventName>Created</EventName>
<EventHandlerType>
<Type>Microsoft.Dynamics.Common.BusinessObjectEventHandler</Type>
<Assembly>Microsoft.Dynamics.Common</Assembly>
</EventHandlerType>
<EventHandler>
<SoftwareVendor>DynamicsSupport</SoftwareVendor>
<Type>EmailAddressExtension.EmailAddressProviderExt</Type>
<StaticMethod>CreateCustomerEmail</StaticMethod>
<Assembly>EmailAddressExtension</Assembly>
<Execute>true</Execute>
</EventHandler>
</Event>
<Event>
<EventName>Updated</EventName>
<EventHandlerType>
<Type>Microsoft.Dynamics.Common.BusinessObjectEventHandler</Type>
<Assembly>Microsoft.Dynamics.Common</Assembly>
</EventHandlerType>
<EventHandler>
<SoftwareVendor>DynamicsSupport</SoftwareVendor>
<Type>EmailAddressExtension.EmailAddressProviderExt</Type>
<StaticMethod>CreateCustomerEmail</StaticMethod>
<Assembly>EmailAddressExtension</Assembly>
<Execute>true</Execute>
</EventHandler>
</Event>
</Value>
</DictionaryEntry>

 

The Custom Function for the Connector Map

There are three pieces of information needed to update the SY01200 table properly:  CUSTNMBR, ADRSCODE, and EMAIL.   So these three strings will be passed to the function as parameters.   These values can be passed to our function by using the mapping tool in the Connector client UI.   We also know that we have to create the XML structure for the Extension object in the Account to Customer map.   So the function for GP 10.0 will return a XmlElement type which is a member of the System.Xml namespace.   If you are using GP 2010, you will need to return a XElement type which is a member of the System.Xml.Linq namespace.

I used the guidance from the CRM Connector SDK download to get started.   Included in the download is a PDF that describes the process of creating a custom mapping function.  In my code below, I created a .Net 4.0 C# Library project which has a reference to the Microsoft.Dynamics.Integration.Mapping.dll located in the C:\Program Files (x86)\Microsoft Dynamics\Microsoft Dynamics Adapter\ folder. 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Xml;
using System.Xml.Linq;

// Using directive for Connector Mapping assembly
using Microsoft.Dynamics.Integration.Mapping;

namespace InternetAddressesFunction
{
[MappingHelper]
public class InternetAddressFunction : LocalizedMappingHelper
{
static XmlDocument doc;
static XmlElement customerEmailXML;
static XmlElement custnmbrXML;
static XmlElement adrscodeXML;
static XmlElement emailXML;
static XmlText text;

        static XElement MyXElement;

        [MappingFunction(Description = "Allows email address to be provided for the Customer. Returns XmlElement.")]
[MappingFunctionCategory(Category = "Connector SDK Custom Functions")]
public static XmlElement DocExtension1ForCustomerEmail(string CustomerID, string AddressID, string EmailAddress)
{
/*Make the XML extension document so it looks something like:

            <CustomerEmail>
<CUSTNMBR>AARONFIT0001</CUSTNMBR>
<ADRSCODE>HQ</ADRSCODE>
<EMAIL>contact@aaronfitz.com</EMAIL>
</CustomerEmail>

*/

            doc = new XmlDocument();
customerEmailXML = doc.CreateElement("CustomerEmail");

            // CUSTNMBR (Customer Number)
custnmbrXML = doc.CreateElement("CUSTNMBR");
text = doc.CreateTextNode(CustomerID);
custnmbrXML.AppendChild(text);
customerEmailXML.AppendChild(custnmbrXML);

            // ADRSCODE (Address Code)
adrscodeXML = doc.CreateElement("ADRSCODE");
text = doc.CreateTextNode(AddressID);
adrscodeXML.AppendChild(text);
customerEmailXML.AppendChild(adrscodeXML);

            // Email Address
emailXML = doc.CreateElement("EMAIL");
text = doc.CreateTextNode(EmailAddress);
emailXML.AppendChild(text);
customerEmailXML.AppendChild(emailXML);

return customerEmailXML;
}     

    }
}

 

The DocExtension1ForCustomerEmail method in the above code accepts the three strings and returns a XmlElement.  Note there are special Mapping attributes on the class and method that need to be applied.  After you build the assembly it will be copied to the C:\Program Files (x86)\Microsoft Dynamics\Microsoft Dynamics Adapter\MappingHelpers folder. 

If using version 2010 for Dyamics GP, then you need to return a XElement type.   The DocExtension type in the Extension object is different between the Dynamics GP 10 and Dynamics GP 2010 Native endpoint for GP Web services.   An example of the function code to return a XElement is below:

using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Xml;
using System.Xml.Linq;

// Using directive for Connector Mapping assembly
using Microsoft.Dynamics.Integration.Mapping;

namespace InternetAddressesFunction
{
[MappingHelper]
public class InternetAddressFunction : LocalizedMappingHelper
{

        static XElement MyXElement;

        [MappingFunction(Description = "Allows email address to be provided for the Customer.")]
[MappingFunctionCategory(Category = "Connector SDK Custom Functions")]
public static XElement DocExtension2ForCustomerEmail(string customerID, string addressID, string emailAddress)
{
/*Make the XML extension document so it looks something like:

            <CustomerEmail>
<CUSTNMBR>AARONFIT0001</CUSTNMBR>
<ADRSCODE>HQ</ADRSCODE>
<EMAIL>contact@aaronfitz.com</EMAIL>
</CustomerEmail>

*/

            MyXElement = XElement.Parse("<CustomerEmail><CUSTNMBR>" + customerID + "</CUSTNMBR><ADRSCODE>" +
addressID + "</ADRSCODE><EMAIL>" + emailAddress + "</EMAIL></CustomerEmail>");

            return MyXElement;
}
}
}

In order for the mapping tool in the Connector client to use the custom function, the CustomerObjectProvider.config file must be edited to add the DocExtension property to the Extension object.   The CustomerObjectProvider.config file currently ships with the Extensions object missing the DocExtension property so it must be modified.   This issue should be fixed in a future release.

For Dynamics GP 10, navigate to the C:\Program Files (x86)\Microsoft Dynamics\Microsoft Dynamics Adapter\Adapters\Microsoft.Dynamics.Integration.Adapters.Gp10\ObjectConfig folder and open up the CustomerObjectProvider.config file. Edit the file so the Extension type includes the DocExtension field like the following: 

    <Type xsi:type="ComplexType" Name="Extension" ClrTypeName="Microsoft.Dynamics.Integration.Adapters.Gp10.GPWebService.Extension, Microsoft.Dynamics.Integration.Adapters.Gp10, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
<Fields>
<Field Name="ExtensionId" TypeName="System.String" DisplayName="Extension ID" IsRequired="false" />
<Field Name="DocExtension" TypeName="System.Xml.XmlElement" DisplayName="DocExtension" IsRequired="false" />
</Fields>
</Type>

 Near the bottom of the file, add a SimpleType reference for the System.Xml.XmlElement like the following: 

<Type xsi:type="SimpleType" Name="System.Xml.XmlElement" ClrTypeName="System.Xml.XmlElement, System.XML, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />   

If you are using Dynamics GP 2010, the steps are similar but different text is added to use System.Xml.Linq.Element like the following: 

    <Type xsi:type="ComplexType" Name="Extension" ClrTypeName="Microsoft.Dynamics.Integration.Adapters.Gp2010.GPWebService.Extension, Microsoft.Dynamics.Integration.Adapters.Gp2010, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
<Fields>
<Field Name="ExtensionId" TypeName="System.String" DisplayName="Extension ID" IsRequired="false" />
<Field Name="DocExtension" TypeName="System.Xml.Linq.XElement" DisplayName="DocExtension" IsRequired="false" />
</Fields>
</Type>

 Add the SimpleType near the bottom of the file:

<Type xsi:type="SimpleType" Name="System.Xml.Linq.XElement" ClrTypeName="System.Xml.Linq.XElement, System.Xml.Linq, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />  

 

Working with the Custom Function

The last step is to use the Connector client to edit the Account to Customer map.   This is the part where we can finally see all the work of the previous steps!  When you go to the Account to Customer map you should see the DocExtension field for the Extension object like the below screen shot.   The tooltip should return the full name of the type you are using depending on the version. 

 

 

 

To map the DocExtension field, click the pencil icon that is to the right of the field and you will see the Destination Field Mapping form pop up:

 Select the Use a function option and you should see the custom function once you select the Function category. The text of the Function category comes from the custom function code using the MappingFunctionCategory attribute so this is up to you on what you call it.

The next window is where you will map the three string parameters.  All three fields will be mapped to source fields.  The CustomerID destination field will be mapped to the Account\Dynamics Integration Key field.  The AddressID destination field will be mapped to Account\Address 1 Dynamics Integration Key. The EmailAddress destination will be mapped to the Account\E-mail source field. 

After you map the DocExtension field, you can provide a constant value to the Extension ID destination field.   In this case, the Extension ID is called "CustomerEmail" .  This is the ExtensionID we provided in our custom GP Web Services Extension code.  The finished mapping will look like the following:

 

 

You can now click the Save button in the Connector client for the changes to the map to be saved.  Now when you enter an email address for an Account in CRM, it should flow to the Customer's Internet Address information in Dynamics GP.  Specifically it should show up in the INET1 field for the SY01200 table.

Until next time,

Chris

// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)