Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Matthew Holmstoen]

Hi all,

We have about 2500 vendors that we have not paid in the past 18 months and would like to make them inactive.  Would anyone happen to know a quick and easy way to accomplish this?  Also, going forward, is there a way to have the system automatically inactivate a vendor that has not been paid in 18 months?  Thanks!

——————————
Matthew Holmstoen
Medica
Minnetonka MN
——————————

[Bert Green]

I do not know how to do it automatically, but if you have integration manager it is not to hard to setup. As you really only need the vendor id and then map the activity to inactive.

——————————
Bert Green
GP Developer/GP Administrator
Berger Transfer & Storage, Inc.
Saint Paul MN
GP2015/MR CU11
——————————
——————————————-

[Windi Epperson]

I was really hoping this was an option under the Navigation Lists, but since it’s not I created a suggestion on Connect.  Go vote for this and maybe we’ll get it in a future version. 🙂
Thanks
Windi

https://connect.microsoft.com/dynamicssuggestions/feedbackdetail/view/3127859/mass-inactivate-master-records-in-navigation-lists

——————————
Windi Epperson
President/GP Senior Consultant
Advanced Integrators, Inc.
Norman OK
405-946-1774 Ext 102
——————————
——————————————-

[Tiffany Ziegler]

I did a similar cleanup a few years ago. I actually took it one step further and deleted history on about 3000 vendors that only had one entry (we use our company credit card A LOT and my predecessor was posting transactions to individual vendors instead of directly to a credit card vendor). Prior to deleting anything, I created an exact copy of the current database and renamed it something like Velcor 2014 Vendor Cleanup or something. That way if need be, we can log into that company and see the vendor history. On the working company, I deleted transaction history for anyone we hadn’t paid in like 2 years, then deleted the vendor completely. It’s been a while, but I believe I created a SmartList to determine who needed to be deleted, exported to Excel and then used Macros to 1.) delete the transaction history and 2.) delete the Vendor ID itself.

More simplified, you could also use a Macro to just change the Vendor status from Active to Inactive. Record the macro on one vendor. Then use an Excel list and mail merge to change your remaining vendors to inactive. 

——————————
Tiffany Ziegler
Vice President & Controller
Velcor Leasing Corporation
Madison WI
——————————
——————————————-

[Joni Finnell]

Matthew:

We use Integration Manager as Bert mentioned. I pull a list of vendors from SmartList to create my list of vendor IDs then integrate them with this setup above. It wouldn’t let me paste it below my comments. 😎

I’m not aware of an automated way either. Probably a SQL script for that too. Maybe one of our SQL pros can answer. 8->

Thanks

——————————
Joni Finnell
Project Accountant/GP Troubleshooter
Consumer Support Services, Inc.
Newark OH
——————————
——————————————-

[Diana Doane]

This does not help with automated vendor inactivation……  But thought I’d share a tip we’ve used for vendor clean up in general. 

Because the mass delete for vendors is dependent on things which may not be efficient, we used the vendor combiner utility to move them all to a ‘dummy’ vendor ID, then deleted that one vendor.  You still have to have a list to import into the vendor combiner but you don’t have to spend time setting up/running a macro.

Using SQL to inactivate a vendor is an option.  But you would end up having to do a mail merge with your script to make sure you target the correct vendors.  May as well just use a macro within GP as Tiffany suggested.  IM is also a great solution as Bert and Joni show you.  Just depends on which tool is more comfortable for/available to you.

Have voted on Connect for a system way to do this in mass.  The little things……

——————————
Diana Doane
Ascend Learning, LLC
Leawood KS
——————————
——————————————-

[Tiffany Ziegler]

Diana – this is a great idea – wish I would have thought of that 🙂 Next time I’m definitely taking that route!

——————————
Tiffany Ziegler
Vice President & Controller
Velcor Leasing Corporation
Madison WI
——————————
——————————————-

[Philip Massey]

I jus did this with a client of ours using a very simple SQL script. They had vendors they had not paid in three years, so we inactivated them. If that is what you are looking for, let me know and I’ll give you the script I used. From there, you would just schedule a SQL job to run monthly and you’d be set. Let me know if you want the script. 

——————————
Philip Massey
Massey Consulting
Raleigh NC
——————————
——————————————-