The GoldMine Blog

Where Are My Contacts Coming From!?!?

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/

Please Note: The GoldMine Blog has moved to http://www.thegmblog.com

SQL Query for Secondary Contacts & their Email Addresses

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?

Please Note: The GoldMine Blog has moved to http://www.thegmblog.com

Great SQL Query - All Primary & Additional Contacts

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

Please Note: The GoldMine Blog has moved to http://www.thegmblog.com