Points: 15050

11/3/2016 8:37:42 AM

All Vendors that do not have a record in the transactional tables

* This article, and all our great SQL (Dynamics) documentation, Is available on the SQL (Dynamics) menu

I received a request yesterday to write a query that would find all vendors that did not have a record in any of the transactional tables; so I thought I'd talk for a little bit about that.

The first thing is that you should not be doing this if you don't understand SQL or the Dynamics GP table structure. Take a backup first.

Second, there is a little bit of selectivity in use here. I started with a query of all the tables that use the VENDORID field, in my install there are 188. Too many for me to want to include all of them in my searching. But, I know that I can eliminate the SVC tables and the JC tables because this client doesn't use them.

So, you would use the script below as a template and modify it to your needs.
Version: All
Section: SQL Scripts
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables