Friday, May 21, 2010

CRM - How to match product information between CRM and GP

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/

2 comments:

Rob Jolliffe said...

Does this mean that the tables are both in the same SQL Database? I would have expected you to prefix one of the tables (either the IV00101 or the ProductBase) with a database name as in

Select ... From ProductBase as A inner join GPDB.IV00101 as B ...

...

Mariano Gomez said...

Rob,

Hope you are doing well and thanks for the post. No, the tables do not need to be in the same database. As I mentioned in the note of the post, you will at least need a linked server connection to your CRM server from your GP server with synonyms in place for the CRM database objects you need to access.

With synonyms in place, you can run these queries from a GP company database.

The other way around works too. If you need to run the queries from a CRM database context, then you can create a linked server connection to the GP server and use synonyms for the IV00101 table.

Hopefully this clarifies.

MG.-
Mariano Gomez, MVP