07-22-2011 11:01 AM
I don't understand what I'm missing here. LAN v7.5.3.
We want to create an Account group showing those accounts without activities scheduled. I thought that the same approach we use in groups showing no history in the past xx days would work, but it doesn't.
The group uses a "Literal" line in it to produce this line in the resulting sql:
SELECT A1.ACCOUNTID, A1.ACCOUNT, A1.MAINPHONE, A1.TYPE, A1.SUBTYPE, A1.STATUS, A1.ACCOUNTMANAGERID, A1.ACCOUNT_UC
FROM ACCOUNT A1 LEFT OUTER JOIN ACTIVITY A2 ON (A1.ACCOUNTID=A2.ACCOUNTID) WHERE NOT (A2.ACCOUNTID IN (select ac.Accountid from activity ac))
I simply cannot get this to work. What am I missing?
Solved! Go to Solution.
07-22-2011 02:17 PM
We do something like this in Knowledge Sync. Basically write the SQL expression to count the number of history records within the date range you are looking for and set it = 0
07-22-2011 04:34 PM
I found the solution.
Create the Group Builder condition as
account.activity.activityid does not contain data
I have no idea why this works, but the "Not in" subselect does not.
In sql, a subselect using Not Exists also works, but you can't do that in Group Builder.
If anybody can shed light on the principal I'm missing, I'd appreciate it.
Hope this helps somebody else, too!
07-22-2011 06:03 PM