The Technology Corner: Linda Rosencrance looks at IE9 Beta across the CRM spectrum

September 23, 2010

My friend, Linda Rosencrance over at MSDynamicsWorld takes a look at early attempts to run Microsoft Dynamics CRM 4 and 5 with the new Internet Explorer 9 Beta in her new article. She also features some of my earlier comments and findings with running Microsoft Dynamics GP 10.0 and Microsoft Dynamics GP 2010 with IE9 Beta. The bottom line is, IE9 Beta is not going to be supported by Microsoft whether you are running Microsoft Dynamics CRM or Microsoft Dynamics GP. Any attempts of running IE9 Beta should be conducted in a non-production environment.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Advertisements

What are all those GPSFOINTEGRATIONID columns in some tables

September 10, 2010

I thought I would close the week with a very little known fact to new comers (and some not so new) to the Microsoft Dynamics GP world.

First some history

Around mid 1998 (or so), Siebel Systems formed an alliance with the then Great Plains Software Inc. to deliver a suite of front- and back-office applications. Great Plains’ back-office applications included an integrated suite of accounting, financial, and supply chain modules. Under the agreement the new suite would add Siebel’s front-office applications covering sales, marketing, and e-business functionality. The combination would allow users to complete sales transactions over the Web, for instance. Great Plains delivered the first component of its new package in November 1999 as the Sales and Marketing Series of Great Plains Siebel Front Office, with customer service and call-center applications to follow in 2000. The suite was aimed at small and mid-sized businesses.

The new Great Plains Siebel Front Office product would initially offer a module to automate sales, marketing, service and electronic business processes. With another module for customer service following in January of 2000, this was the first step toward overall front office/back office solutions.

Fast-forward to April of 2001, Microsoft completed its acquisition of Great Plains and soon began working on other plans to phase out GPSFO, pushing overall its .NET technology and its vision for a .NET based CRM solution. As a result, Microsoft CRM 1.0 was released in January of 2003… the rest is, well, history!

So, how is history related to the title of this post?

So now that you know that your beloved Microsoft Dynamics GP used to play some serious game with Siebel, it’s just about right that they had to have some way of talking back to each other, this is, integration.

As an integration mechanism between GPSFO and Great Plains Dynamics, the Dynamics dictionary (DYNAMICS.DIC) went through a few changes required to accomodate integration points between the two systems. Today, there are still vestiges of these changes in the SOP10100 (Sales Transaction Work), SOP10200 (Sales Transaction Amounts Work), RM00101 (RM Customer Master), RM00102 (RM Customer Address Master) tables. These tables all share a GPSFOINTEGRATIONID, INTEGRATIONID, and INTEGRATIONSOURCE columns, used to track and exchange information between GPSFO and Dynamics. Even the Field Service module had piece of the action with the SVC00203 (Service Call Line Detail) and the SVC_FO_ID column.

Now, can I reuse those columns to store my own data?

As the say goes, not because you can means you should. While these columns are no longer used to store data in these tables — according to the Microsoft Dynamics GP SDK they are marked as “reserved” — you should not begin to store your own data in them. Microsoft may choose in the future to re-purpose or simply drop these from the database schema and your data will be gone. Hence, it’s not recommended to use these fields for any purpose. Alternatively, you can create your own custom tables, whether in Dexterity or natively in Microsoft SQL Server to store any related sales information.

As a final request, please add your comments on your experiences (or nightmares, depending who you ask) with GPSFO. Let’s see how many of you veterans are still out there and tackled the “new brave world” (or the ‘old’ as it has been a good number of years since).

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


Using SQL Server CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM: Configuring SQL Server and creating table triggers

August 21, 2010

In my previous article I outlined some of the steps and methods needed on the Visual Studio side to create an integration to Microsoft CRM. In particular, the article showed you how to create the SQL CLR methods and how these methods interact with the Microsoft CRM Web services to create or update an item in the Product entity.

Following along, once you have created the assemblies (integration assembly and XML serialization assembly), you must proceed to install these in the Framework directory — the assemblies were created targeting the .NET Framework 3.5, so they were installed there — and also register these in the Global Assembly Cache (GAC) under the %windir%\assembly folder.

