Use coupon code Summer15 for 25% off your order at http://www.goldminetrainingresources.com/.
Offer good through July 4th!
I received this question from a long time blog reader, “Do you have a SQL Query that can list the Companies where there has been NO CHANGE, no pending record entered or History Record in the last year?”
Well, no I don’t, but let’s see what we can do!
So here’s the query:
SELECT c1.company, c1.contact, c1.phone1, c1.address1, c1.city, c1.state, c1.zip, c1.key1, c1.accountno FROM contact1 c1 WHERE lastdate < getdate() - 365 AND c1.accountno NOT IN (SELECT accountno FROM cal WHERE createon >= getdate() - 365) AND c1.accountno NOT IN (SELECT accountno FROM conthist WHERE createon >= getdate() - 365) AND c1.key1 <> 'Unqualified' ORDER BY c1.company, c1.contact
So a couple of things to note. You’ll notice I’m using getdate() – 365 to automatically go back one year, you could replace this with a date, e.g. < 1/1/2015, if you want to hard code the dates. Also, I am excluding any contact marked Unqualified in the key1 field for those contacts that you do not want to contact often.
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.
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.
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.
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.
In our last post we talked about automatically creating Relationship Trees to link all of the contacts at a single company together. There will come a time, though, when you want to update all of the contacts within that Relationship Tree. For example, if one of those organizations becomes a customer.
First, go to the top level of the organizational chart, and then change the value of the field you’ll want to replicate.
Then Right-Click on the top entry and choose Replicate data…
Then choose the field or fields to replicate.
|160813||Prompt if E-mail address is not on file when Auto-Linking received email –
message box causes further downloaded emails not to be handled
correctly -NOT auto linked at all and/or no further prompts
|200761||When display name has a comma (e.g. Doe, John) on a sent email linked
via Outlook integration and reply to all is used original sender’s name will
be split into 2
|201654||Prompt for E-mail file options when deselecting the option “Prompt if E-mail
address is not on file when Auto-Linking received Email.”
|215970||Outlook hangs when trying to link emails to GoldMine when images have
been removed and only the placeholders exist.
|220492||Outlook Messages Linked to Contact Goes to “Pending” Instead of “History”|
|204139||Wrong user is being associated to an email after being linked though the
|220493||The wrong user is shown for a user’s activity in history.|
GoldMine’s relationship tree is a great tool to see all of the contacts at a particular company. However, creating them and keeping them up to date can be a chore.
However, there’s a nice tool within GoldMine’s data management utilities to do this for you. Go to Tools > Data Management > Create Default Relationship Trees.
Normally you’ll be building the relationship trees based on the Company field. You can also have GoldMine create subsections (folders) based on a secondary field, in the example I chose State, and finally, I like to replace all existing relationships, that way all the relationship trees are rebuilt.
|217142||Blank notification email sent if “Page me with alarm when not
acknowledged within..” option is checked
|218285||The body of new email retrieved in GoldMine Mobile 2014
is unavailable in the activity list.
|218288||When completing an email message from the activity list
in GoldMine Mobile 2014 the email does not complete in
GoldMine or the Mail module for GoldMine Mobile
|218857||In GMME when creating a new activity, click ‘Add Contact’
and the heading of the screen selector is ‘title’ when it
should be ‘Add Contact’.
Spring is finally here and it’s time for some Spring cleaning on our GoldMine database!
Here’s a SQL Query that’ll show you any duplicate email addresses you have in GoldMine. This is probably one of the most important queries you can run to ensure that any email blasts you do via GoldMine don’t result in duplicate emails being sent to the same person.
SELECT c1.contact , c1.company , cs.contsupref, c1.accountno FROM contact1 c1 INNER JOIN contsupp cs ON c1.accountno = cs.accountno WHERE ISNULL(cs.contsupref, '') <> '' AND cs.contsupref IN (SELECT contsupref FROM contsupp WHERE CONTSUPP.rectype = 'P' AND CONTSUPP.Contact = 'E-mail Address' GROUP BY contsupref HAVING COUNT(*) > 1 ) ORDER BY c1.contact
If you have two different records with the same email address do a merge/purge of the records.
How to Effectively Use ‘Dummy’ Uses in GoldMine
I got a great question on this from Susan Hallam from Hallam Internet, basically how to do marketing calendars or other public calendars in GoldMine.
One of the great things about GoldMine is that its licensing is based on concurrent users, or who is actually logged in. So that gives us a lot of flexibility in creating ‘dummy’ users to use for a variety of reasons. However, for our topic today, let’s say we have a marketing calendar that includes email blasts, attendance at conferences and the like.
We can create a user in GoldMine called ‘Market’, the username is limited to 8 characters, but we can put ‘Marketing Calendar’ as the full name.
You’ll then be able to access the Marketing Calendar from the calendaring module by changing the user.
When scheduling activities on the calendar I’d recommend using Events since they can show up as multi-day activities, for things like conferences, trade show booths, etc. Since the Events probably aren’t related to any specific contact just be sure to uncheck the Linked box when scheduling. Also, be sure the user for the Event you’re scheduling is Market.
If you’ve been using GoldMine for a while you’re probably very familiar with previewing a filter. Here we have a list of all of our Web Import records.
What you may not have noticed is the Drill Down button in the lower-right, and what it’s for. So, once you have a filter like this previewed, go back to the Filters tab, and select another filter. I’ve selected California.
Go back to Preview and click Drill Down.
You’ll see GoldMine has combined the two filters, so we’re only seeing records in California that are Web Import records. If this is a useful filter for you, click Save to save the combined filter as anew filter.