- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-15-2017 05:51 PM
We have a custom attribute, Committed SLA (u_eta), which is a date field type. We want to create a report that shows incidents where u_eta > closed.
Closed is a date/time field, so it does not appear as an option to compare to the date (only) field, u_eta.
Is there a way to compare the dates (only) of these two fields? Ideally, we could just strip-out the time component of the closed date/time value.
Solved! Go to Solution.
- Labels:
-
Reporting
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-16-2017 11:25 AM
To my knowledge, the best way to do this would be to create another field that doesn't show up on the form called something like u_eta_timestamp, that's a calculated field based on u_eta. In the calculated value, set it to midnight either UTC or local time depending on your needs.
Alternatively, you could set up a u_eta_timestamp field and create a business rule to update it whenever the value of u_eta is changed.
Once you have that field defined, you can use the field comparison operators to create your queries. In this case, you'd use something like u_eta_timestamp "is more than" closed to form the comparison.
EDIT: I just worked this up in my personal instance.
I created a new ETA [u_eta] field on the incident table that is of type Date.
Your table might be different, but this should have me in a state that represents what you have now.
Next I created a Date/Time field on the Incident table called Committed SLA Timestamp [u_eta_ts]. On the Dictionary Entry page, I clicked the link to see the Advanced view of the form. After that, I clicked the Calculated Value tab and used the following script for the Calculation:
new GlideDateTime(gs.dateGenerate(current.u_eta, 'u_eta_ts'));
Here's a screenshot of the relevant part of the Dictionary Entry page:
I moved the field on the form so that it's just below the Committed SLA field so you can see its value in the screenshot below. Note that calculated value fields are read-only. In your case, you'll probably want to just remove the timestamp field from the form completely so that the user doesn't even know it's there.
Also note that the timestamp is the start of the Committed SLA [u_eta] date in local time, which in the screenshot above is PDT (UTC -7). If you want it to be UTC instead, just use the following calculation formula instead:
new GlideDateTime(current.u_eta);
In either case, you can now get the records you're looking for by querying for Committed SLA Timestamp is more than 0 Days after Closed, as shown in the following:
Hope this helps,
--Dennis R
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-17-2017 11:06 AM
Due date is a task-level field, which means that it's shared among all derivative tables (incident, problem, request, etc.)
Doing a quick scan of business rules, the only thing I can see that might bite you is that for requested items [sc_req_item], the due_date field is set based on the catalog item delivery time information whenever a request item is created or updated. If your u_eta field is on that table, you'll need to create your own field or use a different one, or else disable the Calculate Request Item Due Date business rule.
I haven't done an extensive search, though, so your mileage may vary.