GoldMine’s SQL Query Tab has to be one GoldMine’s most important features. Other than using an external report writer or database utility, there is no other way within GoldMine to find and view data from any GoldMine table in a layout of your liking.

What you’ll like about SQL Queries – It’s free because it’s built into GoldMine. (It’s found as a tab on the same window as “Filters & Groups.”) It’s fast because it leverages the performance of MS SQL to query the database. It’s also flexible because of its ability to look anywhere in GoldMine and you can format the result in various ways. It’s accessible because it’s available to you from within GoldMine. And, once the query is written, it is quite easy to use.

The only hard part is constructing (writing) the query. While GoldMine does have a “Query Builder” which is fine for many simple tasks, you’ll find it falls short of even slightly demanding needs. And when it does that’s when someone without specific SQL knowledge and skill is needed. Most organizations lack such abilities. My suggestion is that you either copy the query from someone who’s written it before, or let someone write it for you. You wouldn’t learn French to ask someone to point you to the restroom, so why try to learn Standard Query Language (SQL) to ask GoldMine to point you to data that is worth finding!

If you really want to learn more about SQL Queries, here’s some simple generic information to help you get started:

SQL is almost English; it’s made up largely of English words, put together into strings of words that sound similar to English sentences. In general (fortunately), you don’t need to understand any arcane technical language to write SQL queries that work.

The first word of each query is its name, which is an action word (a verb) that tells SQL what you want to do. The query name is followed by words and phrases — some required and some optional — that tells SQL how to perform the action. For instance, you always need to tell SQL what to create, and you always need to tell it which table to insert data into or to select data from.

The following is a typical SQL query. As you can see, it uses English words:

SELECT lastName FROM Contact1

This query retrieves all the last names stored in the Contact1 table in GoldMine. More complicated queries, such as the following, are less English-like:

SELECT lastName FROM CONTACT1 WHERE state=’CA’ AND

city=’Fresno’ ORDER By lastName

This query retrieves all the last names and first names of members who live in Fresno and then puts them in alphabetical order by last name. Although this query is less English-like, it’s still pretty clear.

FREE OFFER FOR GOLDMINE USERS – GET 20 FREE SQL QUERIES!

First Direct Corp. will send you 20 complimentary (free) SQL expressions that you can copy & paste in to your GoldMine system (Corporate or Premium Edition).

SQL queries can easily answer all kinds of useful questions!

Request your 20 FREE SQL Queries today and start realizing the power of SQL Queries!

If you’d like to learn more in order to write them yourself, here is a link to “The GoldMine Guide to SQL Queries”

Here are some general points to keep in mind when constructing an SQL query, as illustrated in the preceding sample query:

• Capitalization: The case of the SQL words doesn’t matter. For example, select is the same as SELECT, and from is the same as FROM, as far as SQL is concerned. On the other hand, the case of the table names, column names, and other variable information does matter. Your database table and column names must match exactly, so the case for the column names has to be correct — for example, lastname isn’t the same as lastName.

• Spacing: SQL words must be separated by one or more spaces. It doesn’t matter how many spaces you use; you could just as well use 20 spaces or just 1 space. SQL also doesn’t pay any attention to the end of the line. You can start a new line at any point in the SQL statement or write the entire statement on one line.

• Quotes: Notice that CA and Fresno are enclosed in double quotes (“) in the preceding query. CA and Fresno are a series of characters called text strings or character strings. You’re asking SQL to compare the text strings in the SQL query with the text strings already stored in the database. When you compare numbers (such as integers) stored in numeric columns, you don’t enclose the numbers in quotes.

One useful way to use SQL queries with GoldMine is to check for a number of common data issues. Here are several that your GoldMine administrator can check for:

Simply copy & paste in to the SQL Query tab in your GoldMine system(Corporate or Premium Edition) and start using!

No duplicate accountnos in contact1 & contact2

SELECT COUNT(*), accountno

FROM contact1

GROUP BY accountno

HAVING COUNT(*)>1

For each accountno in c1, there should be 1 and only 1 accountno in c2

Missing CONTACT2 records

SELECT *

FROM contact1

WHERE accountno not in (SELECT accountno

FROM contact2 )

Orphaned CONTACT2 records

SELECT *

FROM contact2

WHERE accountno not in (SELECT accountno

FROM contact1 )

Orphaned CONTSUPP records

SELECT *

FROM contsupp

WHERE rectype <> ‘H’ AND

rectype <> ” AND

accountno not in ( SELECT accountno

FROM contact1 )

