Goldmine - ALL Primary/Secondary Contacts with email addresses and web

[wsilage]

I am trying to pull all the information I can from goldmine. I was able to get all the contacts (primary/secondary) into a table. The problem I am having is I can not match the email and the website. I know in contsupp table for rectype we use P and either it equals to Email or web. I was able to figure that out. The problem that I am having is, if I have one hospital and there are a couple contacts under that. The email addresses get assinged to every contact person, so I am getting duplicates. how do get this information with out connecting other email address to the contact person or secondary contact person.

Here is my store procedure, I hope someone can give me some direction.



Select Distinct
CONTACT1."RecID",
CONTSUPPte."CONTSUPREF" AS "EMAIL",
CONTSUPPtw."CONTSUPREF" AS "WEB",
CONTACT1.ACCOUNTNO,
CONTACT1.COMPANY,
CONTACT1.CONTACT,
CONTACT1.DEPARTMENT,
CONTACT1.LASTNAME,
CONTACT1.TITLE,
CONTACT1.SOURCE,
CONTACT1.ADDRESS1,
CONTACT1.ADDRESS2,
CONTACT1.ADDRESS3,
CONTACT1.CITY,
CONTACT1.STATE,
CONTACT1.ZIP,
CONTACT1.COUNTRY as COUNTY,
CONTACT1.PHONE1,
CONTACT1.PHONE2,
CONTACT1.PHONE3 as [Prop Fee],
CONTACT1.FAX,
CONTACT1.KEY1 as [DHS REP],
CONTSUPPtc."CONTACT" AS [Secondary Contact],
CONTSUPPtc."TITLE" AS [Secondary Title],
CONTSUPPtc."PHONE" AS [Secondary Phone],
CONTSUPPtc."EXT" AS [Secondary Ext],
CONTSUPPtc."FAX" AS [Secondary FAX],
CONTSUPPtc."ADDRESS1" AS [Secondary Address1],
CONTSUPPtc."ADDRESS2" AS [Secondary Address2],
CONTSUPPtc."ADDRESS3" AS [Secondary Address3],
CONTSUPPtc."CITY" AS [Secondary City],
CONTSUPPtc."STATE" AS [Secondary State],
CONTSUPPtc."ZIP" AS [Secondary ZIP],
CONTACT1.KEY2 as SPECIALTY,
CONTACT1.KEY3 as [Number of Docs],
CONTACT1.KEY4 as [Org Type],
CONTACT1.KEY5 as [TAX ID]


FROM
GoldMine.dbo.CONTACT1 CONTACT1

LEFT OUTER JOIN goldmine.dbo.CONTSUPP CONTSUPPte ON
CONTACT1."ACCOUNTNO" = CONTSUPPte."ACCOUNTNO" and CONTSUPPte."RECTYPE" IN('P') and CONTSUPPte."CONTACT" = 'E-mail Address'

LEFT OUTER JOIN goldmine.dbo.CONTSUPP CONTSUPPtw ON
CONTACT1."ACCOUNTNO" = CONTSUPPtw."ACCOUNTNO" and CONTSUPPtw."RECTYPE" IN ('P') and CONTSUPPtw."CONTACT" = 'Web Site'

left JOIN goldmine.dbo.CONTSUPP CONTSUPPtc ON
CONTACT1."ACCOUNTNO" = CONTSUPPtc."ACCOUNTNO" and CONTSUPPtw."RECTYPE" IN ('C')

 

[DJHunt]

You don't tell us the backend for your GoldMine, however, against a SQL backend you could try modifying this query:

select AccountNo,
Company,
Contact,
Address1,
Address2,
City,
State,
Zip,
Phone1,
(select top 1 contsupref+address1
from contsupp as cs
where cs.accountno = c1.accountno
and contact = 'E-MAIL ADDRESS'
and zip like '_1__'
) as Email
from contact1 as c1
Union select ContSupp.AccountNo,
ContSupp.Contact as Company,
ContSupp.Contact,
ContSupp.Address1,
ContSupp.Address2,
ContSupp.City,
ContSupp.State,
ContSupp.Zip,
ContSupp.Phone,
CS2.ContSupRef+CS2.Address1 AS Email
FROM Contact1, ContSupp, ContSupp CS2
WHERE Contact1.AccountNo=ContSupp.Accountno
and ContSupp.RecID = CS2.LinkAcct
AND ContSupp.RecType='C'
order by Company

DJ Hunt
Phone: (978)2-3333
WebSite:

http://www.DJHunt.US

- The Hacker's Guide to GoldMine Premium
- One-on-One GoldMine Technical Support ( Fee Based )

http://www.DJHunt.US/Default.html

 

[wsilage]

This works great Dj, the only thing that is not pulling are the ones that are underneath the contact tab that do not have email addresses, How would I work that into this query?

 

[DJHunt]

As I stated: You could try modifying the above query to meet your needs.

If I had known the exact answer to your question, I would have given you the exact answer. You might have to do an independent query for those without an e-mail address.

DJ Hunt
Phone: (978)2-3333
WebSite:

http://www.DJHunt.US

- The Hacker's Guide to GoldMine Premium
- One-on-One GoldMine Technical Support ( Fee Based )

http://www.DJHunt.US/Default.html