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.
If you have custom fields in CRM for your Product, you can use the following query to tie these in.
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.
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/
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/
Comments
Select ... From ProductBase as A inner join GPDB.IV00101 as B ...
...
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