We’re concerned, here, with fields that should contain items from a known list of values. Fields like Customer Type, which is usually coded “Prospect”, “Customer”, or “Vendor”. Or Sales Rep, which simply contains a list of personnel.
This is actually an important part of producing accurate metrics via reporting. If you can’t rely on Sales Rep or Customer Type to be consistent, any numbers generated would always be questionable. We’re going to look at how to produce lists like in the following example.
Does your database contain Customer Types like this?
The first thing we must do before actually writing the query is to identify which field we are working with. In GoldMine, the way to do this is;
Right-click on the field label, then select Properties.
On the Profile Tab, take note of the “Name in Database” value. For example, our Customer Type field is actually called “KEY1”.
Now we can actually write our SQL Query. In GoldMine, select Tools | SQL Query from the top level menu.
The query we’re using is simply an aggregate(count) of the field in question, grouped by all unique values. Copy and paste the following code into your query window;
SELECT KEY1, COUNT(*) FROM CONTACT1 GROUP BY KEY1
And then click “Query”. It’s just that easy. To get lists of other fields, just repeat the process using the different fieldname in place of KEY1.
For example, you can get a list of States with this;
SELECT STATE, COUNT(*) FROM CONTACT1 GROUP BY STATE
For user-defined fields (any field on the lower half of the record), substitute CONTACT2 for CONTACT1, like this;
SELECT USERDEF01, COUNT(*) FROM CONTACT2 GROUP BY USERDEF01
Remember, you can’t hurt anything by selecting data, so have at it and have fun!
