Adimpact.com

From the category archives:

SQL Query

Anytime you’re using a system like GoldMine, it’s a good idea to evaluate how often a field is populated and the different values that are being put in.  For example, here we have our Industry field.

Industry

Are people using it?  If so how many records do we have for the different industries? Fortunately this is an easy question to answer with a pretty simple SQL Query. First, however, you’ll need to do a Right-Click | Properties on the field to get the field name. This is how it’s referenced in the database.

Industry Properties

So our SQL Query to analyze this will be:

SELECT key3, count(*) as '#' FROM contact1 GROUP BY key3

Simply copy and past the SQL Query into Tools | SQL Query and hit query.  You’ll see a result like this.  You’ll notice there’s two blank areas, one for blanks and one for nulls, but essentially they’re the same thing.

Query

You can switch out the fields just be sure to change the name after the select as well as after the group by.  You can also use this for other tables, for example contact2, for custom fields, just change the table name after the FROM.

 

Over the years, I’ve post many SQL Queries that GoldMine users have found very useful.  To see the entire list check out the SQL Category on the right or just use this link: http://www.thegoldmineblog.com/?cat=8

For any of those, here’s how to export those results.  Right-click in the results area, then choose Output To and Excel.  While you can output the results to Word or the Clipboard, you’ll find the formatting off… Excel works the best.  From there you can massage the data, print, save, email, etc.

GoldMine Output SQL Query

The only times you’ll run into trouble is if the SQL Query includes notes, those tend not to transfer well.

GoldMine’s Forecasted Sales allow you to keep track of your open sales pipeline.  If you’re not currently using Forecasted Sales check out this post for the basics: http://www.thegoldmineblog.com/?p=2017

Here’s a handy SQL Query to show all of the past due Forecasted Sales for the team.  To run the query go to Tools | SQL Query:

Past Due Forecasted Sales

Then copy and paste this in:

SELECT   ca.userid                 ,
         c1.company                ,
         c1.contact                ,
         ca.ondate                 ,
         ca.number1  AS Amt        ,
         ca.duration AS Probability,
         ca.ref                    ,
         c1.accountno
FROM     cal ca
         INNER JOIN contact1 c1
         ON       ca.accountno = c1.accountno
WHERE    rectype               = 'S'
AND      ondate               <= GETDATE()
ORDER BY userid

Happy New Year’s Everyone!

I’m looking forward to another great year with GoldMine, and if you have any suggestions for blog postings you’d like to see please drop me a line at chad.smith(@)thegoldmineblog.com!

GoldMine’s Opportunity Manager gives you a great place to track all of your open opportunities.  You can even record your calls and appointments against an opportunity, which are all tracked within the Opportunity Manager.

GoldMine Opportunity Manager History Tab

Of course, we can’t remember all the opportunities we have and open and should be following up on, so here’s a SQL Query to show you all of your open opportunities sorted by the last time you have a history record recorded for them.  To run the query go to Tools | SQL Query:

GoldMine Neglected Opportunity SQL Query

Use this SQL Query:

SELECT   ch.lasthistory,
         o.userid      ,
         o.company     ,
         o.contact     ,
         o.name        ,
         o.status      ,
         o.cycle       ,
         o.stage       ,
         o.source      ,
         o.startdate   ,
         o.foramt      ,
         o.forprob     ,
         o.accountno
FROM     opmgr o
         LEFT OUTER JOIN
                  (SELECT  MAX(ondate) AS LastHistory,
                           loprecid
                  FROM     conthist
                  GROUP BY loprecid
                  )
                  ch
         ON       o.recid = ch.loprecid
WHERE    rectype          = 'O'
ORDER BY ch.lasthistory

Just copy and paste it into the query window and hit Query

I think this concept would be very applicable to some GoldMine Dashboards as well.  If you’re looking for some dashboard help please drop me a line at chad.smith@thegoldmineblog.com.

Hey, Hope everyone is having a great summer!

Here’s a handy SQL Query to show everyone you’ve had a call or appointment with in the last 90 days.  This could be a great list to use to remind of you follow ups you could be doing.

Go to Tools | SQL Query in GoldMine, copy and paste this in and hit query.

Simply change the part where it says ‘USERID = ‘Bill’ to your userid in GoldMine so you’ll be seeing only your activities.

 

SELECT c1.contact   ,
       c1.company   ,
       c1.phone1    ,
       ch.ondate    ,
       ch.ref       ,
       ch.actvcode  ,
       ch.resultcode,
       c1.accountno
FROM   contact1 c1
       INNER JOIN conthist ch
       ON     c1.accountno = ch.accountno
WHERE  ch.srectype IN ('A',
                       'C')
AND    ch.ondate >= GETDATE() - 90
AND    userid     = 'BILL'

Here’s a handy SQL Query that’ll let you search your Notes tab.

Go to Tools | SQL Query, then paste this query into the box, then change ‘GoldMine’ to whatever you’d like to search for!

SELECT   c1.company   ,
 c1.contact   ,
 c1.key1      ,
 n.createddate,
 n.userid     ,
 n.note       ,
 c1.accountno
FROM     contact1 c1
 INNER JOIN notes n
 ON       c1.accountno = n.accountno
WHERE    n.rectype             = 'c1'
AND      CAST(CAST(n.note AS VARBINARY) AS VARCHAR(8000)) LIKE '%GoldMine%'
ORDER BY n.userid,
 createddate

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!

GoldMine Guide to SQL Queries

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.

If you’ve been using GoldMine for a while, and I am sure you have, you probably have a lot of old and outdated data in your database.  For example, everyone probably has something of an Account Type field in your system.

Our pick-list is quite current and update to date, but how do you see if there are any outdated entries in the system?

The solution is pretty easy!

First, find the database name of the field you want to review.  This can be done with a Right-Click | Properties of the field in question.

Since this is my KEY1 field I can use this SQL Query:

SELECT   key1     AS field,
 COUNT(*) AS COUNT
FROM     contact1
GROUP BY key1
ORDER BY COUNT(*) DESC

To run this simply go Tools | SQL Query then past in the query and click on Query.

If the field you want to examine does not have a ‘U’ in front of it simply swap out KEY1 for your field name in the query.  If the field does begin with a ‘U’ then swap out the field name as well as contact1 for contact2 as you’ll be looking at a user-defined field.

From here you can use filters to find those records that don’t fit and fix as needed.

Hey there – hope everyone is having a great summer!

Keeping your data clean in a CRM system like GoldMine, can be a chore.  Here’s a nifty SQL Query that’ll help you find invalid email addresses in Goldmine.  Simply Copy & Paste this into GoldMine’s SQL Query window (Tools | SQL Query) and start cleaning!

SELECT c1.company                                      ,
 c1.contact                                      ,
 cs.contsupref + ISNULL(cs.address1, '') AS email,
 c1.accountno
FROM   contact1 c1
 INNER JOIN contsupp cs
 ON     c1.accountno = cs.accountno
 AND    cs.contact   = 'E-mail Address'
 AND    cs.rectype   = 'P'
 AND
 (
 (
 cs.contsupref + ISNULL(cs.address1, '') NOT LIKE '%@%'
 )
 OR
 (
 cs.contsupref + ISNULL(cs.address1, '') LIKE '%]%'
 )
 OR
 (
 cs.contsupref + ISNULL(cs.address1, '') LIKE '%[%'
 )
 OR
 (
 cs.contsupref + ISNULL(cs.address1, '') LIKE '%)%'
 )
 OR
 (
 cs.contsupref + ISNULL(cs.address1, '') LIKE '%)%'
 )
 OR
 (
 cs.contsupref + ISNULL(cs.address1, '') LIKE '%,%'
 )
 OR
 (
 cs.contsupref + ISNULL(cs.address1, '') LIKE '%..%'
 )
 OR
 (
 cs.contsupref + ISNULL(cs.address1, '') LIKE '%/%'
 )
 OR
 (
 cs.contsupref + ISNULL(cs.address1, '') LIKE '%\%'
 )
 )

Don’t miss our June Special on GoldMineTraining.NET

I have updated “The Guide” for GoldMine 9.0.  Updates include sections on dealing with notes, the new notes table, as well as using the SQL Case/When statement.

GoldMine Guide to SQL Queries

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.

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                                            ,
 CONVERT(varchar(10), conthist.ondate, 101) AS activity_date,
 conthist.ref                                               ,
 CASE
 WHEN srectype = 'A'
 THEN 'Alert'
 WHEN srectype = 'L'
 THEN 'Letter'
 WHEN srectype = 'R'
 THEN 'Required Field'
 WHEN srectype = 'C'
 THEN 'Call'
 WHEN srectype = 'M'
 THEN 'Email'
 WHEN srectype = 'D'
 THEN 'To Do'
 WHEN srectype = 'O'
 THEN 'Other'
 WHEN srectype = 'T'
 THEN 'Action'
 WHEN srectype = 'S'
 THEN 'Sale'
 WHEN srectype = 'K'
 THEN 'Task'
 ELSE srectype
 END AS activity_type,
 contact1.company
FROM     contact1
 INNER JOIN conthist
 ON       contact1.accountno = conthist.accountno
 AND      conthist.ondate BETWEEN '1/1/2011' AND '1/31/2011'
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!

To change the date range simply change the dates after ‘Between’.

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.

GoldMine Guide to SQL Queries