GoldMine – Check for Duplicate Email Addresses

When reviewing the health of your data in general or when doing an email merge, it can be helpful to check for duplicate email addresses.

Duplicate addresses in GoldMine can cause several issues with your data.  Items can be linked to the incorrect contact or not at all until you choose the correct one.  Duplicate messages will be created when doing mail merges or syncing with Constant Contact.

The query below will provide a list of any email addresses that are in GoldMine more than once.

To run the query:

1. Open GoldMine and navigate to Tools -> Filters and select the SQL Query

2. Copy and paste the query below:

SELECT contact1.company,contact1.contact,cs.accountno,contsupref+cs.address1,cs.address2,cs.zip
FROM contsupp cs,contact1
WHERE   (cs.CONTACT = ‘E-mail Address’) and contact1.accountno=cs.accountno
AND (SELECT
CASE
WHEN cs.contsupref IS NULL
OR CHARINDEX(‘@.’,cs.contsupref+cs.address1) > 0
OR CHARINDEX(‘.@’,cs.contsupref+cs.address1) > 0
OR CHARINDEX(‘..’,cs.contsupref+cs.address1) > 0
OR CHARINDEX(‘”‘, cs.contsupref+cs.address1) <> 0
OR CHARINDEX(‘(‘, cs.contsupref+cs.address1) <> 0
OR CHARINDEX(‘)’, cs.contsupref+cs.address1) <> 0
OR CHARINDEX(‘,’, cs.contsupref+cs.address1) <> 0
OR CHARINDEX(‘<‘, cs.contsupref+cs.address1) <> 0
OR CHARINDEX(‘>’, cs.contsupref+cs.address1) <> 0
OR CHARINDEX(‘;’, cs.contsupref+cs.address1) <> 0
OR CHARINDEX(‘:’, cs.contsupref+cs.address1) <> 0
OR CHARINDEX(‘[‘, cs.contsupref+cs.address1) <> 0
OR CHARINDEX(‘]’, cs.contsupref+cs.address1) <> 0
OR RIGHT(RTRIM(cs.contsupref+cs.address1),1) = ‘.’
OR CHARINDEX(‘ ‘,LTRIM(RTRIM(cs.contsupref+cs.address1))) > 0
OR LEN(cs.contsupref+cs.address1)-1 <= CHARINDEX(‘.’, cs.contsupref+cs.address1)
OR cs.contsupref+cs.address1 LIKE ‘%@%@%’
OR cs.contsupref+cs.address1 LIKE ‘% %’
OR cs.contsupref+cs.address1 NOT LIKE ‘%@%.%’ THEN 0
ELSE 1
END) = 0

3. Click the Query button and any duplicate email address(es) will display in the result list. If any are found, navigate to the record or records and correct it.

To Save the query:

1. Click on the Save button in the SQL Query Window

2. Give the query a name
3. Click OK

4. The query can now be selected from the drop down list under the user it is stored under.