Once the assemblies are GAC’ed you can now begin the process of registering these with Microsoft SQL Server 2005, 2008, or 2008 R2. To begin registering the assemblies with SQL Server, we must first define an Asymmetric Key from the signed assembly created in our previous project.

USE master;
GO

CREATE ASYMMETRIC KEY CrmKey
FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v3.5\Crm.Integration.dll'
GO

An asymmetric key is a securable entity at the database level. In its default form, this entity contains both a public key and a private key. When executed with the FROM clause, CREATE ASYMMETRIC KEY imports a key pair from a file or imports a public key from an assembly. For additional information on asymmetric keys click here.

Next, you must define a SQL Server login that’s associated to the asymmetric key for code signing purposes. One of the characteristics of the .NET Framework is that all external resources being accessed will require a certain level of trust. SQL Server accomplishes this by using a login for code signing with specific permissions to the outside world.

USE master;
GO

CREATE LOGIN [crmlogin] FROM ASYMMETRIC KEY [CrmKey];
GO

GRANT UNSAFE ASSEMBLY TO crmlogin;
GO

For more information on granting permissions to assemblies click here.

Once we have created the asymmetric key, it’s now time to create the assemblies in your company database.

USE [CompanyDB];
GO

CREATE ASSEMBLY [Crm.Integration]
FROM 'C:\Windows\Microsoft.NET\Framework\v3.5\Crm.Integration.dll'
WITH PERMISSION_SET = UNSAFE;
GO

CREATE ASSEMBLY [Crm.Integration.XmlSerializers]
FROM 'C:\Windows\Microsoft.NET\Framework\v3.5\Crm.Integration.XmlSerializers.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

For more information on creating assemblies, click here.

With the assemblies created, it’s now time to expose our CLR stored procedure to SQL Server. In order to register our CLR method, we use the standard CREATE PROCEDURE statement with a twist:

SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[crmInsertProduct]
  @itemNumber       NVARCHAR(31),
  @itemDescription  NVARCHAR(100),
  @VendorName     NVARCHAR(65),
  @VendorItem       NVARCHAR(31),
  @ItemShipWeight   NUMERIC(19,5),
  @defaultUofM     NVARCHAR(20),
  @defaultUofMSched NVARCHAR(20),
  @defaultPriceList NVARCHAR(20),
  @currencyID       NVARCHAR(15),
  @decimals     INT,
  @quantityOnHand   NUMERIC(19,5),
  @listPrice        NUMERIC(19,5),
  @priceListPrice   NUMERIC(19,5),
  @standardcost     NUMERIC(19,5),
  @currentCost     NUMERIC(19,5),
  @productTypeCode  INT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Crm].[Integration].[clrProcedures].[CreateProduct]
GO
SET ANSI_NULLS OFF
GO
SET ANSI_WARNINGS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

GRANT EXECUTE ON [dbo].[crmInsertProduct] to [DYNGRP]
GO

Note that the stored procedure must be created with the same number of parameters as the CLR method.

Finally, we can create a trigger on the IV00101 table to call the stored procedure and pass in the parameters required.

Here are some final notes from and things I had to implement at the SQL Server configuration level to make all this work:

1. First, you must enable CLR integration on SQL Server to allow it to execute assemblies. To enable CLR integration, you must change the ‘CLR Enabled’ option in SQL Server configuration.

USE master;
GO
EXEC sp_configure 'show advanced option', '1';
GO
RECONFIGURE;
GO
EXEC sp_configure 'CLR Enabled', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'show advanced option', '0';
GO
RECONFIGURE;
GO

2. In order to recreate all the above objects, you must first drop the stored procedure, then drop the assemblies, then login, and finally the asymmetric key, this is, objects need to be dropped in reverse order to avoid dependency errors.

USE [CompanyDB]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[crmInsertProduct]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[crmInsertProduct]
GO

IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'Crm.Integration.XmlSerializers' and is_user_defined = 1)
DROP ASSEMBLY [Crm.Integration.XmlSerializers]

GO

IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'Crm.Integration' and is_user_defined = 1)
DROP ASSEMBLY [Crm.Integration]
GO

USE master;
GO

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'crmlogin')
DROP LOGIN [crmlogin]
GO

DROP ASYMMETRIC KEY CrmKey;
GO

I hope you had a good time reading this series. A lot of what you read here I had to learn on the fly, so a lot of reading and research went into building this integration approach. I am sure there are things that could be improved, but this is the down and dirty version.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


Using SQL CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM: Creating a CLR assembly and working with CRM web methods

August 18, 2010

Before we get started, there are a few rules: a) I assume you have good working knowledge of both Microsoft Dynamics GP and Microsoft CRM and that you know enough about the Item Master in GP and the Product entity in CRM, b) you are familiar with Microsoft Visual Studio and can create class libraries and create Web references, c) you have done some coding with either VB.NET and/or C#, and d) you will not ask me if I have the same code snippets in VB.NET. As I have said in multiple occassions — no offense to VB developers — when I work on commercial grade code I will choose C# over VB.NET any day of the week.

A bit of a reminder of the objective of today’s code: a) we will create our CLR methods that will serve as bridge to the Microsoft CRM web methods. The resulting assembly will be registered on SQL Server with the CLR methods exposed as stored procedures that can be called from a trigger, and b) we will create the code that will allow us to establish a connection to Microsoft CRM and in turn insert a new or update an existing Product in CRM.

We begin by creating a class library project and renaming our default class library file to clrProcedures.cs. Once this is done, we can start declaring all namespaces to help us control the scope of class and method names that we will be using throughout the project. In particular, SQL Server CLR methods will benefit from using the Microsoft.SqlServer.Server namespace contained in the System.Data.dll assembly.

clrProcedures.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web.Services.Protocols;
using Microsoft.SqlServer.Server;
using Crm.Integration;

Also note that in the above code, I have declared the Crm.Integration namespace. This namespace will be created as a new Class Library file (Crm.Integration.cs) within our project further on in this article.

We must now implement the clrProcedures class. One note about CLR methods is that they are not encapsulated within a namespace and rather begin with the class declaration. This behavior is by design. Within our clrProcedures class, we will create a method, CreateProduct, that can be registered as a stored procedure in SQL Server. We will declare all the parameters that will be passed to the stored procedure. I believe these are pretty self-explanatory, but if you have any questions please follow up with a comment on the article.

public class clrProcedures
{
    [SqlProcedure]
    public static void CreateProduct(
          string itemNumber
        , string itemDescription
        , string vendorName
        , string vendorItem
        , decimal itemShipWeight
        , string defaultUnitOfMeasure
        , string defaultUnitOfMeasureSched
        , string defaultPriceLevel
        , string currencyID
        , int decimalsSupported
        , decimal quantityOnHand
        , decimal unitPrice
        , decimal priceLevelPrice
        , decimal standardcost
        , decimal currentCost
        , int productTypeCode
    )

Now we will proceed to create a few local variables, particularly the CRM server name, the CRM server port, and CRM Organization Name. These will be passed to our connection method to, well, open a connection to CRM. These values are read from a custom SQL table, dbo.crmInfo, in our company database. You may ask, why not create these values in a configuration file? One of the goals for my client was to provide easy access to database administrators to quickly reconfigure CRM server names and organization names without having to bother the network administrators, so it was easier to store this information in a table. In turn, our configuration file would be left to the network adminstrators to configure the address of the CRM web services as needed. My client is a public company and required segregation of duties between database admins and network admins.

    {
        string crmServerName, CrmServerPort, CrmOrgName;
        string sSQL = "SELECT CrmServerName, CrmServerPort, CrmOrgName FROM crmInfo";

        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            SqlCommand command = new SqlCommand(sSQL, connection);
            SqlDataReader r = command.ExecuteReader();

            r.Read();
            crmServerName = Convert.ToString(r["CrmServerName"]);
            CrmServerPort = Convert.ToString(r["CrmServerPort"]);
            CrmOrgName = Convert.ToString(r["CrmOrgName"]);
        }

Now that we have queried our CRM server settings, we can establish a connection to CRM. Our CRM authentication is done via Active Directory. This is important to know when using CLR methods as the SQL Server service startup credentials will be passed to the CRM connection. Hence, the SQL Server service account must exist in the CRM users and be associated to a role that has access to create Products in CRM. Suffice to say, we will be expanding on the crmIntegration class later and the crmConnection() and crmInsertProduct() methods.

