<

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

Published on
9,999 Points
3,799 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

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Join & Write a Comment

In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month