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:
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 ...
...
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
Post a Comment