question Re: Setting Due Date Field in ITSM forum

Setting Due Date Field

jcsmith — Fri, 27 Jan 2017 13:37:53 GMT

I am attempting to set the due_date field on the catalog requested item form to pull from a user created field (u_planned_completion_date) on the sc_task table. Preferably, if the item has multiple tasks, it would use the last Planned Completion Date available. For some background, I'm doing this to override the auto-calculated "Expected Delivery Date of Completed Order" that the user sees when a request is submitted. I would like this to be blank until the work is planned and the Planned Completion Date field is filled.

I, honestly, don't even know where to start on this one. I'm not technically inclined, as I usually work on front-end, user experience with SNOW. Any and all help would be appreciated.

The closes thing I can think of would be something like:

task.due_date = current.variables.u_planned_completion_date;

But, I'm fairly certain that's not even close. I'm thinking it's going to have to be a more complicated client script.

Thanks!

Re: Setting Due Date Field

Chuck Tomasi — Fri, 27 Jan 2017 13:44:13 GMT

Hi Justin,

You can do this with a before business rule to automatically trigger or you can use a UI action to manually trigger it.

The key is to find all child requests and find the greatest value for the u_planned_completion_date field. Something like this:

Standard disclaimer: The following code is untested, requires review and potential modifications (specifically field names)

// Assumption: This is running on the sc_req_item table

(function () {

var max = new GlideDateTime('1970-01-01 00:00:00');

var rec = new GlideRecord('sc_task');

rec.addQuery('request_item', current.request_item);

rec.query();

while (rec.next()) {

if (rec.u_planned_completion_date > max)

max = rec.u_planned_completion_date;

}

current.u_planned_completion_date = max;

})();

Re: Setting Due Date Field

jcsmith — Fri, 27 Jan 2017 13:46:49 GMT

I'll give that a try and let you know how that works, Chuck. Thank you!

Re: Setting Due Date Field

kristenankeny — Fri, 27 Jan 2017 13:50:18 GMT

Hi Justin,

