addQuery() Date This Week

[stevejarman]

Hi - I'm attempting to write a query that only returns results when a field of type Date (called u_date_worked) contains a value in the current week.

This is what I'm trying, which results in an "undefined" error.

       var rs = new GlideAggregate("task_time_worked");
       rs.addQuery("user", "=", "javascript:gs.getUserID()");
       rs.addQuery("u_date_worked", ">", "javascript:gs.beginningOfThisWeek()");
       rs.addAggregate("SUM","u_time_in_hours");
       rs.groupBy("user");
       rs.query();

Can anyone help me out?

Second part to this question, I'll also need the "less than" side of this query, but I'm finding that this causes the script to fail as it sees the "less than" sign as a tag. How do you properly use this code within a Jelly bracket? (might also be why the first part above doesn't work, though that script does actually save).

<g:evaluate var="jvar_totalTime">
       var rs = new GlideAggregate("task_time_worked");
       rs.addQuery("user", "=", "javascript:gs.getUserID()");
       rs.addQuery("u_date_worked", ">", "javascript:gs.beginningOfThisWeek()");
       rs.addQuery("u_date_worked", "<", "javascript:gs.endOfThisWeek()");
       rs.addAggregate("SUM","u_time_in_hours");
       rs.groupBy("user");
       rs.query();
       if (rs.next())
       {
               rs.getAggregate("SUM", "u_time_in_hours");
       }
</g:evaluate>

[stevejarman]

In fact... I've just figured out that I can't even get a basic UI Page to work. I'm obviously doing something wrong but I can't spot what it is.

For example:

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">

<g2:evaluate>
       var rs = new GlideRecord("task_time_worked");
       rs.addQuery("task", "=", "704880144fad9e0022c720201310c7fb");
       rs.query();
</g2:evaluate>

<table border="1">
<tr><td colspan="4"><center>Utilization</center></td></tr>

<j2:while result="$[rs.next()]">
       <tr><td><b>Work Started:</b></td><td>$[rs.getValue("u_work_start")]</td><td><b>Work Finished:</b></td><td>$[rs.getValue("u_work_finish")]</td></tr>
</j2:while>

</table>
</j:jelly>

doesn't work. I just see the title cell "Utilization" but no row data.

Running this as a Background Script though works just fine:

var rs = new GlideRecord("task_time_worked");
rs.addQuery("task", "=", "704880144fad9e0022c720201310c7fb");
rs.query();


while (rs.next())
{
       gs.log(rs.getValue("u_work_start") + "---" + rs.getValue("u_work_finish"));
}

[russell_miller]

Hi Steve,

You don't need the quotes or the "javascript: xxxx" in your query definitions.

Try

      rs.addQuery("user", gs.getUserID());  

      rs.addQuery("u_date_worked", ">", gs.beginningOfThisWeek());

and for the jelly script you need to escape the symbols for the less than...

eg: rs.addQuery("u_date_worked", "<", gs.beginningOfThisWeek());

Hope that helps