The GoldMine CRM system has a powerful synchronization capability sends changed data to remote sites. But what if you need to review changes on your local machine?
You can list changes to individual records using Sync Spy. On recent versions, go to the record you are interested in, then go to Tools > Synchronization > Sync Spy:
In older versions of GoldMine, this was under Tools > Sync Spy.
But what if you need to see all changes since a certain date?
SQL to the Rescue
I recently had this need and wrote a “quick-and-dirty” SQL script to list changes to the primary GoldMine tables since a certain date and time, based on the sync logs. The tables are Contact1, Contact2, ContSupp, ContHist, and Cal.
Note There are lots of other tables; for example, changes to Lookup will not be listed. Also, only adds and updates are listed; deletions are not shown.
Caution The changes identified seem accurate to me, but there’s no guarantee that the script catches all changes or lists them correctly. Use at your own risk!
Get the Cutoff LogStamp
The script uses the sync logs to identify changes. The sync logs store the date and time that the entry was created in a special format. Before you run the script, you must convert the date and time to this special format using GoldMine.
Example You want to list all changes since December 20, 2012 at 12:00am. In GoldMine, go to Tools > GoldSync Administration. Right-click anywhere in the list of Site Groups and Servers and select Date/Time Stamp Conversion…:
In the Date/Time Stamp Conversion dialog, enter the date and time and press Convert:
Here we see that 12/20/2012 12:00am converts to GNDI4G0. This is the value we need for the script.
Run the Script
Here is the script. Modify the set statement to use the converted value from above.
-- Find GoldMine changes since a certain date
declare @SinceLogStamp as varchar(7)
-- Convert the desired date/time cutoff using GoldMine.
-- In the GoldSync Administration Center, right-click and select Date/Time Stamp Conversion.
-- Fill in the converted value here:
set @SinceLogStamp = 'GNDI4G0' -- 12/20/2012 12:00am
-- Contact1
select C1.Company, C1.Contact, C1.LastDate, TL.LogStamp, TL.TableID, TL.FieldName, C1.AccountNo, C1.RecID
from Contact1 C1
inner join ContTLog TL
on C1.RecID = TL.FRecID
where TL.LogStamp >= @SinceLogStamp
and TL.FieldName not in ('LASTDATE','LASTTIME','LASTUSER','LASTCONTON','LASTCONTAT','PREVRESULT','MEETDATEON','MEETTIMEAT','NEXTACTION','ACTIONON','CALLBACKON')
order by C1.Company, C1.Contact, TL.LogStamp
-- Contact2
select C1.Company, C1.Contact, C1.LastDate, TL.LogStamp, TL.TableID, TL.FieldName, C1.AccountNo, C2.RecID
from Contact1 C1
inner join Contact2 C2
on C1.AccountNo = C2.AccountNo
inner join ContTLog TL
on C2.RecID = TL.FRecID
where TL.LogStamp >= @SinceLogStamp
and TL.TableID = '''' -- Contact2 TableID is a single quotation mark
and TL.FieldName not in ('LASTDATE','LASTTIME','LASTUSER','LASTCONTON','LASTCONTAT','PREVRESULT','MEETDATEON','MEETTIMEAT','NEXTACTION','ACTIONON','CALLBACKON')
order by C1.Company, C1.Contact, TL.LogStamp
-- ContSupp
select C1.Company, C1.Contact, CS.RecType, CS.Contact as ContSuppContact, CS.ContSupRef, C1.LastDate, TL.LogStamp, TL.TableID, TL.FieldName, C1.AccountNo, CS.RecID
from Contact1 C1
inner join ContSupp CS
on C1.AccountNo = CS.AccountNo
inner join ContTLog TL
on CS.RecID = TL.FRecID
where TL.LogStamp >= @SinceLogStamp
and TL.FieldName not in ('LASTDATE','LASTTIME')
order by C1.Company, C1.Contact, TL.LogStamp
-- ContHist
select C1.Company, C1.Contact, CH.RecType, CH.Ref, CH.OnDate, CH.OnTime, TL.LogStamp, TL.TableID, TL.FieldName, C1.AccountNo, CH.RecID
from Contact1 C1
inner join ContHist CH
on C1.AccountNo = CH.AccountNo
inner join ContTLog TL
on CH.RecID = TL.FRecID
where TL.LogStamp >= @SinceLogStamp
and TL.FieldName not in ('LASTDATE','LASTTIME')
order by C1.Company, C1.Contact, CH.OnDate, CH.OnTime, TL.LogStamp
-- Cal
select C1.Company, C1.Contact, Cal.Ref, Cal.OnDate, Cal.OnTime, Cal.AlarmDate, Cal.AlarmTime, TL.LogStamp, TL.TableID, TL.FieldName, C1.AccountNo, Cal.RecID
from Contact1 C1
inner join Cal
on C1.AccountNo = Cal.AccountNo
inner join GMTLog TL
on Cal.RecID = TL.FRecID
where TL.LogStamp >= @SinceLogStamp
and TL.FieldName not in ('LASTDATE','LASTTIME')
order by C1.Company, C1.Contact, TL.LogStamp
Now run the script from SQL Management Studio to list changes. Note that several system-generated changes (e.g. LASTDATE, LASTTIME) are intentionally excluded.
If you use the script, and especially if you improve it, post a comment!