Vendor ID

(0) Share

Report

Posted on by Community Member

I have set up a creditor with an incorrect ID.

There has been 1 invocie and payment entered against it so I can not delete and restart.

Does anybody know if there are many tables to alter to rectify this? In a test environment I have changed the PM00200 table and entered the correct ID but I am beginning to think I will need to repeat this in lots of other tables...

I know I could create a new creditor and mark the old one as on hold but I would prefer to try and rectify the situation (if it means changing only a few tables).

Calum

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (2)

Ron Wilson 6,010 on at

Like (0)

Report

I am sure you could change the tables, but I am not 100% sure all the tables that would need to be changed. I would not recommend this.

CRG has a product called Changer that will allow you to do this.

www.crgroup.com/.../crg-changer

If it were me, I would change the vendor id and make a note on the old vendor id to use the new one. Just my $.02.

Ron

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

You are right. There are number of tables containing vendor data. It is not recommended to change vendor ID directly in tables.

There is 3rd party tool CRF Changer which can help you making this change.

Since, there is just one vendor with couple of transactions you want to clear. Steps you can use to change vendor are :

1. Void the invoice and check

2. Use the Remove Payables Transaction History window (Microsoft Dynamics GP menu >> Tools >> Utilities >> Purchasing >> Remove Transaction History) to clear the transactions for the vendor.

3. Similarly, remove the Distribution, period and posting journal history using other remove windows under menu option Microsoft Dynamics GP menu >> Tools >> Utilities >> Purchasing >>

4. After clearing the vendor history, you will be allowed to delete the vendor.

5. Add the vendor with correct ID

6. Re-enter the invoice and check

Make sure you have database backup before making these changes. If possible, the practice the steps in test company before making changes. You can print report before removing data to make sure you do not accidently remove data for other vendors.

These steps are not recommended for the vendor with more than couple transactions.

Was this reply helpful? Yes No
Verified answer

Victoria Yudin 22,769 on at

Like (0)

Report

Calum,

Changing this in the tables is not recommended because it is very easy to break something and thus compromise the integrity of your data.

There is a tool sold by Microsoft as part of the Professional Services Tools Library that will do this for you, but it is not free.

The only other way to do this correctly is to find EVERY table where the vendor ID appears and change it. I can think of at least 20 tables off the top of my head that you would need to at least check. The difficultly in this is that the column name might be different depending on the table. For example when you post subledger transactions to the GL, there is a column for Originating Master ID that will store the vendor ID for the originating subledger transactions. This is not something that I would recommend unless you are 100% confident and comfortable with your knowledge of GP tables and data.

Was this reply helpful? Yes No
Suggested answer

Richard Whaley 25,195 on at

Like (0)

Report

It would be easier to void the payment, void the invoice, put the vendor on hold, and create a new one. There are several tables for the vendor plus the Open and apply tables in PM. Add to that the financial transactions that refer to the vendor number......

Back out the transactions!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Calum.

In Resource Descriptions under Field Descriptions you can select the Vendor Id field and it will list all tables that contain the field.

You can then script these tables to replace the Vendor ID.

In the GL20000 table that contains the entries created by the posted invoice you will need to update the originating master id field with the new vendor id.

This should do the trick.

There is also the Changer tool that Microsoft supplies that allows to change master id values.

Hope this helps

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Calum,

I really like Rubal suggesstion of deleting the vendor and starting over. Very clean. This is really just a tip from me as an FYI. If you change the status of a vendor (creditor) to 'Temporary', you can delete it even if it has transaction history. It will not remove the transaction history, but it will take the vendor out of the vendor master. Be sure not to re-use the ID, if you do it will link up to the old transactions.

Kind regards,

Leslie

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Guys

Thank you for all the replies. That really is very helpful.

Rubal I have implemented your suggestion in a test environment and it worked fine so am going to recreate in live. Thanks again.

Kind regards

Calum

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I am glad to know that it helped.

Rubal

Was this reply helpful? Yes No
Mary L. 45 on at

Like (0)

Report

Did your vendor (creditor) have EFT information? We did a mass delete on the vendor table and also purged history, but the customer re-used the vendor ID and the EFT information was still there. Is there a utility to get rid of that?

They are on GP 10. Thanks,

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Mary,

Thank you for your post. We have a routine that removes very old vendors that we have not used in years. We did not link the vendors to their EFT data and we could have run into the same problem you are seeing. After your post we are now checking for old EFT data on old vendors. I do not know of any utility that will do this. The table SY06000 holds vendor EFT data. We are now clearing SY06000 as part of removing old vendors.

Thanks for pointing out a potential problem for us.

Keith McConnell

Was this reply helpful? Yes No