        //create an instance of the crm integration class
        crmIntegration crmInt = new crmIntegration();

        try
        {
            // Establish connection with CRM server
            crmInt.crmConnection(crmServerName, CrmServerPort, CrmOrgName);

            // Insert product
            crmInt.crmInsertProduct(
                    itemNumber.Trim()
                    , itemDescription.Trim()
                    , vendorName.Trim()
                    , vendorItem.Trim()
                    , itemShipWeight
                    , defaultUnitOfMeasure.Trim()
                    , defaultUnitOfMeasureSched.Trim()
                    , defaultPriceLevel.Trim()
                    , currencyID.Trim()
                    , decimalsSupported
                    , quantityOnHand
                    , unitPrice
                    , priceLevelPrice
                    , standardcost
                    , currentCost
                    , productTypeCode
            );
        }
        catch (System.Exception ex)
        {
            if (ex.InnerException != null)
            {
                SqlContext.Pipe.Send("Exception occurred: " + ex.InnerException.Message);

                SoapException se = ex.InnerException as SoapException;
                if (se != null)
                    SqlContext.Pipe.Send("Exception detail: " + se.Detail.InnerText);
            }
        }
        finally
        {
            //do something else here
        }
    }
}

Since one of the main concerns of the client was the ability to upgrade with each new release of CRM, we made use of the CRM web services provided by the Microsoft CRM 4.0 SDK. For this, we will add a new class library to our project and call it Crm.Integration.cs which will implement the Crm.Integration namespace and the CrmIntegration class. But first, we must create two web references: one for the CRM Service, contained under the CrmSdk namespace and one for the CRM Discovery Service, contained under the CrmDiscovery namespace.

Crm.Integration.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Text;
using System.Web.Services.Protocols;
using CrmDiscovery;
using CrmSdk;

Now that we have declared our namespaces, we can proceed to implement the crmIntegration class. The first method to be implemented will be the connection method. This method contains all the code needed to use the Discovery service to obtain the correct URL of the CrmService Web service for your organization. The code then sends a WhoAmI request to the service to verify that the user has been successfully authenticated. This method consists of three specific operations: a) instantiate and configure the CRMDiscovery Web service, b) Retrieve the organization name and endpoint Url from the CrmDiscovery Web service, and c) create and configure an instance of the CrmService Web service.

namespace Crm.Integration
{
    public class crmIntegration
    {
        CrmService crmService;

        // Establishes a connection to CRM
        public void crmConnection(string hostName, string hostPort, string orgName)
        {
            try
            {
                // STEP 1: Instantiate and configure the CrmDiscoveryService Web service.

                CrmDiscoveryService discoveryService = new CrmDiscoveryService();
                discoveryService.UseDefaultCredentials = true;
                discoveryService.Url = String.Format(
                    "http://{0}:{1}/MSCRMServices/2007/{2}/CrmDiscoveryService.asmx",
                    hostName, hostPort, "AD");

                // STEP 2: Retrieve the organization name and endpoint Url from the
                // CrmDiscoveryService Web service.
                RetrieveOrganizationsRequest orgRequest = new RetrieveOrganizationsRequest();
                RetrieveOrganizationsResponse orgResponse =
                    (RetrieveOrganizationsResponse)discoveryService.Execute(orgRequest);

                String orgUniqueName = String.Empty;
                OrganizationDetail orgInfo = null;

                foreach (OrganizationDetail orgDetail in orgResponse.OrganizationDetails)
                {
                    if (orgDetail.FriendlyName.Equals(orgName))
                    {
                        orgInfo = orgDetail;
                        orgUniqueName = orgInfo.OrganizationName;
                        break;
                    }
                }

                if (orgInfo == null)
                    throw new Exception("The organization name is invalid.");

                // STEP 3: Create and configure an instance of the CrmService Web service.

                CrmAuthenticationToken token = new CrmAuthenticationToken();
                token.AuthenticationType = 0;
                token.OrganizationName = orgUniqueName;

                crmService = new CrmService();
                crmService.Url = orgInfo.CrmServiceUrl;
                crmService.CrmAuthenticationTokenValue = token;
                crmService.Credentials = System.Net.CredentialCache.DefaultCredentials;

                // STEP 4: Invoke CrmService Web service methods.

                WhoAmIRequest whoRequest = new WhoAmIRequest();
                WhoAmIResponse whoResponse = (WhoAmIResponse)crmService.Execute(whoRequest);

            }
            // Handle any Web service exceptions that might be thrown.
            catch (SoapException ex)
            {
                throw new Exception("An error occurred while attempting to authenticate.", ex);
            }
        }

For more information on using the CRM Discovery service with Active Directory authentication, click here. Following the authentication process, we can now implement the method that will insert or update a product in the Product entity.

