8/24/2025 5:39:36 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
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!
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.
|
|
|
|
Groups, report building, and dates
Posted: 08 Aug 08 1:46 PM
|
Here's a simplistic view of what I'm trying to do. I need a view/group that shows which accounts were created between 10 and 20 days ago. Easy to do in sql, but I think it may be impossible to do in query builder.
In the condition tab there are many operators when a Date/Time field is selected. (> greater than, < less than, within last xxx days, null, blah, blah blah). I see how to hard code dates, but I don't see a way to utilize 'now()' or sysdate. "Use value as a Litera" is also an option, but that doesn't help me a bit.
I need ( > now() + 10 days) AND ( < now() + 20 days) .
thanks very much.
|
|
|
|
Re: Groups, report building, and dates
Posted: 08 Aug 08 3:17 PM
|
Glenn,
Open the Query builder.Drag n drop the required fields in layout. Open the 'Conditions' tab.
Define the codition you want as ....
1)Fieldname = blah
2)FieldType = Date/Time
3)Operator = (>) greater than
4)'Use Value as a literal' = ticked
5)Value = DATEADD(dd,10,GETDATE())
add another condtion..
3)Operator = (<) greater than
4)'Use Value as a literal' = ticked
5)Value = DATEADD(dd,20,GETDATE())
OK......OK
-Harendra |
|
|
| |
|
Re: Groups, report building, and dates
Posted: 08 Aug 08 5:08 PM
|
Well, unfortunatly this doesn't work for me & I'm pretty sure it's because we're on oracle.
I've done some looking and the equivelent of 'DATEADD(dd,20,GETDATE())' should be 'sysdate + 20'.
This doesn't work either & I think it's because slx is putting single quotes around the entry. If I view the SQL, here's what I see:
.....A5.EXPDATE>'sysdate + 20' ORDER BY.....
If I paste the entire sql expression into my sql window & remove the single quotes it works.
Any Ideas?? I think this is an SLX bug.
Thanks. |
|
|
|
Re: Groups, report building, and dates
Posted: 11 Aug 08 9:33 AM
|
Originally posted by Glenn Williams
Well, unfortunatly this doesn't work for me & I'm pretty sure it's because we're on oracle.
I've done some looking and the equivelent of 'DATEADD(dd,20,GETDATE())' should be 'sysdate + 20'.
This doesn't work either & I think it's because slx is putting single quotes around the entry. If I view the SQL, here's what I see:
.....A5.EXPDATE>'sysdate + 20' ORDER BY.....
If I paste the entire sql expression into my sql window & remove the single quotes it works.
Any Ideas?? I think this is an SLX bug.
Thanks. |
|
when i viewed sql (i am using SQL server 2005) , there is no single quotes(i don't think slx is putting quotes .. may be i am wrong .. if its true SLX is treating the statement differently for SQL Server and Oracle ..thats a bug)
SELECT A1.CONTACTID, A2.ACCOUNT A2_ACCOUNT, A2.ACCOUNTID A2_ACCOUNTID, A2.MODIFYDATE A2_MODIFYDATE, A1.NAMELF FROM CONTACT A1 INNER JOIN ACCOUNT A2 ON (A1.ACCOUNTID=A2.ACCOUNTID) WHERE (A2.MODIFYDATE<=DATEADD(dd,-10,GETDATE())) AND (A2.MODIFYDATE<=DATEADD(dd,-40,GETDATE())) ORDER BY A1.NAMELF ASC
AGAIN ... pls. make sure that you are entering the value without quotes as mentioned in my steps....
|
|
|
|
Re: Groups, report building, and dates
Posted: 12 Aug 08 8:16 AM
|
Two of us spent a lot of time trying to get this to work - no luck. I agree, I think this is a bug.
A brilliant individual provided this solution:
One of the operators available in the query builder is "IN". Therefore, you can do the following:
select account.accountid as the field, Operator = "in" and Value is:
(select accountid from account where createdate > ( sysdate - 20 )) and createdate < (sysdate - 10 ) )
Remember to check "Use value as Literal"
This works great and opens up many possibilities. Thanks very much for your help.
|
|
|
| |
|
Re: Groups, report building, and dates
Posted: 12 Aug 08 8:31 AM
|
What's odd is YES, but it works.
If you have to edit the condition the single quotes must be removed or you will receive an error. You remove them, save, & it works. When you edit the condition they're back.
|
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|