Open Forum

 View Only
Expand all | Collapse all

Mass Vendor Change from active to inactive

  • 1.  Mass Vendor Change from active to inactive

    Posted Sep 22, 2017 04:39 PM
    Hello,  we are GP2016R2.  We have an ancillary company with 800 vendors and we only use 50-100 of them.  Is there an "easy" way to change them all to inactive​?  We figured it w/b easier to change individually back to active rather than inactive.  thanks!!

    ------------------------------
    Ronda Smith
    Arkansas Specialty Orthopaedics
    Little Rock AR
    ------------------------------


  • 2.  RE: Mass Vendor Change from active to inactive

    TOP CONTRIBUTOR
    Posted Sep 22, 2017 05:12 PM
    Hi Ronda,

    Assuming you have access to SQL Management Studio, you can execute the following script to set ALL vendors to INACTIVE.  You could also add a where clause if you would like to limit this to a certain customer class or something else.  Let me know if you have any questions and please do this in your test environment first to make sure you really want to do this.

    update RM00101 set INACTIVE = 1

    ------------------------------
    Tom Monfre
    Batteries Plus Bulbs, LLC
    Hartland WI
    ------------------------------



  • 3.  RE: Mass Vendor Change from active to inactive

    GOLD CONTRIBUTOR
    Posted Sep 22, 2017 05:19 PM
    The SQL above will change ALL vendors to inactive, I don't think that is what you want to do. You want to inactivate most of your vendors, but not all of them, correct?

    You can accomplish this at least 2 different ways... I am sure there are more...

    1. If you have SQL access, then create a temp table with the vendors you want to inactivate and then run the SQL to inactivate those vendors only;

    2. If you don't have SQL access then you can create a MACRO from excel to inactivate those vendors from a list in the excel file.

    ------------------------------
    Kerry Hataley
    CEO & President
    Nanook Software, Inc
    ------------------------------



  • 4.  RE: Mass Vendor Change from active to inactive

    Posted Sep 25, 2017 03:52 AM
    Correct me if I'm wrong - but will the above SQL statement - not change all DEBTORS (Customers)  - rather than Vendors (Creditors)  -

    To change Vendors, I think you need:
           UPDATE PM00200 set VENDSTTS = 2

    Hope this helps

    Thanks


    ------------------------------
    Ray Phelan
    FRS Network Ltd
    Ireland
    ------------------------------



  • 5.  RE: Mass Vendor Change from active to inactive

    TOP CONTRIBUTOR
    Posted Sep 25, 2017 10:08 AM
    ​When you go behind Great Plains' back to change active flags for vendors and customers, you also do not benefit from the logic of the code that Great Plains runs when you inactivate vendors and customers through the data entry windows.

    It would be FAR BETTER to write a macro using Excel formulas or Word Mail Merge to inactivate these vendors/customers, which allows Great Plains to run through the logic that makes these actions safer.

    ------------------------------
    Bruce Strom
    Programmer Analyst
    Associated Grocers of Florida
    sunrise FL
    ------------------------------



  • 6.  RE: Mass Vendor Change from active to inactive

    Posted Sep 25, 2017 10:34 AM
    ​Morning.  The solution to address this is a revision of the SQL script "update RM00101 set INACTIVE = 1".  If you add a Where clause at the end, you can only impact the vendors needed. 
    First, download a SmartList with all vendors into an Excel spreadsheet.  Filter the list to only the vendors that need to become inactive.  In Excel, use the following formula in the far right column once you have the vendor down to a list that you want to make inactive - =concatenate((update RMA00101 set INACTIVE = '1" where VendorID = ",ColumnA(which is VendorID)) - The syntax needs some massaging, but the goal is to get the concatenated SQL code and copy into SQL to update only the vendors that need to be updated.
    Thanks - Chuck

    ------------------------------
    Chuck Docekal
    SMB Suite/Nextcorp
    Irving TX
    ------------------------------



  • 7.  RE: Mass Vendor Change from active to inactive

    GOLD CONTRIBUTOR
    Posted Sep 25, 2017 11:15 AM
    As mention before you need to use PM00200 not RM00101 - RM00101 is for Sales or AR not AP.

    Now Bruce is correct, you need to consider business logic. So if SQL and Dynamics GP are not your love then the MACRO is the best solution but it could create MACRO errors when something is left openly the vendor.

    If you are doing the SQL approach, then you need to add in validation to look for open payments / credits and open shipping and invoice receipts.

    Since you don't want to inactivate a vendor if they have unpaid invoices, open shipping receipts etc.

    Just more to think about....

    ------------------------------
    Kerry Hataley
    CEO & President
    Nanook Software, Inc
    ------------------------------



  • 8.  RE: Mass Vendor Change from active to inactive

    Posted Sep 25, 2017 12:46 PM
    ​Thank you all for your input!

    ------------------------------
    Ronda Smith
    Arkansas Specialty Orthopaedics
    Little Rock AR
    ------------------------------



  • 9.  RE: Mass Vendor Change from active to inactive

    Posted Sep 25, 2017 01:38 PM
    I use the following script. 


    The script checks to see all vendors who last had a check issued 12 months back and the last purchase date falls within 12 months as well. The script also checks to ensure the vendors are active and have a current balance of $0.00. If you want to change the date ranges simply modify them in the WHERE clause at the bottom.

    ------------------------------
    Jonathan Cox
    Frontier Tubular Solutions, LLC
    Oklahoma City OK
    ------------------------------



  • 10.  RE: Mass Vendor Change from active to inactive

    GPUG ALL STAR
    Posted Sep 26, 2017 02:53 AM
    ​Ronda,
    I also would strongly recommend that you do not change the status from the back end. I like the macro idea much better. Since you do not have that many vendors, the macro will run quickly and will enforce the business logic. A problem I see with the macro approach is that the macro will crash on each vendor that does not qualify to be made inactive. If you have Integration Manager, you can update the vendor status and allow enough errors to throw so that it will only change the vendors that qualify.
    If you want to go the SQL route, you'll need to make sure:
    • The Vendor doesn't have any current or unposted transactions.
    • The Vendor doesn't have a 1099 amount for the current year.
    • All Closed purchase orders for that vendor have been removed using the Remove Completed Purchase Order Utility.
    • The vendor isn't set up for expense reimbursement in Dynamics GP Project Accounting module.
    Take a look at

    ------------------------------
    Leslie Vail
    Accounting Systems Consulting, Inc.
    Dallas TX
    leslievail@earthlink.net 972-814-8550
    ------------------------------



  • 11.  RE: Mass Vendor Change from active to inactive

    Posted Sep 26, 2017 11:21 AM
    This is also a very simple integration if you use Integration Manager.

    ------------------------------
    Kimberly Lomax
    Manager, Financial Systems
    Rayonier
    Jacksonville FL
    ------------------------------



  • 12.  RE: Mass Vendor Change from active to inactive

    GPUG ALL STAR
    Posted Sep 26, 2017 12:54 PM
    Go vote for this product suggestion to allow mass inactivating for master records from the navigation lists:  https://connect.microsoft.com/dynamicssuggestions/Feedback/Details/3127859

    It would be a great feature for future releases.
    Thanks
    Windi

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



  • 13.  RE: Mass Vendor Change from active to inactive

    Posted 20 days ago
    In case others are searching and come across this post. You can now mass update inactive/active via the Navigation list. Yay! Here are some details. Thank you to all who voted.  https://community.dynamics.com/gp/b/dynamicsgp/posts/microsoft-dynamics-gp-2018-r2-mass-update-inactive-action-within-navigation-lists-inactivate-reactivate-temporary

    ------------------------------
    Angela Morehouse
    CC Industries
    Chicago IL
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role