Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Nickel Super Contributor
Posts: 80
Registered: ‎07-11-2009
Accepted Solution

Create LAN Account group for Missing Activities

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?

Jerry Norman
Highlighted
Nickel Contributor
Posts: 46
Registered: ‎03-29-2009

Re: Create LAN Account group for Missing Activities

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

Nickel Super Contributor
Posts: 80
Registered: ‎07-11-2009

Re: Create LAN Account group for Missing Activities

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!

Jerry Norman
Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: Create LAN Account group for Missing Activities

Good solution, Jerry.

 

It's too bad we can't do a SQL replace and override the QueryBuilder - BOTH LAN and Web  ;-) 

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html