Days since in Catalog Item/RITM

[JonnyFoster]

Hello brains trust. 

I have a requirement to document physical site visits by my IT team. All of the IT department are situated at one of three Corporate locations, and we have an SLA with the Business to visit our branches once per quarter. I would like to record this in ServiceNow through, probably, a Service Request. 

I've already built a form that the team can use to record details of their visit (which branch was visited ref cmn_location table; who visited referencing the sys_user table with qualifying references; what was done on the visit, what is still to do; when the visit occurred with a date field). 

What I'd like to have on the resulting form is a 'days since this visit' automatically calculated on the RITM or SCTask which becomes red after 93 days. I can then present a dashboard, listing each of our locations and finding the most recent request of the 'branch visit' type and how many days ago that visit occurred. I have a field on the form which displays today's date and will be hidden once I have a solution.

What I'm struggling with is getting the 'days since this visit' field calculated. 

Anyone able to help, please? Screen shot of the form presented in ESC for reference:

[Jordan Vignoni]

I think you could accomplish this by creating an onChange catalog client script for when the "Branch Visited" is populated (or changes).  The logic will include a GlideAjax call to a script include that queries the most recent visit of the tasks for the specific branch, and returns the difference of the two dates.

[JenniferRah]

If you want the value stored in the record itself, you would have to run a script against all the records nightly to calculate that value and store it in the record. 

You can handle the coloring with a custom style (System UI -> Field Styles).