Community
Showing results for 
Search instead for 
Do you mean 
Reply

SQL statement tweak

Copper Super Contributor
Posts: 122
Country: United Kingdom

SQL statement tweak

Trying to run this SQL statement against Act Database - just want to get a list of all the contacts in a given group - this doesn't seem to work - any ideas?

Thanks

Andy

SELECT CONTACT.FIRSTNAME,
CONTACT.LASTNAME,
CONTACT.SALUTATION,
CONTACT.COMPANYNAME,
CONTACT.USER9 AS [URN],
CONTACT.USER2 AS [DCSF],
CONTACT.JOBTITLE,
ADDR.LINE1 AS [ADDRESS 1],
ADDR.LINE2 AS [ADDRESS 2],
ADDR.CITY AS [CITY],
ADDR.STATE AS [COUNTY],
ADDR.COUNTRYNAME AS [COUNTRY],
PHO.NUMBERDISPLAY AS [PHONE],
EMAIL.ADDRESS AS [EMAIL]
FROM TBL_GROUP GRP
INNER JOIN TBL_GROUP_CONTACT MID
ON GRP.GROUPID = MID.GROUPID
INNER JOIN TBL_CONTACT CONTACT
ON MID.CONTACTID = CONTACT.CONTACTID
LEFT JOIN TBL_ADDRESS ADDR
ON CONTACT.CONTACTID = ADDR.CONTACTID AND ADDR.TYPEID = '31bd07d5-4cb7-4038-9505-d2347aa530a1'
LEFT JOIN TBL_PHONE PHO
ON CONTACT.CONTACTID = PHO.CONTACTID AND PHO.TYPEID = 'c3ae9586-6158-4d0d-9faf-253cb0152f18'
LEFT JOIN TBL_EMAIL EMAIL
ON CONTACT.CONTACTID = EMAIL.CONTACTID AND EMAIL.TYPEID = '9ce4ff61-f0b5-439f-81cc-857138422ec7'
WHERE GRP.NAME = 'Name of Group'

Andy Kent
Angel Solutions (UK and NZ)
www.angelsolutions.co.uk
Nickel Elite Contributor
Posts: 508
Country: USA

Re: SQL statement tweak

I changed the SELECT just a little bit:

 

EMAIL.ADDRESS AS [EMAIL],

GRP.NAME as [GNAME]

 

Added GRP.NAME as part of the SELECT, and commented out the WHERE clause, and noticed that I did *not* get all the groups returned.  Only two, in fact.  Then, I added the WHERE back in, *with* one of the known groups:

 

WHERE GRP.NAME = 'GMail Contacts'

 

and it worked fine.  I guess a check on the actual group names might help.  Good luck...

Richard Brust
ACT! Certified Consultant
richard@rbrDataSolutions.com
Copper Super Contributor
Posts: 122
Country: United Kingdom

Re: SQL statement tweak

Select tbl_compliments.compliment as Compliment where compliment = "the best compliment and thank you I can make"

Geeky response I know and bad SQL too!

Thanks heaps

Andy
Andy Kent
Angel Solutions (UK and NZ)
www.angelsolutions.co.uk
Copper Super Contributor
Posts: 122
Country: United Kingdom

Re: SQL statement tweak - CAVEAT!

A caveat!!

I notice the SQL only works when it is NOT a dynamic group

If it's a dynamic group then this doesn't work? Any comments?

Thanks

Andy
Andy Kent
Angel Solutions (UK and NZ)
www.angelsolutions.co.uk
Nickel Elite Contributor
Posts: 508
Country: USA

Re: SQL statement tweak - CAVEAT!

ACT! does not store dynamic group members - hence the name "dynamic".  You would have to add/join the group name with the dynamic group query as a sub-query to return those results.  That's why my example only returned two groups, I only have two that are static.
Richard Brust
ACT! Certified Consultant
richard@rbrDataSolutions.com
Nickel Elite Contributor
Posts: 508
Country: USA

Re: SQL statement tweak - CAVEAT!

FYI - TBL_GROUP also holds the query string - this is probably fairly easy to implement - the group name and query are all right there.  Good luck...
Richard Brust
ACT! Certified Consultant
richard@rbrDataSolutions.com
Copper Super Contributor
Posts: 122
Country: United Kingdom

Re: SQL statement tweak - CAVEAT!

mmm - currently beyong my knowledge of SQL ! Any clever people out there?!
Andy Kent
Angel Solutions (UK and NZ)
www.angelsolutions.co.uk
Copper Super Contributor
Posts: 122
Country: United Kingdom

Re: SQL statement tweak - CAVEAT!

one other obstacle related to this is when referencing CUSTOM fields in the Act Database

The SQL works FINE in MS SQL Server Management Studio - however an error occurs using our mailmerge program - the original SQL runs fine

Are there any tips/tricks to referencing custom fields?

Snippet from SQL below with the custom fields

CUST_User12_012904234 AS [PHASE],
CUST_PSPv2_101124812 AS [PSPv2],
CUST_PSPv3_101210000 AS [PSPv2],
CUST_Exclude_125012796 AS [Exclude],
CUST_BadEmail_012135068 AS [BadEmail],

Thanks

Andy
Andy Kent
Angel Solutions (UK and NZ)
www.angelsolutions.co.uk