Same Customer/Vendor Numbers across all companies

(0) Share

Report

Posted on by John Olley

365

Hello,

We are looking for an easy way to try to sync up all of our customer/vendor numbers across our 12 company databases. We'd like to make the numbers the same so we could run one big multi-database report rolling up our customers/vendors from all databases so that we get one big total. I know I can use PSTL to modify them to get to the same number but is there an easier way to do this instead of one at a time? On the other side, I wasn't sure if there were any 3rd party apps that would 'link' customers together for these types of reports instead of modifying with PSTL. Any suggestions would be much appreciated! If PSTL is just the best way to go, then so be it!

Thanks,

John Olley

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (2)

Verified answer

Olaf Laos 1,890 on at

Like (0)

Report

With PSTL you can normally import values that you can prepare in a spreadsheet first - and then have PSTL execute the lot.

Was this reply helpful? Yes No
Frank Hamelly | MVP... 46,625 Super User 2025 Season 2 on at

Like (0)

Report

Binary Stream has an app called Multi Entity Management that allows for shared master records - COA, Customers, Vendors, Items, etc. The drawback is that all company databases have to be consolidated to 'master' one, which can be a significant undertaking. We've done this at a number of companies though and once the implementation is complete, it works pretty slick.

Was this reply helpful? Yes No
Verified answer

Victoria Yudin 22,769 on at

Like (0)

Report

If the only need for this is reporting, I would recommend two options:

1. Use PSTL to rename the existing customers. For the future, you can set up a 'master trigger' using PSTL to automatically create customers in all of your companies when one is created in the 'master' company.

2. Create a custom table in SQL (probably in the Dynamics DB) that holds a 'mapping' of the customer IDs across your companies. This would need to be updated manually, so might be a hassle to maintain depending on how often you add customers, but would allow you to not have to change any customer IDs in GP if you want to keep them the way they are currently. When creating reports, this custom table can be used to link the related customers.

Was this reply helpful? Yes No
John Olley 365 on at

Like (0)

Report

Thanks so much for the help everyone. Those are all really great ideas that I can bring to my team to see what works best for us. I really appreciate it!

John Olley

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

John,

We are considering the same issue, multiple companies but would like one place to manage vendors. We are considering Binary Stream's Multi-Entity Management tool. Just wondering if you moved forward with one of the suggested solutions.

Thanks.

Vicki

Was this reply helpful? Yes No
John Olley 365 on at

Like (0)

Report

Vicki,

We did use Binary Stream and it just wasn't the right fit for us. I'm sure it would be great for the majority of companies but we had a few issues with it on our end.

Using the PSTL to rename vendors/customers would work in all of our databases, except one database that utilizes WennSoft. WS has it's own tables of customer/vendor references and those would need to be updated or we would be getting a lot of errors (PSTL won't update those). Of course, we could get WS involved and have them do custom work for us if needed, but it would just cost more money. If you have enough money, you can make anything happen, right? Haha :)

The recommendations above with PSTL seem to be about the best I had on this. We never did go through and make our customers/vendor IDs the same in our databases. After looking into it at the time, it just wasn't worth the effort and our resources needed to be in other projects.

You can do this with PSTL for all your customer/vendors at one time (per company database) if you wanted to. You would just need to prepare for it before you did this. You could use a Smartlist (or SQL) to export the master lists company by company. Then you would make the changes you wanted in Excel, save has a .txt file (that's how I've done it with account modifier), and then just import them all at once with PSTL in each company. That process wouldn't take too long, but this is of course depending on how much historical data you have.

After all databases matched your vendor IDs, you could then create some custom SQL code that says "When I put a vendor ID in database X, copy this to XXX databases." Of course if you don't have anyone internally that knows how to do this, you can get your partner to help, Microsoft to help, or some kind of Dynamics GP/SQL consultant involved. There will be a cost!

You do have to be careful of custom codes like that though. Dynamics GP could easily change table names, add or remove columns, add tables, ect to the product in a new version and the 'old code' wouldn't work anymore. It could cause more problems than what it was originally doing to help if that happens so you need to be safe there as well if you go that route before doing an upgrade. Always test, test, test custom work! : )

Good luck!

John Olley

Was this reply helpful? Yes No