        // Insert product method
        public void crmInsertProduct(
            string productNumber,
            string productName,
            string productVendorName,
            string productVendorItem,
            decimal productWeight,
            string defaultUnitOfMeasure,
            string defaultUnitOfMeasureSched,
            string defaultPriceLevel,
            string currencyID,
            int decimalsSupported,
            decimal quantityOnHand,
            decimal listPrice,
            decimal priceLevelPrice,
            decimal standardCost,
            decimal currentCost,
            int productTypeCode)
        {
            try
            {
                string strProductId;
                product crmProduct = new product();

                bool found = crmGetProduct(productNumber, out strProductId);
                if (!found)
                {
                    // is a new product, create
                    crmProduct.productnumber = productNumber;
                    crmProduct.name = productName;

                    // quantity decimal places
                    crmProduct.quantitydecimal = new CrmNumber();
                    crmProduct.quantitydecimal.Value = decimalsSupported;

                    // quantity on hand
                    crmProduct.quantityonhand = new CrmDecimal();
                    crmProduct.quantityonhand.Value = quantityOnHand;

                    // unit price
                    crmProduct.price = new CrmMoney();
                    crmProduct.price.Value = listPrice;

                    // standard cost
                    crmProduct.standardcost = new CrmMoney();
                    crmProduct.standardcost.Value = standardCost;

                    // Current cost
                    crmProduct.currentcost = new CrmMoney();
                    crmProduct.currentcost.Value = currentCost;

                    // Vendor Name
                    crmProduct.vendorname = productVendorName;

                    // Vendor Item
                    crmProduct.vendorpartnumber = productVendorItem;

                    // Shipping Weight
                    crmProduct.stockweight = new CrmDecimal();
                    crmProduct.stockweight.Value = productWeight;

                    //------------------------------------------------//
                    // Product type code                              //
                    //------------------------------------------------//
                    crmProduct.producttypecode = new Picklist();
                    if (productTypeCode != 0)
                        crmProduct.producttypecode.Value = productTypeCode;
                    else
                        crmProduct.producttypecode.IsNull = true;

                    // retrieve guid's for the default unit of measure
                    string strUofM;
                    string strUofMSched;

                    bool isUofM = crmGetUofM(defaultUnitOfMeasure, out strUofM, out strUofMSched);
                    if (isUofM)
                    {
                        crmProduct.defaultuomid = new Lookup();
                        crmProduct.defaultuomid.Value = new Guid(strUofM);
                        crmProduct.defaultuomid.type = EntityName.uom.ToString();

                        crmProduct.defaultuomscheduleid = new Lookup();
                        crmProduct.defaultuomscheduleid.Value = new Guid(strUofMSched);
                        crmProduct.defaultuomscheduleid.type = EntityName.uomschedule.ToString();
                    }

                    // create the product
                    Guid productId = crmService.Create(crmProduct);

                    // create pricelist
                    crmInsertProductPricelist(productNumber, defaultUnitOfMeasure, defaultUnitOfMeasureSched, defaultPriceLevel, currencyID, 1, priceLevelPrice, 0);

                    // Create the column set object that indicates the fields to be retrieved.
                    ColumnSet columns = new ColumnSet();
                    columns.Attributes = new string[] { "productid", "pricelevelid" };

                    // Retrieve the product from Microsoft Dynamics CRM
                    // using the ID of the record that was retrieved.
                    // The EntityName indicates the EntityType of the object being retrieved.
                    product updatedProduct = (product)crmService.Retrieve(EntityName.product.ToString(), productId, columns);
                    updatedProduct.pricelevelid = new Lookup();

                    string guidPriceLevel;
                    bool isPricelevel = crmGetPriceLevel(defaultPriceLevel.ToUpper(), out guidPriceLevel);
                    if (isPricelevel)
                    {
                        updatedProduct.pricelevelid = new Lookup();
                        updatedProduct.pricelevelid.Value = new Guid(guidPriceLevel);
                        updatedProduct.pricelevelid.type = EntityName.pricelevel.ToString();

                    }

                    // update the record
                    crmService.Update(updatedProduct);
                }
                else
                {
                    // Create the column set object that indicates the fields to be retrieved.
                    ColumnSet columns = new ColumnSet();
                    columns.Attributes = new string[] { "productid", "name", "quantityonhand", "price", "standardcost", "currentcost", "defaultuomid", "defaultuomscheduleid" };

                    // Retrieve the product from Microsoft Dynamics CRM
                    // using the ID of the record that was retrieved.
                    // The EntityName indicates the EntityType of the object being retrieved.
                    Guid _productGuid = new Guid(strProductId);
                    product updatedProduct = (product)crmService.Retrieve(EntityName.product.ToString(), _productGuid, columns);

                    updatedProduct.name = productName;

                    // quantity decimal places
                    updatedProduct.quantitydecimal = new CrmNumber();
                    updatedProduct.quantitydecimal.Value = decimalsSupported;

                    // quantity on hand
                    updatedProduct.quantityonhand = new CrmDecimal();
                    updatedProduct.quantityonhand.Value = quantityOnHand;

                    // unit price
                    updatedProduct.price = new CrmMoney();
                    updatedProduct.price.Value = listPrice;

                    // standard cost
                    updatedProduct.standardcost = new CrmMoney();
                    updatedProduct.standardcost.Value = standardCost;

                    // Current cost
                    updatedProduct.currentcost = new CrmMoney();
                    updatedProduct.currentcost.Value = currentCost;

                    // Vendor Name
                    updatedProduct.vendorname = productVendorName;

                    // Vendor Item
                    updatedProduct.vendorpartnumber = productVendorItem;

                    // Shipping Weight
                    updatedProduct.stockweight = new CrmDecimal();
                    updatedProduct.stockweight.Value = productWeight;

                    //------------------------------------------------//
                    // Product type code                              //
                    //------------------------------------------------//
                    updatedProduct.producttypecode = new Picklist();
                    if (productTypeCode != 0)
                        updatedProduct.producttypecode.Value = productTypeCode;
                    else
                        updatedProduct.producttypecode.IsNull = true;

                    // retrieve guid's for the default unit of measure
                    string strUofM;
                    string strUofMSched;

                    bool isUofM = crmGetUofM(defaultUnitOfMeasure, out strUofM, out strUofMSched);
                    if (isUofM)
                    {
                        updatedProduct.defaultuomid = new Lookup();
                        updatedProduct.defaultuomid.Value = new Guid(strUofM);
                        updatedProduct.defaultuomid.type = EntityName.uom.ToString();

                        updatedProduct.defaultuomscheduleid = new Lookup();
                        updatedProduct.defaultuomscheduleid.Value = new Guid(strUofMSched);
                        updatedProduct.defaultuomscheduleid.type = EntityName.uomschedule.ToString();
                    }

                    string guidPriceLevel;
                    bool isPricelevel = crmGetPriceLevel(defaultPriceLevel.ToUpper(), out guidPriceLevel);
                    if (isPricelevel)
                    {
                        updatedProduct.pricelevelid = new Lookup();
                        updatedProduct.pricelevelid.Value = new Guid(guidPriceLevel);
                        updatedProduct.pricelevelid.type = EntityName.pricelevel.ToString();

                    }

                    // create pricelist
                    crmInsertProductPricelist(productNumber, defaultUnitOfMeasure, defaultUnitOfMeasureSched, defaultPriceLevel, currencyID, 1, priceLevelPrice, 0);

                    // update the record
                    crmService.Update(updatedProduct);
                }
            }
            catch (SoapException ex)
            {
                throw new Exception("An error occurred while attempting to insert a record in the CRM product entity.", ex);
            }
        }

In order to establish whether a product should be inserted or updated in the Product entity, you must first lookup the product. That’s accomplished by invoking the crmGetProduct() method (to be implemented below). If the product is not found in the catalog, we can proceed to setup all the attributes to be inserted, then call the crmService.Create() method.

If the product is found, then we can just retrieve all the columns that will be subsequently updated, then invoke the crmService.Update() method to commit the changes.

Finally, the crmGetProduct() method is shown below:

