Microsoft Dynamics GP (Archived)

Vendor Master Records - Copy 4 companies to New Company

(0) Share

Report

Posted on by Napolo

766

We currently have 4 GP companies. We are merging into one company. At this point, moving all master records over.

So far I have reviewed the different sets of class id's and terms. Cleaned up the New systems to match.

What would be the best method to isolate all active vendors that have a last check date in last two years, from 4 different companies into one.

I have a query of what I want for each company, however I do not want to bring over duplicate records. Some companies may have the same vendor id.

I am wanting to copy over the following information for unique vendor id's across all 4 companies.

Vendor Record
Vendor Address Records
Vendor Notes
Vendor Attachments

Any guidance is greatly appreciated!

*This post is locked for comments

I have the same question (0)

All responses (12)

Answers (1)

Suggested answer

Tim Wappat 5,711 on at

Like (1)

Report

There are many ways to do this and everyone has methods that they are more familiar with than others, such as integration manager and GP macros and direct SQL.

For me, I would write a SQL query for each company, adding a static column for the company id.

I would then UNION ALL the four company queries together.

I would then use a ROW_NUMBER() windowed function on the results of the union, groupby VENDORID and sorted by the company id to create a priority column.

I would then select out all the rows with priority =1 so we only get one instance of each vendor, and the vendor we get is of the highest priority out of your companies (as you have to decide which company will provide the data where multiple companies have that data).

I would use that data to drive a mail merge GP macro to create the vendors.

I would then use SQL to squirt all the notes and attachments from the four companies into the new vendors of the new company.

If you have these skill sets then you should be ok, otherwise I would recommend talking to your GP support partner if you are an end user about this objective and seeing what they can do for you.

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

Nicole -

I've participated in projects which used two very different approaches for the same purpose; combining companies to take advantage of the time saving features of Binary Stream's Multi-Entity Management. First, querying numerous (60+) databases and using these queries to populate a new single entity. Second, using Corporate Renaissance Group's Company Combiner product. Both approaches require testing and validation. Querying and combining companies requires more consulting hours but does not require a software investment. CRG's Company Combiner cuts consulting hours, reduces risk but requires a software investment.

Your vendor problem is familiar territory, and proved difficult to de-duplicate. Have you considered merging all records and then using Vendor Modifier/Combiner to merge the records after the compilation?

Was this reply helpful? Yes No
Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (1)

Report

Hi Nicole,

Both Tim & Harry's approach are valid options.. As you already assessed the different vendor classes and terms, one way you could use, and this is if you do not have hundreds of active vendors, is to install the PSTL and use the Master Trigger to copy over essentially all the information for each Vendor card you want replicate.

It only takes to hit the card and save it.. the master trigger will replicate the information into the other company.

Use a smartlist or Excel report to figure out which Vendords are worth bringing over.

This approch requires zero investment in SW, and could mostly be carried on by yourself without your partner's assistance (aside of eventually installing the PSTL).

Was this reply helpful? Yes No
Napolo 766 on at

Like (0)

Report

In total I have 3102 Vendors to bring over. I am still green with SQL, but I have been successful with isolating the vendors and updating their status to inactive, so that I have a valid list to work with. Below is the SQL code that I have so far. I think I am doing the UNION Correction, but I am getting Union and Use errors.

USE FLORI

SELECT 'Florida' AS Co, *

FROM dbo.PM00200

WHERE VENDSTTS = 1

UNION ALL

USE FEDER

SELECT 'Federation' AS Co, *

FROM dbo.PM00200

WHERE VENDSTTS = 1;

UNION ALL

USE PREPH

SELECT 'Hudelson' AS Co, *

FROM dbo.PM00200

WHERE VENDSTTS = 1

UNION ALL

USE PREPN

SELECT 'Northern' AS Co, *

FROM dbo.PM00200

WHERE VENDSTTS = 1;

Was this reply helpful? Yes No
Napolo 766 on at

Like (0)

Report

HI Harry Lee,

Yes, we met with CRG a few times. We have decided to make a clean break to a new company without bringing over history. Just master records.

I thought about Merging everything and then evaluating duplicates, but I was thinking the duplicate vendor ID would flag errors, if there was a vendor already in the new co?

Was this reply helpful? Yes No
Verified answer

Tim Wappat 5,711 on at

Like (0)

Report

USE sets the database context that the following SQL will run under, you can't change context half way though like that.

Instead table names come in four parts,

server.database.schema.tablename

for shorthand we can leave off everything but the table name, but when querying over multiple databases we have to specify the database. In GP schema is almost always dbo, although this the default schema and default schemas can be omitted so you may sometimes see double dots with no schema given.

FLORI.dbo.PM00200 and FLORI..PM00200 are both ok.

Also your semi colon end a SQL statement so that semicolon on your FEDER needs to come out or it will end the statement early, then SQL will wonder why you are starting the next statement with UNION ALL.

