Building Contact Groups from SQL Queries in GoldMine
Time and time again we need to perform data changes, deletions, copying/moving, merging/purging, or some other action against a specific "group" of contact records. To work against a "group" of contacts makes the process easier. While there are different methods for building a contact group in GoldMine, this article discusses building a contact group using a SQL query. Why use SQL? Building your specific group of contact records can be a tricky endeavor. Suppose for example that you need to update a "follow-up" field on your contact records for those you have not had contact with (based on history records) within a specific date range and these same records must have no PHONE1 value. This is not easily done without using a SQL query to locate these records and build a contact group from. There is power here and building strong SQL queries can help you pinpoint specific contact records to work with.
This blog article is not intended to teach about writing SQL queries but to show how a SQL query is used to create a contact group in GoldMine. To learn about writing SQL queries, please visit these two blog articles to get you started (also note that the internet is "full" of SQL tutorials too). While the following articles were written for using SQL queries in our wMobile product, the "SQL query writing" content is applicable here for GoldMine too:
Building a Contact Group in GoldMine
To stay on topic, please note that we are building a contact group based on a SQL query. To do this, we start with the SQL Query tab in GoldMine which is available from the menu using, Tools > SQL Query:
NOTE: Not all users are granted access to this "SQL Query" tab for security reasons. If you do not see this tab, you can check with your GoldMine administrator.
In the following example, the SQL query will return records based on these criteria:
- primary contacts are in NJ and NY and...
- primary contacts have "Customer" in the "Contact Type" field (i.e., KEY1/U_KEY1 in the database)
The SQL query statement:
select contact, city, state, zip, u_key1 [Contact Type] from contact1 where (state = 'NJ' or state = 'NY') and u_key1 = 'Customer'
Write and test your SQL query on the SQL Query tab. After entering the SQL query, click the Query button to return the results. The results will be displayed below the query window as shown (please note this query returned 5 records):
Review the results and if needed, adjust your SQL query statement and then run the updated SQL query to get the desired results before building the contact group. Once you are sure that the records being returned by the SQL query meet your needs for the contact group you are building, you are ready to move onto the next step. Be sure to leave the SQL Query tab "as-is" after running the SQL query for the next steps.
- Click on the Groups tab.
- Click the New Group button to open the New Group dialog.
- Enter a descriptive name for your contact group (we'll use "NJ/NY Customers" in this example).
You will be presented with the Group Building Wizard dialog:
- Click the SQL Query records option (this option is only available when there are SQL query results present).
Now you will be presented with the Optional Settings dialog. The settings in this dialog are not required but can be used to sort the data within the contact group, display another field's values for reference, and allow limiting the records searched. In our example, we will only sort by "Company" and add the contact record's "Office" (CONTACT1.PHONE1) phone number for reference in the contact group results.
- Enter the Sort field value.
- Enter the Reference field expression.
The GoldMine Process Monitor will display the processing as the group is built and then the results will display on the Groups tab for the selected group. In this screenshot, you'll see there are 5 members representing the 5 records the SQL query returned in a prior step (above) and these were added to the contact group we named "NJ/NY Customers". Also note the records are sorted by company and the phone number is present for reference:
Contact Group Usage Example
Once you have your contact group created, it can be used for many purposes in GoldMine. As one example, if a Global Replace was used to replace a field with a specific value for the contacts within this "NJ/NY Customers" contact group, select that contact group so only those records are updated:
Knowing how to write SQL queries and coupling that knowledge to build contact groups using the results of your SQL queries adds a lot of power in GoldMine when working with specific sets of contacts easily and effectively. This has been my "go-to" method for many years, am happy to have this functionality available.