Query to find max date

[Wade Clairmont]

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 · Accepted solution]

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

[Wade Clairmont]

So here is what I have now, however, now the   ts.u_week_starts_on = wsodate.addDays(7); must be wrong as there is no date after that function, is the syntax right?   Thanks again!

var copyHours = false;

try {

  var ts = new GlideRecord('u_time_sheet');

  ts.u_state = 'Pending';

  wsodate = getnextdate();

  ts.u_week_starts_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 = ts.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 ' + wsodate + ' ' + ts.u_week_starts_on);

  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()) {

      var startson = count.u_week_starts_on.getGlideObject();

  return startson;

  }

}

[tltoulson]

Hey Wade,

Sorry, I overlooked an additional issue.   The addDays function doesn't return anything (technically returns null).   The addDays internally changes the existing object.   So instead of:

ts.u_week_starts_on = wsodate.addDays(7);

Do this:

wsodate.addDays(7);

ts.u_week_starts_on = wsodate;