Microsoft Dynamics GP (Archived)

Vendor Master Records - Copy 4 companies to New Company

(0) Share

Report

Posted on by Napolo

750

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

All responses (12)

Answers (1)

TimDotNet 95 on at

Like (0)

Report

RE: Vendor Master Records - Copy 4 companies to New Company

Yes notes and attachments get more fun.

I don't know what you have in mind to tackle this?

You could bring over notes from the source companies, and insert them all into the single company, adding separators in the text to make it clear where each note comes from.

e.g.

====Notes transferred from company x start ============

blah blah blah

====End of notes transferred from company x============

====Start of notes transferred from company y ===========

blah blah

====End of notes transferred from company y============

You need to use a MERGE SQL statement to create or add the records to the destination notes table from each of the source databases.

Then you could move the attachments from the source company databases and put them into the destination database against the destination note id.

There is an excellent blog post about the tables and doc attach here that should help:
https://community.dynamics.com/gp/b/dynamicsgp/archive/2016/05/02/document-attach-vs-notes-and-how-to-locate-whether-a-master-record-has-a-document-attached

This is a little bit more involved, so if you need any assistance let me know outside of the forum.

Tim.
Napolo 750 on at

Like (0)

Report

RE: Vendor Master Records - Copy 4 companies to New Company

Hi Tim,

Do you have a reference for the note/Attachments?

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

I have pulled each of the below tables to see the activity taking place. I think I can copy over the SY03900 Text Note Index. That appears to be pretty clear.

It is the Attachments that seem to appear more complex. Unless I am making it harder than it is?

SELECT *

from coAttachmentItems

SELECT *

FROM CO00101

select *

from co00102

select *

from CO00103

select*

from CO00104

select *

from CO00105

select *

from CO40100

select *

from SY03900
Tim Wappat 5,701 on at

Like (0)

Report

RE: Vendor Master Records - Copy 4 companies to New Company

Glad to hear it - have fun!
Napolo 750 on at

Like (0)

Report

RE: Vendor Master Records - Copy 4 companies to New Company

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;
Napolo 750 on at

Like (0)

Report

RE: Vendor Master Records - Copy 4 companies to New Company

Oh wait.. i didn't ready down to your PS!!! ahahah sorry
Napolo 750 on at

Like (0)

Report

RE: Vendor Master Records - Copy 4 companies to New Company

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;
Verified answer

Tim Wappat 5,701 on at

Like (0)

Report

RE: Vendor Master Records - Copy 4 companies to New Company

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;
Napolo 750 on at

Like (0)

Report

RE: Vendor Master Records - Copy 4 companies to New Company

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?
Napolo 750 on at

Like (0)

Report

RE: Vendor Master Records - Copy 4 companies to New Company

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;
Suggested answer

Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at

Like (1)

Report

RE: Vendor Master Records - Copy 4 companies to New Company

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