GoldMine

Documents & Files
 

General Items


Creating Counter Fields

GoldMine can track an unlimited number of uniquely named counters. The counter values are stored in the LOOKUP table. For example inserting ~COUNTER("ID",1) into the lookup pick list for a field provides a unique number starting at 0 that increments by 1 each time it is selected by the user.

Extra User Variables

You are not restricted to holding the four variables in the Personal tab in User Preferences

Create extra variables by amending the username.ini file.
Create the variables under the [user_var] section
e.g.
[user_var]
Var1=www.prior-analytics.com
This can then be referenced in email merges and mail merges

Check DBASE Expression

Hold down the following keys:
Ctrl
Shift
D

Command Switches

The /u: stands for the username
The /p: stands for the users password
/s:goldsync /u:master /p:access

Sounds for incoming email

GoldMine needs to first be set to auto-retrieve email.

Go to Edit > Preferences > Internet tab > More Options > Accounts tab > Edit, then check Auto-retrieve.

Set the time intervals that GoldMine will check for incoming email. Go to Edit > Preferences > Internet tab > More Options > Retrieval, then check Retrieve mail every xx minutes.

To select a sound to be played when new email has arrived, open the Control Panel in Windows (95, 98, ME, NT, 2000, XP). Click on the Sounds or Sounds and Multimedia icon. On the Sounds tab, scroll through the Sound Events and locate New Mail Notification. Click Browse and select any .WAV file to play when new mail comes into to GoldMine, or any other email client.

Remove Word Prompt for Update File

Go into your Word Options, and turn off Update links when loading program.

SQL Queries

Changing the name of a column header in an SQL statement

select contact,company,zip as Pcode from contact1
This will represent the column header Zip as PCode

Primary and Additional contacts e-mail addresses

select contact1.company,contact1.contact,
contsupp.address2 as EM_Contact,
contsupp.contsupref,
contsupp.zip from contsupp join contact1
on contact1.accountno=contsupp.accountno and
contsupp.contact = "E-mail Address"
order by contact1.company

Today's Completed Activities for all users

select * FROM CONTHIST WHERE DAY(ONDATE) = day(getdate()) AND MONTH(ondate) = MONTH(GETDATE()) AND YEAR(ONDATE)=YEAR(GETDATE())

Cleaning emails addresses - Based on a missing @ symbol

select * from contsupp where rectype='P' and contact='E-mail address' and not(contsupref like '%@%')

Finding Duplicate Company Records

select company,contact,phone1,zip,key1,key2,key3,key4,key5 from contact1 where company in (select company from contact1 group by company having count(*) >1) order by company

Primary contacts and the primary email address

select contact1.company, contact1.contact, contact1.title,contact1.phone1,contsupp.contsupref from contsupp,contact1 where contact1.Accountno = contsupp.accountno and contsupp.contact="E-mail Address" and contsupp.zip like "_1%"

Primary and additional contacts with their associate email addresses.

select contact1.contact,contsupp.address2,contact1.company, contsupp.contsupref, contact1.owner from contsupp join contact1 on contact1.accountno= contsupp.accountno and contsupp.contact ='E-mail Address' order by contact1.company

Primary and additional contacts with their associate email addresses by mergecodes

select contact1.contact,contsupp.address2,contact1.company, contact1.mergecodes, contsupp.mergecodes as mer, contsupp.contsupref, contact1.owner from contsupp join contact1 on contact1.accountno= contsupp.accountno and contsupp.contact ='E-mail Address'
WHERE contact1.mergecodes like '%TWB%' and contsupp.mergecodes like '%cmsa01%'

User defined fields

select count(*) from contudef where dbfname ='CONTACT2' and field_name not like 'USERDEF%' and field_name like 'U%'

Merge Codes of Additional Contacts - Works only on SQL systems

Select cs.address2, cs.contsupref, cs.mergecodes as Mailcode, cs2.mergecodes as emailcode from contsupp as cs, contsupp as cs2 where cs.linkacct = cs2.recid and cs.address2 is not null order by cs.address2

All E-mail Address and Secondary Contact Names from CONTSUPP

SELECT address2, contsupref FROM CONTSUPP WHERE contsupp.rectype = "P" and contsupp.address2 <> ""

All Secondary Contacts joined with Primary Address

SELECT Contact1.COMPANY, ContSupp.CONTACT AS ContSupp_CONTACT, Contact1.ADDRESS1, Contact1.CITY, Contact1.STATE, Contact1.ZIP
FROM Contact1 LEFT JOIN ContSupp ON Contact1.ACCOUNTNO = ContSupp.ACCOUNTNO
WHERE (((ContSupp.RECTYPE)="C"));

Secondary Contacts joined with Primary Address WHERE ContSupp Address1 is empty

SELECT Contact1.COMPANY, ContSupp.CONTACT AS ContSupp_CONTACT, Contact1.ADDRESS1, Contact1.CITY, Contact1.STATE, Contact1.ZIP
FROM Contact1 LEFT JOIN ContSupp ON Contact1.ACCOUNTNO = ContSupp.ACCOUNTNO
WHERE (((ContSupp.RECTYPE)="C") AND ((ContSupp.ADDRESS1) Is Null));

Secondary Contacts joined with Primary Address WHERE ContSupp Address1 is empty and Contact1 Address1 is not empty