Duplicate RECIDs in contsupp. linkacct

SELECT *

FROM contsupp

WHERE recid IN( SELECT linkacct

FROM contsupp

GROUP BY linkacct HAVING COUNT(linkacct)>1 )

No duplicate RecID in entire db

(works for all non tlog tables)

SELECT COUNT(*), recid

FROM contact1

GROUP BY recid HAVING COUNT(*) > 1 or recid IS NULL

No duplicate FRecID, fieldname in conttlog & gmttlog

SELECT COUNT(*), frecid, fieldname

FROM conttlog

GROUP BY frecid, fieldname

HAVING COUNT(*)>1 OR frecid IS NULL OR fieldname IS NULL

For each pending email in the Inbox, there should be a CALENDAR (CAL) entry

SELECT * FROM mailbox

WHERE folder = ‘X-GM-INBOX’ AND recid NOT IN(

SELECT mailbox.recid FROM mailbox, cal

WHERE mailbox.folder = ‘X-GM-INBOX’ AND

mailbox.linkrecid = cal.recid )

For each sent or filed email, there should be a HISTORY (conthist) entry

SELECT * FROM mailbox

WHERE (folder = ‘Sent’ OR folder = ‘Filed’) AND

recid NOT IN( SELECT mailbox.recid

FROM mailbox, conthist

WHERE (folder = ‘Sent’ OR folder = ‘Filed’) AND mailbox.linkrecid = conthist.recid )

Check for invalid rectypes in CALENDER (CAL) table

SELECT * FROM cal

WHERE accountno NOT LIKE ‘PB:%’ AND

rectype NOT IN (‘A’, ‘C’, ‘D’, ‘E’, ‘F’, ‘H’, ‘M’, ‘O’, ‘Q’, ‘S’, ‘T’)

All CALENDAR (CAL) entries of type a, c, d, e, f, o, q, s, t should have a userid

SELECT * FROM cal

WHERE accountno NOT LIKE ‘PB:%’ AND

rectype IN (‘A’, ‘C’, ‘D’, ‘E’, ‘F’, ‘O’, ‘Q’, ‘S’, ‘T’) AND (userid = ” OR userid IS NULL)

FREE OFFER FOR GOLDMINE USERS – GET 20 FREE SQL QUERIES!

First Direct Corp. will send you 20 complimentary (free) SQL expressions that you can copy & paste in to your GoldMine system (Corporate or Premium Edition).

SQL queries can easily answer all kinds of useful questions!

Request your 20 FREE SQL Queries today and start realizing the power of SQL Queries!

If you’d like to learn more in order to write them yourself, here is a link to “The GoldMine Guide to SQL Queries“.

GoldMine 5.0 introduced the ability to create business rules for handling incoming email. In the upgrade to 5.5 this feature now also covers outgoing email. Email Rules, as this feature is called in GoldMine, is often overlooked as a way of improving productivity. Email Rules enable the GoldMine user to instruct GoldMine to automatically handle email for the user according to criteria that the user defines. For example, perhaps all email from a certain senders to be automatically filled in a specific email folder. Or, maybe you want a copy of all email from some sender to automatically be forwarded to another person in your company. The possibilities are too numerous too list, however, if you explore the Email Rules Center, found on the toolbar of your Email Center, you’ll see the wide variety of options for “Conditions” and “Actions” – and with a little imagination you can think of lots of timesaving ideas.

Email Rules are not complicated to create, however, for many GoldMine users it is difficult to get started. Given the potential that Email Rules offer to improve everyone’s productivity, it would be a great idea to hold a little in-house training class on this specific topic. There is a lot to be gained with a little guidance from your GoldMine administrator or a power user.

While most companies that purchase OmniRush do so for the ability to send faxes, an equipment manufacturer loves OmniRush for handling incoming faxes.
The executive secretary that administers OmniRush is able to receive, route and link incoming faxes with GoldMine. She states, “With OmniRush, the moment an incoming fax arrives, I am automatically notified. I can view the fax on my computer and then select which GoldMine user I want to forward it to. Then they receive a GoldMine email and an alarm alerting them that they have a fax. By simply clicking on the attachment they can view the fax on their desktop.” If either the Administrator or the user wants they can link the fax to a GoldMine record for easy future retrieval. It can even be synchronized to remote users.

