<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

How to extract the Distribution list Email addresses from GoldMine CRM 8.x/9.x. and above

Published on
10,085 Points
3,885 Views
2 Endorsements
Last Modified:
Approved
GoldMine CRM has a fairly open database architecture , however in this case the email distribution list is stored in a quirky way and does not allow easy access for external use.

This article will give details of this and provide a method to extract any distribution list by name easily.
GoldMine CRM Distribution list retrieval.

Have you ever needed to pull a list of the email addresses used in a GoldMine distribution list?

Here I will detail how to achieve this within SQL. If you are unfamiliar with SQL  Stored Proceedures and Functions I am sure there are plenty of articles here that can assist.

GoldMine stores the distribution list as a record in the MAILBOX table with the Distribution list name stored in the field called "FOLDER2" and the field called "FOLDER" has the value of "X-GM-GROUPS"

So if you perform a select of this record you will get something that is not usable in its current form.

Unfortunately the records are recorded in the RFC822 field as a CHAR(1) seperated list, each record has a preeceeding letter to denote what sort of record it is detailed there.

C - ACCOUNTNO of the Contact and this will use the Primary email address of that contact
S - RECID of the CONTSUPP record that holds the Email address selected.
E - Email address as entered literally.
G - GoldMine username.

Now this is all well and good but you cannot use this data or export it with a list of email addresses. What you need to do is process this list and extract the email address based on the first characters.

I have built an SQL Function you can use in your GoldMine SQL database and will enable you to use this from within Goldmine's SQL window or SQL Managment Studio directly.

You will need to copy and paste the following code into a new query window against your GoldMine database.

Open SQL Managment Studio, Select "New Query" and choose the database from the drop down selection. Alternatively you can preceed the below code with "USE yourDBDName" and GO below e.g.

 
Use GoldMine
GO

Open in new window


Here is the Function:-

 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Darren Lawrence
-- Create date:           07/12/2012
-- Description:	Return a list of Email addresses for a distribution list
-- =============================================
CREATE FUNCTION GetDistList ( @DISTNAME VARCHAR(20) )
RETURNS @temptable TABLE
    (
      RecordItem VARCHAR(50) ,
      EmailAddress VARCHAR(1000)
    )
AS 
    BEGIN       
        DECLARE @idx INT       
        DECLARE @item VARCHAR(MAX) 
        DECLARE @content VARCHAR(MAX)  
        DECLARE @Delimiter CHAR(1)
        DECLARE @firstchar VARCHAR(1)
        DECLARE @email VARCHAR(200)

    
        SET @Delimiter = CHAR(1)  
      
        SELECT
            @idx = 1       

		-- Fetch the Value of the RFC822 Field for this list	
        SELECT
            @content = ( SELECT
                            CAST(CAST(RFC822 AS VARBINARY(MAX)) AS VARCHAR(MAX))
                         FROM
                            dbo.MAILBOX
                         WHERE
                            FOLDER = 'X-GM-GROUPS'
                            AND folder2 = @DISTNAME
                       )
      
        WHILE @idx != 0 
            BEGIN       
                SET @idx = CHARINDEX(@Delimiter, @content)       
                IF @idx != 0 
                    SET @item = LEFT(@content, @idx - 1)       
                ELSE 
                    SET @item = @content       
          
                IF ( LEN(@item) > 0 ) 
                    BEGIN 
                    
                        SET @firstchar = SUBSTRING(@item, 1, 1)
                        SET @email = ''
                        IF @firstchar = 'C' 
                            BEGIN
								-- Contact - get primary ContSupp record 
                                SELECT
                                    @email = ( SELECT
                                                CONTSUPREF + ISNULL(ADDRESS1,
                                                              '')
                                               FROM
                                                dbo.CONTSUPP
                                               WHERE
                                                ACCOUNTNO = SUBSTRING(@item, 2,
                                                              LEN(@item))
                                                AND RECTYPE = 'P'
                                                AND CONTACT = 'E-Mail address'
                                                AND zip LIKE '_1%'
                                             )
                            END
                        IF @firstchar = 'E'
                            OR @firstchar = 'G' 
                            BEGIN
								-- Literal Email Address
                                SET @email = SUBSTRING(@item, 2, LEN(@item))
                            END
                        IF @firstchar = 'S' 
                            BEGIN
								-- ContSupp Email Record - RECID
                                SELECT
                                    @email = ( SELECT
                                                CONTSUPREF + ISNULL(ADDRESS1,
                                                              '')
                                               FROM
                                                dbo.CONTSUPP
                                               WHERE
                                                RECID = SUBSTRING(@item, 2,
                                                              LEN(@item))
                                             )
                            END
                       
                        INSERT  INTO @temptable
                                ( RecordItem, EmailAddress )
                        VALUES
                                ( @item, @email )       
                    END
                SET @content = RIGHT(@content, LEN(@content) - @idx)       
                IF LEN(@content) = 0 
                    BREAK       
            END 
        RETURN       
    END  

Open in new window


This function is so simple to use and will return 2 columns per row, 1 row per email address
 
SELECT * FROM dbo.GetDistList('Distribution list name')

Open in new window

You will be returned rows of records with RecordItem and EmailAddress - RecordItem is the item as it was stored in the GoldMine distribution record and the EmailAddress will give you the email address based on the rules mentioned above.

I hope this is useful for others and please feel free to ask me any questions about this.
2
Comment
Author:GMGenius
1 Comment

Expert Comment

by:visionquestit
Great script. Thank you.

Allan
0

Featured Post

Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Join & Write a Comment

Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month