Once you start using GoldMine Automated Processes, no doubt you will run into situations where you realize you have a problem with your process, or for some reason you need to identify all the records in your database with certain Automated Processes. Here’s what you need to know to do that.
To begin with, you’ll want to be aware of where and how GoldMine stores information about Automated Processes in the GoldMine Record. Information on Automated Processes attached to records in GoldMine is stored in the “CONSUPP” Table under the RecType “E.” Here is how the ConSupp Fields are used:
|Fieldname||Used For (Data Stored In)|
|ACCOUNTNO||AccountNo of the Linked Record|
|CONTACT||Date/Time AP was attached. In format: YYYYMMDDHH:MM:SS|
|CONSUPREF||Next event to be triggered. Duplicate of Tracks.Name|
|PHONE||Duplicate of Tracks.option1.|
|EXT||Next Event Number. Duplicate of Tracks.EventNo.|
|ADDRESS1||Process name. Duplicate of Tracks.Name for header record of AP Track|
|CITY||Text description of the next action type. Based on TracksActnType.|
|ZIP||Username that assigned the track|
|COUNTRY||Number of days until event is triggered. Based on Tracks.NextActn|
*All field values are set by the “system” as users manipulate the chart.
GoldMine doesn’t give you an obvious way to find all the records in your database that have tracks assigned to them. But, since this information is in the SQL tables in GoldMine you can use SQL queries to locate records with Automated Processes assigned to them. The following query will return the records and the name of the Automated Process Track that is linked to it.
Copy the text below and paste it into GoldMine’s SQL Query* building window. SELECT contact1.accountno, contact1.company, contsupp.address1 from CONTACT1 Inner join contsupp on contsupp.accountno=contact1.accountno where contact1.accountno in (SELECT contsupp.accountno from CONTSUPP where contsupp.rectype = ‘E’) and contsupp.rectype =’E’
Automated Processes are a phenomenal feature in GoldMine. Unfortunately they are not very easy to get started with. If you’d like to, please contact me at First Direct Corp. We’d love to assist you and show you how! Check out our Automated Processes Workshop.
* About GoldMine SQL Queries – In the Filters and Groups dialog box, on the SQL Query Tab, you can build and run SQL Queries of your GoldMine database. The SQL Query options are designed and optimized for systems running a SQL database, and enable you to search all the tables within the GoldMine database. The Query Builder feature helps you to write a query. However, queries can be written freehand or prewritten queries can be pasted into the window and run. Queries may be saved and re-run. Results of a GoldMine SQL query can be output Excel, Word, or your Clipboard. And, the identified records from the query can be added to a GoldMine Group.
To learn more about SQL queries and for a list of over 60 sample queries I recommend “The GoldMine Guide to SQL Queries”