For quick, easy, and cheap communication with your leads, prospects, and customers, having an email address is of utmost importance. While your database is probably quite old, you can start monitoring new contacts that were created without an email address today.
Use this to encourage colleagues to get email addresses for these people, and even run a contest to see who can get the most email addresses!
So here’s a SQL query that will show you all your contacts added within the last 30 days that don’t have 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'
)
AND createon >= GETDATE() - 30
ORDER BY company,
contact
To use this, from GoldMine’s main menu choose Tools | SQL Query then Copy & Paste the above in the top section then click Query.
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!
When you invest in Crystal Reports for GoldMine not only do you get 49 ready-to-use to Crystal Reports for your edition of GoldMine (Corporate 6.7, Corporate 7.0, Corporate 7.5 or Premium 8.x/9.x) but I’ll also release BONUS reports from time to time that you’ll be able to get at no additional charge.
Here’s one of those…
(Click on the images for a larger version)
Call and Appointment Report by User – Selection Criteria
Call and Appointment Report by User – Report Sample
If you have already purchased Crystal Reports for GoldMine simply login to the download site with your credentials to download the new Crystal Report.
Of course, if you need some custom Crystal work for GoldMine or other applications please drop me at chad.smith@thegoldmineblog.com.
I’ve been a long time user of CompanionLink for syncing GoldMine with applications like Outlook as well as my iPhone. The folks at CompanionLink have recently released a major new release 5.0, which has a great new user interface for syncing GoldMine with Outlook, iPhones, Android devices, etc. I just tested out the GoldMine to Gmail sync for contacts and am very impressed! Worked like a charm. More info on the upgrade at: http://www.companionlink.com/blog/2011/09/companionlink-5-launches-today/
http://www.companionlink.com
When purchasing CompanionLink be sure to use Affinity Code TB5222….
Happy New Year Everyone!
If one of your New Year’s resolutions is to improve your follow ups here’s a SQL Query that you’ll find helpful. It will show a list of contacts as well as the date of the last completed appointment, and next scheduled appointment, as well as last completed call, and next scheduled call.
SELECT c1.company ,
c1.contact ,
c1.phone1 ,
la.ondate AS Last_Appt,
na.ondate AS Next_Appt,
lc.ondate AS Last_Call,
nc.ondate AS Next_Call,
c1.key1 ,
c1.key2 ,
c1.key3 ,
c1.key4 ,
c1.key5 ,
c1.accountno
FROM contact1 c1
LEFT OUTER JOIN
(SELECT MAX(ch.ondate) AS ondate,
ch.accountno
FROM conthist ch
WHERE srectype = 'A'
GROUP BY accountno
) AS la
ON la.accountno = c1.accountno
LEFT OUTER JOIN
(SELECT MAX(ca.ondate) AS ondate,
ca.accountno
FROM cal ca
WHERE rectype = 'A'
GROUP BY accountno
) AS na
ON na.accountno = c1.accountno
LEFT OUTER JOIN
(SELECT MAX(ch.ondate) AS ondate,
ch.accountno
FROM conthist ch
WHERE srectype = 'C'
GROUP BY accountno
) AS lc
ON lc.accountno = c1.accountno
LEFT OUTER JOIN
(SELECT MAX(ca.ondate) AS ondate,
ca.accountno
FROM cal ca
WHERE rectype = 'C'
GROUP BY accountno
) AS nc
ON nc.accountno = c1.accountno
WHERE c1.key1 = 'Customer'
AND c1.key4 = 'BILL'
ORDER BY c1.company
To run this simply go Tools | SQL Query then past in the query and click on Query.
You’ll notice at the end in the WHERE clause I have:
c1.key1 = ‘Customer’
which will limit the contacts returned to just customers.
I also have:
c1.key4 = ‘BILL’
which limits these only to Bill’s contacts. You will want to tweak fields and values for your particular needs, or take the entire where clause out for the entire database.
