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/

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 ...

...
May 21, 2010 at 7:39 PM
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
May 21, 2010 at 11:09 PM
Post a Comment

Popular posts from this blog

Why my transactions don't post through GL even when I have the flag selected?

Image
Are you tired of asking why is it that some transactions do post through GL and why some don't? Tired of changing the posting settings to no avail? This issue outta be one of the most confusing issues for users and consultants, new or seasoned alike. If you ever struggle with one issue in GP, please tell me it's this one, because even today, I cannot claim victory over this subject. So let's walk the walk together. What the theory says? According to Part 3, Chapter 13, page 87 of the Microsoft Dynamics GP's System Setup guide, " If you mark the Post Through General Ledger Files option, batches posted in other modules automatically will update the module in which the transactions originated and the appropriate posting accounts in General Ledger. Your transactions will post to General Ledger and not through General Ledger when posting transaction individually (without a batch). " Now lets break it down... It's important to really pay close attention to the t...
Read more

Power Automate | Passing Collections from Power Automate to Power Apps (Revisited)

Image
I revisit an old video I did couple years ago showing how to pass collections from Power Automate to Power Apps.  This time around, I show how to leverage arrays to accomplish the same. This time around you will learn about: Power Apps trigger in Power Automate   Get Rows SQL action   Variables action Select action and Arrays Response action Power Apps Collections To check out my YouTube channel visit: https://youtube.com/user/mgomezb1 LIKE AND SUBSCRIBE!!! Until next post! MG.- Mariano Gomez, MVP
Read more

A .NET Framework error occurred during the execution of user-defined routine or aggregate "GetAssignedUsers"

Image
So, I found myself helping a client to troubleshoot a workflow issue. After configuring workflow a few weeks aback and running a few tests without any issues, we applied Microsoft Dynamics GP 2015 R2 January 2016 Hotfix. Now, when the client went to execute the workflow, he received the corresponding email indicating there was a purchase order to approve. He then proceeded to click the Approve hyperlink and the process continued as expected. After clicking the Approve button, the client receive the following error message: The server encountered an error processing the request. See server logs for more details The following shows the error as displayed on the client's screen. At this point, it is worth stating that the workflow ran to completion and the PO appeared completely approved and fine in Dynamics GP, so whatever was causing this error did not seem to interrupt the approval flow. As good troubleshooters, we went to the Event Viewer to check for addi...
Read more