You mention that the field you want to reference is on the sc_task, but then the script you provided seems to indicate that the field is actually a variable on the catalog item (which then populates in the variables on the requested item and catalog task. I'm going to work from the assumption that you have a custom field on the sc_task table that staff is updating and that you want that to trickle up to the requested item.

You'll want to create a business rule on the sc_task table. Make it a "before" rule and on "update".

Add a condition that u_planned_completion_date changes

Check off "advanced" so you can do some scripting

In the script try something like this:

var t = new GlideRecord('sc_task');

t.addQuery('request_item',current.request_item);

t.query();

var date = '';

while(t.next()){

if(date == ''){

date = t.u_planned_completion_date;

}

else if(t.u_planned_completion_date > date){

date = t.u_planned_completion_date;

}

var r = new GlideRecord('sc_req_item');

r.addQuery('sys_id',current.request_item);

r.query();

if(r.next()){

r.due_date = date;

}

Re: Setting Due Date Field

jcsmith — Fri, 27 Jan 2017 14:00:47 GMT

Kristen,

Thanks for the help! I tried adding that business rule and it was unsuccessful. It seems to do a strange thing to the due date on the task record, and did not change the due date on the requested item record. I'm wondering if the date references in the middle of the script should be due_date...but, again, I know nothing about this stuff. I'm just trying to get stuff done while our scripting person is out for a few weeks.

Re: Setting Due Date Field

kristenankeny — Fri, 27 Jan 2017 14:35:00 GMT

Can you provide a screenshot of the two records you want to update with the fields highlighted? Perhaps I'm not understanding the need.

Re: Setting Due Date Field

jcsmith — Fri, 27 Jan 2017 14:48:30 GMT

I would like this field:

to populate this field:

So the user sees the Planned Completion Date - or the latest Planned Completion Date from all tasks included in the request - here:

Re: Setting Due Date Field

Chuck Tomasi — Fri, 27 Jan 2017 14:56:09 GMT

Hi Justin,

The estimated deliver date of the completed order on that final screenshot is created from the workflow, if I remember right. It is "estimated". Users only see that when they do the final checkout (someone may correct me on that.)

Normally the tasks come along later in the process, so I am confused on the timing. If someone ordered something on Tuesday and you don't create tasks until later in the workflow (say Thursday), then how can you show them a different number on the checkout screen based on tasks that don't exist yet.

I get the idea of moving the latest task to the RITM. The scripts above should get you in that direction.

Re: Setting Due Date Field

jcsmith — Fri, 27 Jan 2017 15:04:03 GMT

Chuck,

The requester can always check the status of the request. You are correct in that the estimated completion date of the request is calculated from the stages/tasks in the workflow. However, we're just starting to implement our Service Catalog. We're starting with a very general service request option where the user basically just fills out a free form box with a description of the work they would like to have completed.

With this general use, there's no way to estimate or have a general schedule for the work that is to be completed since it could be something as small as requesting access to a system or as complex as upgrading a software across multiple data centers. This particular route only has a single item and a single task (fulfill the request), which is generated immediately after the request is submitted.

Re: Setting Due Date Field

kristenankeny — Fri, 27 Jan 2017 15:04:36 GMT

Chuck - isn't that view of the request the "self service" - no matter if it's upon submission or you open your request later? I think you're right though, it originates from the workflows - though this is at the request level - so is that trickling up from each requested item? We removed this from our layouts to prevent false expectations with our end users.

Justin - can you explain what you mean by strange things happening with the due date on the task? There may already be some business rules in place, either on the sc_req_item table or on the sc_task table that are causing some sort of recursive issue.

Re: Setting Due Date Field

jcsmith — Fri, 27 Jan 2017 15:10:11 GMT

Kristen,

So the due date field on the task (which I will probably hide when we go to production because it's not relevant) is populating with a date two months out. I do have a schedule currently set up on the item workflow, but the stage/task is only scheduled to take 14 business days. Now, looking back to some of the requests that I was testing before I tried your script, it was a "feature" that was happening before I added that business rule.

Re: Setting Due Date Field

kristenankeny — Fri, 27 Jan 2017 15:16:18 GMT

Okay, so the only remaining issue is updating the requested item's due date based on the planned completion date? What type of field is the planned completion date? I noticed OOB, the due date is a date/time field.

Re: Setting Due Date Field

jcsmith — Fri, 27 Jan 2017 15:19:22 GMT

Correct. Planned Completion Date is a user created, date/time field.

Re: Setting Due Date Field

kristenankeny — Fri, 27 Jan 2017 15:20:32 GMT

Hi Justin,

Just realized I missed a line of code (the update of the request item).

var t = new GlideRecord('sc_task');

t.addQuery('request_item',current.request_item);

t.query();

var date = '';

while(t.next()){

if(date == ''){

date = t.u_planned_completion_date;

}

else if(t.u_planned_completion_date > date){

date = t.u_planned_completion_date;

}

var r = new GlideRecord('sc_req_item');

r.addQuery('sys_id',current.request_item);

r.query();

if(r.next()){

r.due_date = date;

r.update();

}

Re: Setting Due Date Field

jcsmith — Fri, 27 Jan 2017 15:33:19 GMT

So I tried that, and it altered the "Expected Delivery Date of Complete Order" on the user view of the request, which is good. However, when I fill out the Planned Completion Date field and update the task record, it is not updating or completing the Due Date field on the item. That field is now blank, which is making the "Estimated Delivery Date of Complete Order" also blank.

Re: Setting Due Date Field

kristenankeny — Fri, 27 Jan 2017 16:58:43 GMT

Ok, after some testing in a personal dev, I found that this works, but it should be an "after" business rule:

var t = new GlideRecord('sc_task');

t.addQuery('request_item',current.request_item);

t.query();

var date = new GlideDateTime('1999-01-01 00:00:00');

while(t.next()){

var pd = t.u_planned_completion_date.getGlideObject();

if(date.getDisplayValue() == '1999-01-01 00:00:00'){

date = pd;

}

else if(t.u_planned_completion_date > date){

date = pd;

}

var r = new GlideRecord('sc_req_item');

r.addQuery('sys_id',current.request_item);

r.query();

if(r.next()){

r.due_date = date.getDisplayValue();

r.update();

}

Re: Setting Due Date Field

jcsmith — Fri, 27 Jan 2017 17:03:04 GMT

PERFECT!! Thank you so much Kristen. I cannot tell you how much I appreciate all of the help!