7/8/2025 9:33:47 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.
|
|
|
|
Group of contacts that have not been contacted in the last 90 days
Posted: 08 Feb 10 8:12 AM
|
Hi all.
We have SLX LAN v7.2.1 and we would like to create a contacts group that shows contacts that have not been contacted (i.e. no phone call, email or note) within the past 90 days.
I tried using the query builder but I don't see how I can get this to work.
Any ideas?
Thank you in advance. |
|
|
|
Re: Group of contacts that have not been contacted in the last 90 days
Posted: 08 Feb 10 10:05 AM
|
This is harder than you would expect.
The trick is to find a way to use the IN operator in the group definition - because only then can you write some freehand SQL, which is what you need to solve this problem.
First you need a query which will return a list of all ContactIDs which have no HISTORY record with a completed date in the last three months (database changes excluded). Use a correlated subquery:
select C.ContactID from Contact C where not Exists (
select Distinct H.ContactID from history H
where H.CompletedDate > DateAdd(m, -3, getutcdate()) and H.ContactID = C.ContactID and H.Type <> 262156
)
Now create yourself a Contact group. In the conditions bit, add Contact.ContactID and set the operator to IN, and paste the above query in.
|
|
|
|
Re: Group of contacts that have not been contacted in the last 90 days
Posted: 08 Feb 10 12:40 PM
|
Thanks for the reply Phil.
I opened query builder and selected Contact.ContactID and set the operator to IN and pasted the following in the "Value is:" box:
select A1.ContactID from Contact A1 where not Exists (select Distinct H.ContactID from history Hwhere H.CompletedDate > DateAdd(m, -3, getutcdate()) and H.ContactID = A1.ContactID and H.Type <> 262156)
But when I click OK to see the results nothing displays. Here is what appears in the SQL when I click the "View SQL" button:
SELECT A1.CONTACTID, A1.FIRSTNAME, A1.LASTNAME, A1.ACCOUNT, A2.CITY A2_CITY, A2.STATE A2_STATE, A1.WORKPHONE, A1.MOBILE, A1.EMAIL FROM CONTACT A1 INNER JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID) WHERE (A1.CONTACTID IN (select A1.ContactID from Contact A1 where not Exists (select Distinct H.ContactID from history H where H.CompletedDate > DateAdd(m, -3, getutcdate()) and H.ContactID = A1.ContactID and H.Type <> 262156)))
|
|
|
| |
|
Re: Group of contacts that have not been contacted in the last 90 days
Posted: 08 Feb 10 12:55 PM
|
Still no results. Here's the SQL code:
SELECT A1.CONTACTID, A1.FIRSTNAME, A1.LASTNAME, A1.ACCOUNT FROM CONTACT A1 WHERE (A1.CONTACTID IN (select C.ContactID from Contact C where not Exists (select Distinct H.ContactID from history H where H.CompletedDate > DateAdd(m, -3, getutcdate()) and H.ContactID = C.ContactID and H.Type <> 262156)))
|
|
|
| |
| |
| |
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|