The GoldMine Blog - Tips, Tricks & More

Here's a handy SQL Query for you.  It will show all primary and secondary contacts in the database.  If the secondary contact has an address it'll display that address and if they don't it'll use the address information from the primary contact.  To use go to:

Lookup | SQL Query then paste this into the box and hit query.  I tested this on 6.7 SQL 2000 & 7.0 SQL 2005.  It won't work on dBASE.

select company, contact, title, address1, address2, address3, city, state, zip, phone1, contact1.accountno from contact1
union
select contact1.company, contsupp.contact, contsupp.title, contact1.address1, contact1.address2, contact1.address3, contact1.city, contact1.state, contact1.zip, contsupp.phone, contact1.accountno from contact1, contsupp where rectype = 'C' and (contsupp.address1 is null or contsupp.address1 <= '') and contact1.accountno = contsupp.accountno
union
select contact1.company, contsupp.contact, contsupp.title, contsupp.address1, contsupp.address2, contsupp.address3, contsupp.city, contsupp.state, contsupp.zip, contsupp.phone, contact1.accountno from contact1, contsupp where rectype = 'C' and contsupp.address1 > ' ' and contact1.accountno = contsupp.accountno
order by company, contact

Here's a SQL Query request that comes up often...  How to find all secondary contact and their email addresses:

Here ya' go!

select contact1.company, contsupp.contact, contsupp2.contsupref + contsupp2.address1 from contact1, contsupp, contsupp as contsupp2 where contsupp.rectype = 'C' and contact1.accountno = contsupp.accountno and contsupp.recid = contsupp2.linkacct order by contact1.company

Now wouldn't the GoldMine Guide To SQL Queries (http://www.thegmblog.com/sql4gm/) make a great Holiday gift for that special GoldMiner in your life?

Ever wondered where your records are coming from? 

Here's a SQL Query that'll show you the number of records created, by their source for 2007.  The dates can easily be changed so show whatever date range you'd like.  This should work on any SQL based GoldMine system. 

select source, count(*) as "#", max(accountno)
from contact1
where createon >= '1/1/2007' and createon <= '12/31/2007'
group by source
order by source

If you're using a dBASE GoldMine, this won't give a summary but will display the information:

select source, company
from contact1
where createon >= '1/1/2007' and createon <= '12/31/2007'
order by source

P.S. Want to learn how to write your own SQL Queries in GoldMine?
Check out http://www.thegmblog.com/sql4gm/

… not a pleasant thought, eh?

Fortunately, though, if you use GoldMine forgetting about, and then loosing, an important customer because you ‘forgot’ about them is easy to avoid. 

Here’s a SQL Query that will show us all clients that have no history this year:

select contact1.company, contact1.contact, contact1.phone1, contact2.lastconton
from contact1, contact2
where contact1.accountno = contact2.accountno and
contact1.accountno not in (select accountno from conthist where ondate >= '1/1/2007') and
contact1.key1 = 'Client' and
contact1.key4 = 'Chad'
order by company

To use this, from GoldMine’s main menu choose Lookup | SQL Queries then Copy & Paste the above in the top section then click Query. 

There are a couple of tweaks that you may want to make so this work optimally on your system.

First, the contact1.key1 = ‘Client’.  My GoldMine is configured so the Key1 field, normally located at the top of the lower-right quadrant, denotes client, prospect, etc.  You may want to change that to the field you use and/or the correct nomenclature, e.g. client, customer, etc.

(Here's a screen shot so you know what I mean by lower-right quadrant).

Second, the contact1.key4 = ‘Chad’.  My GoldMine is configured so the Key4 field, normally located in the 4^th position in the lower-right quadrant, denotes the account manager.  You may want to change that to the field you use and/or correct nomenclature.

Also, you may want to change the date, you can use any date in there that makes sense.

For more information on saving queries, using queries to make groups, etc. check out The GoldMine Guide to SQL Queries at http://www.thegmblog.com/sql4gm

You’re not alone.  You contemplate life and the weekend on a Friday afternoon and wonder what you, your sales reps, and/or your customer service personnel did the past week.  Fortunately, if you’ve trained your staff to actively use GoldMine to schedule and complete activities to the History Tab this is going to be a very easy question to answer.  

... if you haven't drop me a line (248.506.5222) to discuss some GoldMine training.. 

I actually run this every week for myself, usually during the laid back hour of 7:00 to 8:00 AM on a Saturday to see if I need to schedule some follow up activities for the following week.  It’s not that I particularly like being up at that time, but “Black Jack” my Cairn Terrier does!  Who’s to argue?

So, to answer this question…  Go to Lookup | SQL Queries in GoldMine.  Paste in this code to the upper part of the screen:

select conthist.userid, conthist.ondate, conthist.ref, conthist.rectype, contact1.company
from contact1, conthist
where contact1.accountno = conthist.accountno and conthist.ondate >= '6/8/2007' and conthist.ondate <= '6/16/2007'
order by userid, ondate

Then click Query.

You’ll see your results.  If you find this useful use the ‘Save’ button to reuse this later on and just change the date range…  Within the results, you can do a Right-Click | Output To | Excel to save the results, print them, and further analyze!

If  you are on GoldMine Corporate Edition or GoldMine Premium Edition you can use this query:

select conthist.userid, conthist.ondate, conthist.ref, conthist.rectype, contact1.company
from contact1, conthist
where contact1.accountno = conthist.accountno and conthist.ondate >= getdate() - 7 order by userid, ondate

To automatically show you the past 7 days.

This is a great example of how useful some simple SQL Queries can be.  To learn more about queries check out The GoldMine Guide to SQL Queries.

Ever wonder how many primary contacts, secondary contacts, or history items you have in your GoldMine database?

Here are three simple SQL Queries to give you an answer!

First, go to Lookup | SQL Queries or Go To | Filters & Groups | SQL Queries Tab in GoldMine Premium Edition.  Copy and paste the queries into the top half of the screen then click the Query button.

 
For a count of primary contacts:

Select count(*) from contact1  

For a count of secondary contacts:

Select count(*) from contsupp where rectype = 'C'

For a count of history records:

Select count(*) from conthist

Go to Lookup | SQL Queries or in GoldMine Premium Edition Tools | Filters & Groups then click on the SQL Query Tab.  Then, paste in this:

select userid, srectype as Type, count(*) as Number from conthist where srectype in ('A', 'C', 'M') and ondate >= '1/1/2000' and ondate <= '6/30/2008' group by userid, srectype

Change the date range to suite your needs… This counts Appointments (A), Call (C), and Emails (M).

This is a great example of multi-field grouping, and how it can be very useful in SQL Queries.

Don't forget, you can send these results to Excel:

http://www.thegmblog.com/2007/10/the_one_that_didnt_get_away_fi.php 

… In today’s economic environment having every bit of information you can have about a contact is incredibly important.

For quick, easy, and cheap communication perhaps none is as important as an email address.

So here’s a SQL query that will show you all your contacts without an email address!

select company, contact, city, state, zip, key1, key2, key3, key4, key5 from contact1 where accountno not in (select accountno from contsupp where contact = 'E-mail Address' and rectype = 'P') order by company, contact

To use this, from GoldMine’s main menu choose Lookup | SQL Queries then Copy & Paste the above in the top section then click Query.  If you are using GoldMine Premium Edition then Tools | Filters & Groups and then the SQL Query tab.

 

You’ll see your results.  If you find this useful use the 'Save' button to reuse this later on. Within the results, you can do a Right-Click | Output To | Excel to save the results, print them, and further analyze!

To learn more about how SQL Queries & eMarketing with GoldMine check out...

&