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!
|
|
Look Up Groups With Lasthistorydate
Posted: 04 Nov 08 3:29 PM
|
I am not sure where this fits so I will just throw it out.
I am trying to create a lookup group for accounts that displays the last date of history/notes posted on the account. My initial attempts use the Contact/Lasthistorydate in my layout.
This is proving to be inaccurate about 80% of the time. I am not sure why. Does anyone else use groups for accounts that include information on last note/history date information? I am trying to identify accounts that have not been touched by sales people in say, the last 6 months. |
|
|
|
Re: Look Up Groups With Lasthistorydate
Posted: 04 Nov 08 4:22 PM
|
Why not use CreateDate on the HISTORY table? Something like
Select Max(CreateDate) from History
where Type <> 262156 and AccountID = 'AAAAAAAAAAAA'
(Type 262156 is a database change - I'm thinking that you don't want these included.)
Phil |
|
|
|
Re: Look Up Groups With Lasthistorydate
Posted: 05 Nov 08 9:30 AM
|
I tried using the History.completeddate, but with a range, say, not within the last 180 days, but the problem is that it shows ALL history that does not match that, not accounts with the LAST history item out of that range.
Let me try your example |
|
|
|
Re: Look Up Groups With Lasthistorydate
Posted: 05 Nov 08 4:58 PM
|
Now I know what you want - here's some SQL:
select a.* from account a where a.AccountID in
(select h1.AccountID from history h1 group by h1.AccountID having max(h1.CreateDate) < '2008-01-01' )
You should be able to build a group along this lines. |
|
|
|
Re: Look Up Groups With Lasthistorydate
Posted: 07 Nov 08 9:05 AM
|
| Thanks Phil. I would like to try that out, but I see that in query builder you can not edit the SQL directly. Is there another place to do this? |
|
|
|
Re: Look Up Groups With Lasthistorydate
Posted: 07 Nov 08 1:55 PM
|
| I am thinking you are referring to SQL Query Analyzer now. Is that right? That is not really what I need as I need to publish a group tab for my users to access this information. Am I asking for too much? |
|
|
|
Re: Look Up Groups With Lasthistorydate
Posted: 07 Nov 08 7:48 PM
|
In Query Builder, you can type in pure SQL if you use 'IN' as the selection operator - which is why I wrote my SQL the way I did 
Phil |
|
|
|
Re: Look Up Groups With Lasthistorydate
Posted: 14 Nov 08 10:04 AM
|
Bear with me, but there is not an operator on my query builder for "IN". I have equal to, not equal to, less than, greater than, etc.
I will try doing this under the admin account. |
|
|
|
Re: Look Up Groups With Lasthistorydate
Posted: 16 Nov 08 3:40 PM
|
What version of SLX are you using?
I have the options you mention, but then several more, including:
in
does not contain data
does contain data
Phil |
|
|
| |
|
Re: Look Up Groups With Lasthistorydate
Posted: 17 Nov 08 4:40 PM
|
That's true, but if you read my later post, you'll see it's using AccountID.
Anyhow, I just built this damn group on my SLX client 
Here is what appears when I click on the 'View SQL' button after building it (carriage returns added for ease of viewing):
SELECT A1.ACCOUNTID, A1.ACCOUNT, A2.CITY A2_CITY, A2.STATE A2_STATE, A1.MAINPHONE, A1.TYPE, A1.SUBTYPE,
A1.STATUS, A1.ACCOUNTMANAGERID, A1.SECCODEID, A1.ACCOUNT_UC
FROM ACCOUNT A1
INNER JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID)
WHERE (A1.ACCOUNTID IN (select h1.AccountID from history h1
group by h1.AccountID having max(h1.CreateDate) < '2008-01-01'))
ORDER BY A1.ACCOUNT_UC ASC
Phil |
|
|
|