The OmniRush administrator says, “Without OmniRush, everyone has to get up from their desk and walk across the building to check for or get a fax. Now they’re delivered right to their desk. Before, I had to log and file faxes. Now I don’t need to since the history is in the record and the document is easily called up.” She claims that OmniRush saves her over two hours a week…time definitely better spent otherwise.
OmniRush saves this manufacturer time, money and helps them to better service their customers. The sending options are just icing on the cake! If you’ve only thought about OmniRush for sending faxes, think again about all the advantages it offers.

Do you have a big prospect list that you want to split up evenly for several telemarketers or sales reps to call? Use the Group Scheduling feature in GoldMine to equally divide the calls between multiple users.

Once the list is in GoldMine, filter or group the records. Then schedule a call with the proper details and notes. Before you press schedule, go to the Users tab and add all the people you want working on the calls. Then go to the “Group Schedule” tab and check “Schedule to a group of contacts.” Check the box to “span over multiple days” and to “skip weekends” if those are appropriate. Set the start date and time and how many calls you want scheduled overall each day. Once you press “Schedule” you’ll see, after the process monitor is done, that the calls have been divided evenly and that each call is assigned to one sales person only.

Almost anything anyone does repeatedly in GoldMine can be done in less time and with less effort through the use of Toolbar Icons/Macros.

GoldMine lets you modify the existing Toolbar in order to add and delete icons. You can also create a custom toolbar with macros for the features you use frequently and activities you do often. I like to place my personal “activities toolbar” running vertically down the left side of my screen and leave the standard toolbar along the top.

A right click on the empty section of the toolbar displays the local menu where you can select to insert icons from a standard list of prerecorded and unassigned icons. It’s on this same local menu that you’ll also find the choice to start recording a macro. Once you start recording, it remembers all your actions in GoldMine, including mistakes. It may take a couple tries to get it right. But it’s well worth it for all the time you’ll save!

Toolbars are user and computer specific. So if you’re intending them for others, you’ve got to go around and record them for each user.

Toolbar icons/macros can be used for more than scheduling and completing activities. They can assign Automated Processes that can do just about anything in GoldMine. They can also be used to launch a particular telemarketing script.

Brainstorm those things that make sense to have macros for. Then plan them out down to the last detail before you start to record them. Don’t overlook anything, including even standard reminder notes you may wish to have inside your activities. If you’re missing an appropriate activity or result code, add it to the lookup list before you record the macro!

Basic Steps in Building a Macro:

1. Plan it out in advance – make sure all the lookup selections you want are already in the lookup lists and that you know what you want to type into any notes boxes.

2. Right click on an open space of your toolbar and select “Insert Item.”

3. From the “Insert Toolbar Item” menu, select “Macro” from the drop down list.

4. Select the icon that you desire to assign for your macro from the scrolling list of available icons. Hold your left click on it and drag it up to your toolbar. Release the left click to drop the icon in place. Use your right click to drag it around after.

5. Go back up to the open space on your toolbar and right click to get the toolbar menu again and select “Record Macro” to begin the recording process. You’ll notice the little recording button toolbar opens. The recording process has already begun, so you don’t need to press any of the buttons unless you want to pause or stop the recording.

6. Using your keyboard as much as possible to perform the steps you want your macro to do. (If you make an error, continue below but press “Cancel” at the very end of the process and then start over at step #5 to re-record your macro.)

7. When you are through performing the steps of your macro, press the Stop Recording button. This will automatically bring you to the next step below.

8. Scroll to find and select the same icon that you dragged onto your toolbar in step 4 above. Fill in the description boxes with an explanation of what the macro will do. The top box appears as rollover text on the icon, so keep it as brief as possible (3 words max.). If you are displaying “Button Text” on your toolbar for all the icons, you’ll need to keep the description even shorter. The second box allows you type a much longer description and will scroll to give you more space. This description will appear in the lower left hand corner of your status bar at the bottom of your GoldMine application window. If you want, you can assign the macro to an available “Function Key” as well.

The “Merge Code” feature in GoldMine can help you to market better by assisting you to reach more decision makers and influencers within the firms you are targeting. Adding key additional contacts to your direct mail and email campaigns is one way to better penetrate targeted firms, thereby helping to ensure that your message reaches someone who may find it appealing and act on it.

While Filters and Groups help you to segment your database, merge codes help you to identify the correct individuals within those records that should receive your communications. GoldMine lets you associate one or more merge codes for each contact’s mailing and email address. It takes extra effort to code them in two places, but it does let you distinguish who should get email and who should get snail mail. Make sure to populate the lookup lists with codes so that entries into records are consistent.

Remember that when you’re merging letters or emails based on codes that it also applies to the primary contact, so they should be coded too!