Dynamics Community Forum Thread Details

SQL to show customers and vendors with no activity

Does anyone have an SQL query to show customer and vendors that have no activity? We have a number of customers and vendors who were setup in the wrong company in GP and should be removed. They have no history, transactions, etc., and will delete cleanly, but I don't have a good way to identify them. I was thinking and SQL script would be nice, but I'm not sure what tables I need to check against.

Thanks,

George

George,

Part of the difficulty with this is that it depends on what modules you're using in GP. Below is some sample code for finding customers that do not have any transactions in RM or SOP modules:

select CUSTNMBR from RM00101

where CUSTNMBR not in (select CUSTNMBR from RM00401) -- this should capture all RM tables

and CUSTNMBR not in (select CUSTNMBR from SOP30200) -- all SOP historical trx

and CUSTNMBR not in (select CUSTNMBR from SOP10100) -- all SOP open trx

if you think you need to check more modules/tables you can just add additional tests based on the example above.

Similarly for vendors, if you are only using the PM module, you could use the following:

select VENDORID from PM00200

where VENDORID not in (select VENDORID from PM00400)

If you need to check more modules, you would need to add additional tests.

For help with tables, you can check my blog, I have a list of the commonly used tables for the main modules: http://victoriayudin.com/gp-tables/. You can also look for every table that has CUSTNMBR or VENDORID using this code: http://victoriayudin.com/2010/04/23/how-to-find-all-sql-tables-with-a-column-name/. That may not be every table that has a customer or vendor ID, but for your purposes I believe it should suffice.

Hope this helps.

Quick Links

Leaderboard