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!
|
|
Dynamic Dates within groups
Posted: 20 Mar 07 6:53 AM
|
fiogf49gjkf0d Hi folks,
Was looking for a way to use a dynamic date for example all accounts created within 30 days from the present date. The only way I've been able to do it so far is by using now function specific to databases such as MSSQL's getdate() or Oracle's sysdate and this is no good as we use both so if someone decided to release the group it would all go terribly wrong.
I'm sure saleslogix have their own custom functions but I've never had to use them before.
Can someone please help. All help appreciated.
Cheers
Kevin |
|
|
|
Re: Dynamic Dates within groups
Posted: 20 Mar 07 8:16 AM
|
fiogf49gjkf0d DISCLAIMER: These are undocumented features that have now been listed due to popular demand. These are not supported by Tech Support and are subject to change at any time, without notice.
- Literals used in both clients (using "=" or "starting with" as the operator) -
:now
:time
:date
:BindID
:AdHocGroupID
:UserID
:OwnerID
- Literals ONLY in the Support Client (using "in" as the operator) -
:accountownerlist
:pluginownerlist
:ticketassignlist
:defectownerlist
:defectassignlist
Timmus |
|
|
|
Re: Dynamic Dates within groups
Posted: 21 Mar 07 12:17 AM
|
fiogf49gjkf0d You can create a group of accounts with condition on createdate and selecting operator as "within last XXX days". You can mention no. of days in value operator. It should solve your purpose. This group would always list accounts created with last XXX days.
Thanks and regards
Sandeep |
|
|
|
Re: Dynamic Dates within groups
Posted: 21 Mar 07 4:12 AM
|
fiogf49gjkf0d Thanks for your replies both but unfortunately this wont work in my situation  I'm looking to create a list of accounts that haven't been contacted within the last 30 days. The last xxx days operator doesnt work because of the way the joins work (e.g. if they had history 40 days ago its effectively not within the last 30 days so it will bring the account back).
Thanks for your comments though, appreciated. |
|
|
|
Re: Dynamic Dates within groups
Posted: 21 Mar 07 7:43 AM
|
fiogf49gjkf0d This is a bit of a hack, but it does work.
In Query Builder, I have successfully placed _extensive_ SQL statements in the (seemingly tiny) parameter input box using the "IN" operator, e.g.
Field Name: Account.AccountId
Operator: in
Value is: '(SELECT A1.ACCOUNTID FROM HISTORY A1 WHERE MAX(A1.CREATEDATE) > DATEADD(dd, -30, GetDate()))'
You might have to tinker with the single quotes, because SLX likes to delete them when you edit the field. |
|
|
|
Re: Dynamic Dates within groups
Posted: 23 Mar 07 4:26 AM
|
fiogf49gjkf0d Cheers for this, looks promising, I shall give it a blast as soon as I get five minutes  |
|
|
|
Re: Dynamic Dates within groups
Posted: 24 Mar 07 11:51 AM
|
fiogf49gjkf0d You may also want to add
AND A1.TYPE IN ('262145', '262146', '262147', '262154')
so that it is actually an activity or an email that it looks at.
I just recently had a problem using that with v7.01 and went a slightly different route on the sql
This one was contact / no activity in 6 months:
Contact.Contactid
in
select c.contactid from contact c left outer join history h on c.contactid = h.contactid where h.type is null or (h.type in (262145,262146,262147,262148,262154) and datediff(d, h.completeddate, getutcdate()) < 180) group by c.contactid having count(h.historyid) = 0
no quotes necessary on 7.01 |
|
|
|