SELECT Contact1.COMPANY, ContSupp.CONTACT AS ContSupp_CONTACT, Contact1.ADDRESS1, Contact1.CITY, Contact1.STATE, Contact1.ZIP
FROM Contact1 LEFT JOIN ContSupp ON Contact1.ACCOUNTNO = ContSupp.ACCOUNTNO
WHERE (((ContSupp.RECTYPE)="C") AND ((ContSupp.ADDRESS1) Is Null) AND ((Contact1.ADDRESS1) Is Not Null));

Secondary Contacts joined with Primary Address WHERE ContSupp Address1 is empty and Contact1 Address1 is not empty and MERECODE =

SELECT Contact1.COMPANY, ContSupp.CONTACT AS ContSupp_CONTACT, Contact1.ADDRESS1, Contact1.CITY, Contact1.STATE, Contact1.ZIP, ContSupp.MERGECODES
FROM Contact1 LEFT JOIN ContSupp ON Contact1.ACCOUNTNO = ContSupp.ACCOUNTNO
WHERE (((Contact1.ADDRESS1) Is Not Null) AND ((ContSupp.RECTYPE)="C") AND ((ContSupp.ADDRESS1) Is Null) AND ((ContSupp.MERGECODES)LIKE"%111"));

Secondary Contacts joined with Primary Address WHERE ContSupp Address1 is empty and Contact1 Address1 is not empty and MERECODES Displayed

SELECT Contact1.COMPANY, ContSupp.CONTACT AS ContSupp_CONTACT, Contact1.ADDRESS1, Contact1.CITY, Contact1.STATE, Contact1.ZIP, ContSupp.MERGECODES
FROM Contact1 LEFT JOIN ContSupp ON Contact1.ACCOUNTNO = ContSupp.ACCOUNTNO
WHERE (((Contact1.ADDRESS1) Is Not Null) AND ((ContSupp.RECTYPE)="C") AND ((ContSupp.ADDRESS1) Is Null));

All ContSupp Addresses

SELECT ContSupp.ACCOUNTNO, ContSupp.CONTACT, ContSupp.RECTYPE, ContSupp.ADDRESS1, ContSupp.CITY, ContSupp.STATE, ContSupp.ZIP, ContSupp.MERGECODES
FROM ContSupp
WHERE contsupp.rectype = "C"

Contact 1 Addresses WHERE Contact 1 Address1 is not empty

SELECT Contact1.COMPANY, Contact1.CONTACT, Contact1.ADDRESS1, Contact1.CITY, Contact1.STATE, Contact1.ZIP
FROM Contact1
WHERE (((Contact1.ADDRESS1) Is Not Null))
ORDER BY Contact1.COMPANY;

Contact 1 Addresses WHERE Contact 1 Address1 is not empty with Mergecodes

SELECT Contact1.COMPANY, Contact1.CONTACT, Contact1.ADDRESS1, Contact1.CITY, Contact1.STATE, Contact1.ZIP, Contact1.MERGECODES
FROM Contact1
WHERE (((Contact1.ADDRESS1) Is Not Null) AND ((Contact1.MERGECODES)LIKE"001"))
ORDER BY Contact1.COMPANY;

All Contact 1 Addresses

SELECT Contact1.ACCOUNTNO, Contact1.COMPANY, Contact1.CONTACT, Contact1.ADDRESS1, Contact1.CITY, Contact1.STATE, Contact1.ZIP, Contact1.MERGECODES
FROM Contact1;

XJoin Queries

XJoinP-All Primary Contact Info

SELECT Accountno, Company, Contact, Lastname, Department, Title, Secr, Phone1, Phone2, Phone3, Fax, Ext1, Ext2, Ext3, Ext4, Address1, Address2, Address3, City, State, Zip, Country, Dear, Source, Key1, Key2, Key3, Key4, Key5, Mergecodes
FROM Contact1

XJoinS

SELECT Accountno, Rectype, Contsupref, Contact, Title, Dear, Phone, Ext, Fax, Address1, Address2, Address3, City, State, Zip, Country, Mergecodes
FROM ContSupp
WHERE Rectype LIKE 'C' OR Rectype LIKE 'P'

XJoinSE

SELECT Accountno, Rectype, Contsupref, Contact, Address2, Mergecodes
FROM ContSupp
WHERE rectype like 'P'

contact1

Contact1 is where the top half the GoldMine contact screen stores its data. The 4 “quadrants” of the top half of the GoldMine contact screen divide the data logically by:

1. Name and title infomation
2. Contact via information
3. Address information
4. Key contact information

Contact1 is also the primary contact table of the GoldMine database. Contact history, details, calendar, additional contacts, referrals, linked documents, opportunities and projects all relate back to a contact1 record. The accountno column is the primary key that relates contact1 to:

1. contact2 – user defined fields
2. conthist – contact history
3. cal – calendar items
4. contsupp – additional contacts, referrals, linked documents, email addresses, websites

contact2

Contact2 holds all userdefined fields plus 15 GoldMine system fields

conthist

Conthist holds all history records which are divided into the following different types

contsupp

I have heard long time GoldMine solution partners refer to the contsupp table as GoldMine’s kitchen sink. It

The RECTYPE value H can be linked to records with the RECTYPE value P. Assigning extended information settings to a profile (assigned to a tab, or extended fields used) creates an H record type to store the settings. The profile record stores a character string in the Phone field that matches the H record’s AccountNo field.