Follow our news:

Follow canonburysvcs on Twitter Follow Canonbury Services on Facebook Follow Canonbury Services' news by RSS Follow Canonbury Services' news by Atom Follow Canonbury Services' news by email


Distribution lists in GM

09/09/2016 – in GoldMine, SQL queries, Structure

Some things are easy to find in the GoldMine database while others are a bit more hidden; distribution lists fall into the latter category, but are simple enough once you know how.

They are held in the mailbox table and are identified by the folder name being equal to ‘X-GM-GROUPS’. The RFC822 field holds the actual list. Inside that field, the individual entries are delimited by ASCII character 1 (presumably chosen because that character won’t appear in any of the entries themselves).

The following query will extract them. I’ve replaced character 1 with ‘ — ‘ just to make the list more readable for us poor humans.

select userid, folder2 as [list name], replace(CONVERT(varchar(max), convert(varbinary(max), rfc822)), char(1), ' --- ') as [list members] from mailbox where folder='X-GM-GROUPS'

Each entry is preceded by a letter which tells you what sort of entry it is. These are:

C: Primary contact, referenced as a GoldMine account number (you would then need to do a separate lookup on contsupp to find the primary email address);

E: Manual email address (ie an actual address);

S: Additional contact, referenced as the recid of the email address in contsupp;

G: GoldMine user name for internal messaging