USE FLORI

SELECT 'Florida' AS Co, *

FROM FLORI.dbo.PM00200

WHERE VENDSTTS = 1

UNION ALL

SELECT 'Federation' AS Co, *

FROM FEDER.dbo.PM00200

WHERE VENDSTTS = 1;

UNION ALL

SELECT 'Hudelson' AS Co, *

FROM PREPH.dbo.PM00200

WHERE VENDSTTS = 1

UNION ALL

SELECT 'Northern' AS Co, *

FROM PREPN.dbo.PM00200

WHERE VENDSTTS = 1;

I've not ran this against anything, but I guess it will be ok, assuming all your database have the same number of columns in the PM00200, something funky would be going on if they didn't, like one company is not on same version of GP as others, for example...

Have fun - SQL is addictive.

Tim.

P.S.

If you want to prioritise which company should provide the record to copy (say one company is more likely to have accurate info than another), then you can set a priority against each one then select only the first occurrence of a Vendor ID. So for fun this script will only list each vendor id once, and only the one with the highest priority donor company.

Don't be put off, go Google each bit of the SQL and you'll learn some helpful things!

USE FLORI

WITH CTE_Union AS(
SELECT 1 as [priority], 'Florida' AS Co, *
FROM FLORI.dbo.PM00200 WHERE VENDSTTS = 1

UNION ALL

SELECT 2 as [priority],'Federation' AS Co, *
FROM FEDER.dbo.PM00200 WHERE VENDSTTS = 1

UNION ALL

SELECT 3 as [priority],'Hudelson' AS Co, *

FROM PREPH.dbo.PM00200 WHERE VENDSTTS = 1

UNION ALL

SELECT 4 as [priority],'Northern' AS Co, *

FROM PREPN.dbo.PM00200 WHERE VENDSTTS = 1),

CTE_Distinct AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY VENDORID ORDER BY [priority] ASC) as RowIDPriority, * FROM CTE_Union

)

SELECT * FROM CTE_Distinct WHERE RowIDPriority=1

ORDER BY VENDORID;

Was this reply helpful? Yes No
Napolo 766 on at

Like (0)

Report

Thank you so much Tim!! Yes, i am getting addicted..

Here is what I have so far. I have corrected the dbo as you suggested. I have added the Row Statement, that could find. The row output is now in a column counting each vendor for each company. My row count is what I expect it to be, so that is good.

From here I am not quite sure how to return unique Vendor ID's for all dbo. I have searched the priority select piece, but I am blank.

SELECT ROW_NUMBER() OVER (ORDER BY vendorid) AS Row_Number,'Florida' AS Co, *

FROM FLORI.dbo.PM00200

WHERE VENDSTTS = 1

UNION ALL

SELECT ROW_NUMBER() OVER (ORDER BY vendorid) AS Row_Number,'Federation' AS Co, *

FROM FEDER.dbo.PM00200

WHERE VENDSTTS = 1

UNION ALL

SELECT ROW_NUMBER() OVER (ORDER BY vendorid) AS Row_Number,'Hudelson' AS Co, *

FROM PREPH.dbo.PM00200

WHERE VENDSTTS = 1

UNION ALL

SELECT ROW_NUMBER() OVER (ORDER BY vendorid) AS Row_Number,'Northern' AS Co, *

FROM PREPN.dbo.PM00200

WHERE VENDSTTS = 1;

Was this reply helpful? Yes No
Napolo 766 on at

Like (0)

Report

Oh wait.. i didn't ready down to your PS!!! ahahah sorry

Was this reply helpful? Yes No
Napolo 766 on at

Like (0)

Report

Thanks Again Tim!

I got it and compared the row total to the number of unique rows i pulled using Excel, and it worked!

WITH CTE_Union AS(

SELECT 4 as [Priority],'Florida' AS Co, *

FROM FLORI.dbo.PM00200

WHERE VENDSTTS = 1

UNION ALL

SELECT 1 as [Priority],'Federation' AS Co, *

FROM FEDER.dbo.PM00200

WHERE VENDSTTS = 1

UNION ALL

SELECT 3 as [Priority],'Hudelson' AS Co, *

FROM PREPH.dbo.PM00200

WHERE VENDSTTS = 1

UNION ALL

SELECT 2 as [Priority],'Northern' AS Co, *

FROM PREPN.dbo.PM00200

WHERE VENDSTTS = 1),

CTE_DISTINCT AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY Vendorid Order by [priority] asc) as RowIDPriority, * FROM CTE_UNION

)

SELECT * FROM CTE_DISTINCT WHERE RowIDPriority=1

ORDER BY Vendorid;

Was this reply helpful? Yes No
Tim Wappat 5,711 on at

Like (0)

Report

Glad to hear it - have fun!

Was this reply helpful? Yes No