        public bool crmGetProduct(string productNumber, out string pId)
        {
            pId = null;

            ConditionExpression condition1 = new ConditionExpression();
            condition1.AttributeName = "productnumber";
            condition1.Operator = ConditionOperator.Equal;
            condition1.Values = new string[] { productNumber };

            FilterExpression filter = new FilterExpression();
            filter.FilterOperator = LogicalOperator.And;
            filter.Conditions = new ConditionExpression[] { condition1 };

            ColumnSet resultSetColumns = new ColumnSet();
            resultSetColumns.Attributes = new string[] { "productid", "productnumber" };

            // Put everything together in an expression.
            QueryExpression qryExpression = new QueryExpression();
            qryExpression.ColumnSet = resultSetColumns;

            // set a filter to the query
            qryExpression.Criteria = filter;

            // Set the table to query.
            qryExpression.EntityName = EntityName.product.ToString();

            // Return distinct records.
            qryExpression.Distinct = true;

            // Execute the query.
            BusinessEntityCollection productResultSet = crmService.RetrieveMultiple(qryExpression);

            // Validate that an expected contact was returned.
            if (productResultSet.BusinessEntities.Length == 0)
                return false;
            else
            {
                bool productFound = false;
                foreach (product aProduct in productResultSet.BusinessEntities)
                {
                    if (aProduct.productnumber.ToUpper().Trim().Equals(productNumber.ToUpper()))
                    {
                        productFound = true;
                        pId = aProduct.productid.Value.ToString();
                        break;
                    }
                }

                return productFound;
            }
        }
    }
}

