Query to find max date

Wade Clairmont · ‎02-23-2015

I am trying to find the most recent date in a function query in an attempt to add 7 days and reassign it to the original field.

Here is the UI Action that I have tried, but it still only takes the current date and adds 7 days. Not sure why, at least a query failure would tell me if I was wrong.

Any help would be appreciated. Thanks in advance!

var copyHours = false;

try {

var ts = new GlideRecord('u_time_sheet');

ts.u_state = 'Pending';

wsodate = getnextdate();

ts.u_weeks_start_on = wsodate.addDays(7);

ts.u_user = current.u_user;

var newID = ts.insert();

var te = new GlideRecord('u_time_entry');

te.addQuery('u_time_sheet', current.sys_id);

te.query();

while(te.next()) {

var teNew = new GlideRecord('u_time_entry');

teNew.initialize();

teNew.u_time_sheet = newID;

teNew.user = te.user;

teNew.task = te.task;

teNew.u_description = te.u_description;

teNew.category = te.category;

teNew.u_task_type = te.u_task_type;

teNew.u_comments = te.u_comments;

teNew.week_starts_on = te.week_starts_on;

teNew.setWorkflow(false);

teNew.insert();

}

gs.clearMessages();

gs.addErrorMessage('A new timesheet has been created, please verify Week Starts on Date before update or submission');

gs.setRedirect('/u_time_sheet.do?sys_id=' + newID);

}

catch(err) {

gs.log('CopyTS:ERROR:' + err);

}

function getnextdate() {

var count = new GlideRecord('u_time_sheet');

count.addQuery('user','current.user');

count.orderByDesc('week_starts_on');

count.setLimit(1);

count.query();

if (count.next()) {

count.getValue("u_week_starts_on");

// gs.print(count.getDisplayValue("week_starts_on"));

}

tltoulson · ‎02-23-2015

Hi Wade,

I was going to try to step through troubleshooting but in light of recent updates I will spill my thoughts. You have a couple problems with your getnextdate function:

1. The query treats current.user as a string instead of a variable. This will cause the query to find incorrect records

2. Unless you have a "user" field on the current record (not likely on a custom table), you probably want to use gs.getUserID() to get the currently logged in user's user ID. Its possible you also intended to use current.u_user. This should still be outside of quote marks.

3. The function does not return a proper GlideDate/GlideDateTime. This is required in order to call addDays. You can retrieve a GlideDateTime object from a GlideRecord's field using the getGlideObject function.

4. You lack a return statement in the getnextdate function which means it will always return null

The fix:

function getnextdate() {


   var count = new GlideRecord('u_time_sheet');


   count.addQuery('user', gs.getUserID()); // Fixes #1 and #1 above


   count.orderByDesc('week_starts_on');


   count.setLimit(1);


   count.query();


   if (count.next()) {


       return count.u_week_starts_on.getGlideObject(); // Fixes #3 and #4 above


   // gs.print(count.getDisplayValue("week_starts_on"));


   }


}

With all that said, I am assuming that your UI Action is Server Side (not marked client=true) and I have not evaluated the rest of your main function after the ts.insert line. This should resolve the calculation aspect, though. I hope this helps.

Kind regards,

Travis

View solution in original post

tltoulson · ‎02-23-2015

Hi Wade,

Does the gs.print on the last line of getnextdate return the date that you desire, or does it show you today's date?

poyntzj · ‎02-23-2015

Looking at your getnextdate function, it is probably returning a null and that is then adding just 7 days.

If you look at the function, change the line

count.addQuery('user','current.user');

to be

count.addQuery('user'current.user);

Cheers

jadona · ‎02-23-2015

I'd try to add some logs before and after the call to the "getnextdate" function to make sure it is returning the correct value.

Have tried to change your statement "wsodate = getnextdate();" to "var wsodate = getnextdate();"

and make the function "getnextdate" returning a value? Like:

var startson = count.getValue("u_week_starts_on");

return startson.

Another thing that I have realized in your function is that your statement

count.addQuery('user','current.user');

should be replaced by "count.addQuery('user',current.user);" providing user is a field of your "u_time_sheet" table.

I hope it helps

ohhgr · ‎02-23-2015

Hi Wade,

You don't seem to return the date from the function call. Could you try returning the date from the function call and try again?

Try below line in your code and verify the returned data

return count.getValue("u_week_starts_on");

Thanks,

Mandar