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!
|
|
Specify a dynamic date range for a group
Posted: 07 Aug 08 1:50 PM
|
I've run into this issue several times where I need to create a group of contacts or accounts where activity has occurred between two dates. This is fine if the dates are static, but they need to be dynamic. For example, show contacts with history 5 to 8 days ago. For this I tried using a SELECT statement in the group conditions but it would not work.
SELECT DATEADD(dd,-5,GETDATE())
Is there any way to setup a group that uses dynamic dates?
Thanks,
John G. |
|
|
|
Re: Specify a dynamic date range for a group
Posted: 07 Aug 08 2:38 PM
|
For the LAN Client, in the dialog that specifies the condition, you can check the "Use Value as Literal" checkbox. Then enter the condition to get dynamic dates.
Sadly, this doesn't work with the SLX Web Client. (At least in 7.2.) |
|
|
|
Re: Specify a dynamic date range for a group
Posted: 08 Aug 08 6:54 AM
|
I tried checking the 'literal' checkbox and it still did not work. I believe when that box is checked the SQL will look something like
... WHERE a.modifydate >= 'SELECT DATEADD(dd,-7,GETDATE())'
This results in 0 records returned.
Thanks,
John G.
|
|
|
|
Re: Specify a dynamic date range for a group
Posted: 08 Aug 08 6:59 AM
|
I tried checking the 'literal' checkbox and it still did not work. I believe when that box is checked the SQL will look something like
... WHERE a.modifydate >= 'SELECT DATEADD(dd,-7,GETDATE())'
This results in 0 records returned.
What I was thinking of doing is creating a view in the database. So the view would be something like
SELECT DISTINCT contactid
FROM history
WHERE modifydate >= 'SELECT DATEADD(dd,-7,GETDATE())'
Then I can setup a group condition that says show me all the contact IDs that are IN SELECT contactid FROM c_CustomViewName
Hmmm, I think that would work. Of course the only problem with this approach is if you have remote clients. How do you send a custom view out to a remote client? I think there is a way to do this that was new with 7.0 but I don't remember. Anyone?
Thanks,
John G.
|
|
|
|
Re: Specify a dynamic date range for a group
Posted: 08 Aug 08 8:36 AM
|
Hi John
Remove the select from your value after choosing use literal eg
In the group builder condition ...
Fieldname = History.ModifyDate
FieldType = Date/Time
Operator = (>) greater than
Value = dateadd(dd, -7, getutcdate())
Use value as a literal = ticked
alternatively
Accountid
in
select accountid from history where modifydate > dateadd(dd, -7, getutcdate())
without the use as literal ticked
Kind Regards
Nick |
|
|
|
Re: Specify a dynamic date range for a group
Posted: 08 Aug 08 8:51 AM
|
Hi John,
I have checked and is working ..
do as follows In codition box
1. Select the operator required
2. Check 'Use value as literal'
3. add this - DATEADD(dd,-7,GETDATE()) in the 'value is' box without quotes and select
-Harendra |
|
|
| |
|
Re: Specify a dynamic date range for a group
Posted: 11 Aug 08 9:20 AM
|
Originally posted by John Gundrum
Ah... Yes this worked. I did notice that you must check 'Use value as literal' before attempting to enter the value.
Thanks to all! |
|
that's why i have mentioned the sequence of steps.. |
|
|
|