The beauty about Microsoft Dynamics CRM platform services is that it provides a number of methods and implementations that facilitate querying any piece of data stored in the platform. The above method shows the use of the ConditionExpression, FilterExpression and QueryExpression classes, that when combined together, form the basis of the query platform. Finally we can create a collection with the filtered Product entity and navigate to see if the product was found.

This completes the first part of our implementation, but here are some final notes and things that I discovered throughout the project:

1. Assemblies that will be registered against SQL Server require signing. You must create a strong name key file that will be used to sign your assembly. To do this, go to the project Properties and select the Signing tab.

2. You cannot simply register an assembly that references a Web service against SQL Server without creating an XML serialization assembly. Serialization assemblies improve the startup performance of the Web service calls. To do this, go the project Properties and select the Build tab. Select On from the Generate Serialization Assembly drop down list.

Keep in mind that the above code is only provided as a sample and that other implementations are required to deal with Unit of Measures and Price Schedules. The bottom line is, the crmGetProduct() method provides the basis for the implementation of the other methods not shown.

Friday, I will show you how to register the assemblies on Microsoft SQL Server and how to implement some basic triggers that will exploit the CLR stored procedures.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


Using SQL CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM

August 16, 2010

I have been involved for over the past 6 months with an extensive project requiring complex integrations between Microsoft Dynamics GP 10.0, Microsoft CRM 4.0 and other custom operational systems. In the process of designing and implementing these integrations the client requested a very easy to use interface that could be maintained without having to hire an army of developers or even specialized resources.

