Love it or hate it, the SQL Query tool in GoldMine is unavoidable sometimes, especially when you want to get “fancy” with searching. So, we it comes to looking up things like “contacts with history” or “contacts with no history in the last X days”, we have no choice but to become temporary experts in SQL. Here are a few examples to get you started;
- First, open up your SQL Query tool by going to Tools | SQL Query.
- To use it, simply paste in your SQL code into the top window and click the “Query” button.
- Profit! 🙂
SQL Code examples (for clarity purposes, all SQL code is given in CAPS);
Contacts with no history
SELECT CONTACT, COMPANY FROM CONTACT1 WHERE ACCOUNTNO NOT IN (SELECT ACCOUNTNO FROM CONTHIST)
Contacts with no history in the last year
SELECT CONTACT, COMPANY FROM CONTACT1 WHERE ACCOUNTNO NOT IN (SELECT ACCOUNTNO FROM CONTHIST WHERE ONDATE >= GETDATE()Â – 365)
Contacts with no email address
SELECT CONTACT, COMPANY FROM CONTACT1 WHERE ACCOUNTNO NOT IN (SELECT ACCOUNTNO FROM CONTSUPP WHERE CONTSUPP.CONTACT = ‘E-mail Address’)
You’ll find that SQL queries are lightning quick, and don’t forget that you can build a GoldMine Group from the Query Results via the Add New Members wizard. Try it, and have fun!
