Monday, November 29, 2010

GoldMine : Using Custom Fields in the SQL Query Tool

A lot of clients I know like to use the SQL Query tool in GoldMine to generate speadsheets (via the Output To... command). But most folks don't know how to add their custom fields (which live in the CONTACT2 table).

Let us consider a query to return Company, Contact and Phone1:

SELECT COMPANY, CONTACT, PHONE1 FROM CONTACT1

Let us now say we want to add a userdefined field you've added to GoldMine, called USALESREP.

SELECT COMPANY, CONTACT, PHONE1, USALESREP FROM CONTACT1
INNER JOIN CONTACT2 ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO

Once we add USALESREP to our SELECT list, we must provide the JOIN to CONTACT2 below.

You'll recall that all GoldMine tables are related with the ACCOUNTNO field.

You could also get away with this;

SELECT COMPANY, CONTACT, PHONE1, USALESREP FROM CONTACT1
WHERE CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO

But the JOIN is the "proper" way to construct the query, and should be used whenever possible.

Now that USALESREP is available in our query, we can use it in the WHERE clause thusly;

SELECT COMPANY, CONTACT, PHONE1, USALESREP FROM CONTACT1
INNER JOIN CONTACT2 ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO
WHERE USALESREP = 'JUSTIN'

Remember to always use single quotes when working in the SQL Query tool.

No comments: