The GoldMine Blog - Tips, Tricks & More

Here’s a very handy SQL Query to find the last time you met with your contacts…  Go to Lookup | SQL Queries and paste this into the Top ½ of the Window:

select contact1.company, contact1.contact, contact1.key1, conthist.userid, conthist.ondate, conthist.ref from contact1, conthist where contact1.accountno = conthist.accountno and conthist.recid in (select max(recid)  from conthist where srectype = 'A' group by accountno) order by key1, ondate

Then Click Query!

Don’t Forget you can Send the Results of the Query to Excel with a Right-Click | Output to Excel.

This and over 60 other very use queries are await you in The GoldMine Guide to SQL Queries!

 

 

PS – Don’t forget about the GoldMine Premium Edition Webinar Coming up on Tuesday!

http://www.thegmblog.com/premium

GoldMine allows you to automatically be reminded every year of a customer, prospect or any acquaintance’s birthday, anniversary, etc. using Occasions.  Once set, you’ll get a reminder every year before the day of the event.  

Never forget your wife or husband’s birthday again! ;-)

First, find the contact record you want to create the Occasion for.

Then go to Edit | Record Properties | Contact Details. 

Then click New to create a new Occasion…  A contact can have multiple Occassions…

 

Use the Reference field as a reminder of the detail of the Occasion, and use the Category field for the type.  GoldMine has predefined the categories as: Other, Anniversary, Birthday, Festival, Reunion and Retreat.  The User will be who gets the reminder.  It’ll default to you, but can be changed.

 

Then click on the Date Span tab.

Here you’ll want to set the Date as well as how many days before the date you’ll want an Alarm.  The "Occurs" section will automatically update based on the Date.  

 

Click OK.

You’ll now see the new Occasion!

Occasions also show up in your Activity List and Calendar!

 

 

It’s getting to be that time of year again, and with it the challenge of figuring out who should be getting what during the Holiday Season.  

There are several different ways of doing this in GoldMine, but let me show you my favorite… This method uses the “Merge” field – and is usable for both primary and secondary contacts.  


 


To configure the merge field for Holiday use we just have to change the lookup values for the primary contact, the same merge codes will automatically be used for secondary contacts.

Within the Merge field, either do a Right-Click or use the F2 key on the keyboard.



Single Left-Click on the New button to add different merge codes.  You may want to use a single one or multiple depending on how you handle cards and gifts.  The two I have here are:

HC for Holiday Card and
HG for Holiday Gift.

We use the format:

HC;//Holiday Card (Code, semi-colon, two slashes, and then description).

Only the code will be placed in the field.  With this format you can fit up to 5 codes in the merge code field.  Select one code, as you normally would when using the lookup list, then select a second in the same manner.  GoldMine will automatically place a comma and space between the two instead of replacing one code with another. 



You can use any number of letters or numbers for the code but all codes should have the same number of letters/numbers.  I prefer two which allows up to 5 codes but also makes it easy to remember what each is for.  You may want to use HC for Holiday Cards, but H1, H2, and H3 for different levels of gifts…  

Get started on this and in a future entry we’ll discuss getting that list out of GoldMine.

Hey There!  Happy Halloween! 

In our previous entry we discussed using the merge code fields to tag who should be getting holiday cards and gifts.  Now that you’ve gone through the process of adding the appropriate merge codes to your contacts, you probably want to see who has been tagged!

If you are only working with primary contacts I suggest using a filter…  Your filter will look something like this:



The key here is to use the Contains operator, as there may be multiple values in the field.

Once you’ve built your filter you can see the list from the Filter’s Preview tab as well as from the Contact Search Center!  Review this post on how to do that:

http://www.thegmblog.com/2007/09/so_easy_yet_so_useful_how_to_p_1.php

You can, then, export from the search center, use GoldMine’s report’s to print labels, etc…  

You can also use GoldMine's Export Wizard available under Tools | Import/Export Wizard | Export Contact Records.  The wizard gives you a variety of options of the export format (dBASE, ASCII, etc.) as well as whether you want to export primary and/or secondary contacts as well as limiting the export to a merge code.

 

Once exported, you can use MS Word® to print labels, etc.  My only complaint with the Export wizard here is that if the additional contact doesn’t have an address listed the Export wizard won’t automatically pick up the address of the primary contact.

Which brings us to our last method of export, a SQL Query, actually three.

For a review of using SQL Query's Check Out This Post: http://www.thegmblog.com/2007/10/the_one_that_didnt_get_away_fi.php 

Here’s one for primary contacts:

select company, contact, title, address1, address2, address3, city, state, zip, phone1, contact1.accountno from contact1 where mergecodes like ‘%HC%’ order by company, contact

Here’s one for secondary contacts without an address (It’ll pick up the address from the primary contact):

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 and contsupp.mergecodes like ‘%HC%’ order by company, contsupp.contact

Finally one for secondary contacts with an address:

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 and contsupp.mergecodes like '%HC%'
order by company, contsupp.contact

Just change the ‘HC’ in each query to the mergecode you’re using.  After running each you, can do the Right-Click | Output to Excel trick and you’re on your way!