The mission: insert/update customer addresses and inventory items from Microsoft Dynamics GP into Microsoft CRM’s Product and Customer Address entities. The client also requested the integration be done using the Microsoft CRM web services in order to ensure upgrade support.

Background

Beginning with SQL Server 2005, the components required to develop basic CLR database objects are installed with SQL Server. CLR integration functionality is exposed in an assembly called system.data.dll, which is part of the .NET Framework. This assembly can be found in the Global Assembly Cache (GAC) as well as in the .NET Framework directory. A reference to this assembly is typically added automatically by both command line tools and Microsoft Visual Studio, so there is no need to add it manually.

The system.data.dll assembly contains the following namespaces, which are required for compiling CLR database objects:

System.Data
System.Data.Sql
Microsoft.SqlServer.Server
System.Data.SqlTypes

You can find more information on SQL Server CLR integration over at MSDN. Be sure to check the following articles:

Overview of CLR Integration
CLR Stored Procedures

Solution

The solution can be broken down into two parts:

1. Creating the assembly with the CLR stored procedures that would in turn instantiate the CRM web methods to open a connection and insert or update the Product and Customer Address entity records.

2. Configuring Microsoft SQL Server and registering the assembly, creating the triggers on the RM Customer Address Master (RM00102) and Item Master (IV00101) tables that would invoke the CLR stored procedures to pass the Microsoft Dynamics GP records.

This week’s series will outline the solution with the code to achieve this. The following topics will become available on the day of their release:

08/18/2010 – Creating a CLR assembly and working with CRM web methods

08/20/2010 – Configuring SQL Server and creating table triggers

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


CRM – How to match product information between CRM and GP

May 21, 2010

I have had the fortune to work on a very challenging Microsoft Dynamics CRM and Microsoft Dynamics GP multi-system integration for the past 3 months, requiring lots of custom code to manage above and beyond the common interfaces that can be had with some of the tools available in today’s market. Of course, one of the main challenges has been for the folks building the reports. So today I start a series of quick and easy queries showing how to obtain data from both Microsoft Dynamics CRM and GP, that hopefully will help you build some quick reports, but also begin to find similarities between the two products. Today features the Product catalog from CRM and the Item Master from GP.


select a.ProductNumber, a.ProductId, a.Name, b.ITEMTYPE, b.STNDCOST, b.CURRCOST, b.ITMCLSCD
from ProductBase a
inner join IV00101 b on (a.ProductNumber = b.ITEMNMBR)

If you have custom fields in CRM for your Product, you can use the following query to tie these in.


select a.ProductNumber, a.ProductId, a.Name, c.ITEMTYPE, c.STNDCOST, c.CURRCOST, c.ITMCLSCD
from ProductBase a
inner join ProductExtensionBase b on (a.ProductId = b.ProductId)
inner join IV00101 c on (a.ProductNumber = c.ITEMNMBR)

Note that in the above query, I have not included any columns from the ProductExtensionBase table. The reason? It’s custom! You can include any name for the custom columns you have created here.

If you are one of those who are comfortable using the CRM views, then this query will provide a one-stop shop to obtain all the information from both the ProductBase and the ProductExtensionBase tables.


select a.ProductNumber, a.ProductId, a.Name, b.ITEMTYPE, b.STNDCOST, b.CURRCOST, b.ITMCLSCD
from Product a
inner join IV00101 b on (a.ProductNumber = b.ITEMNMBR)

Note: the above query assumes you have a linked server connection between your Microsoft Dynamics GP and CRM servers and that you have created synonyms for the objects referenced or that you have at least arranged for these tables to be replicated to a single location.

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


New article on MSDynamicsWorld: "Integrating Microsoft Dynamics CRM with Microsoft Dynamics GP"

January 7, 2010

“No, this is not the Chris Tucker and Jackie Chan movie…”

One of my goals for this year is to become technically and functionally skilled in Microsoft Dynamics GP CRM. However, I never expected this to happen so quickly and abruptly as it did some 3 weeks ago with a “weekender-all nighter-gotta-get-it-done-by-Monday” project I worked on with my business partner. I collected some of the best practices and experiences from that project and put them together in my new MSDynamicsWorld article.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/