How to extract the Distribution list Email addresses from GoldMine CRM 8.x/9.x. and above
Published:
Browse All Articles > How to extract the Distribution list Email addresses from GoldMine CRM 8.x/9.x. and above
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.
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.
Here is the Function:-
This function is so simple to use and will return 2 columns per row, 1 row per email address
I hope this is useful for others and please feel free to ask me any questions about this.
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
GOHere 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 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')I hope this is useful for others and please feel free to ask me any questions about this.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented:
Allan