Want to write a program/Script for scheduled job calculating age of the case
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-13-2024 06:35 AM
Hello Experts,
I have a custom case table like below and I want to populate age of the case in ageing column ( Shown below ) based on below logic.
Age of the case should populate based on "Account" & "Currency" column combination. "Created On" field is of date time type.
Like shown in below example,
If no similar case is created ( Using Account/Currency combination ) before then age = 0. If same case is created on previous day then age =1 , again before previous day then age = 2.
If case is created multiple times on same day do not increase the count of age.
If same case is NOT created on Consecutive day reset Age =0 for that case.
| date-Time field | ||||
| Account | Currency | Created_On | Aging | |
| aaa | $U | 4/25/2024 | 0 | |
| aaa | $U | 4/25/2024 | 0 | |
| aaa | $U | 4/25/2024 | 0 | |
| aaa | $U | 4/25/2024 | 0 | |
| aaa | $U | 4/26/2024 | 1 | |
| aaa | $U | 4/26/2024 | 1 | |
| aaa | $U | 4/27/2024 | 2 | |
| bbb | $U | 4/18/2024 | 0 | |
| bbb | $U | 4/20/2024 | 0 | |
| bbb | $U | 4/21/2024 | 1 | |
| bbb | $U | 4/23/2024 | 0 | ( Here no case is available on exact previous day hence set age = 0 ) |
| bbb | $U | 4/24/2024 | 1 | |
| bbb | $U | 4/24/2024 | 1 | |
| bbb | $U | 4/24/2024 | 1 | |
| bbb | $U | 4/25/2024 | 2 | |
| bbb | $U | 4/26/2024 | 3 | |
| ccc | $U | 5/21/2024 | 0 | |
| ccc | $U | 5/21/2024 | 0 | |
| ccc | $U | 5/21/2024 | 0 | |
| ccc | $U | 5/28/2024 | 0 | |
| ccc | $U | 5/29/2024 | 1 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-13-2024 08:44 PM
Hi, based on details provided I would look at using a daily scheduled job\script to validate and update records as required.
Scheduled jobs (servicenow.com)
Glide Aggregate might be the best way to evaluate the number of records involved.
and you can then update the records with GlideRecord or GlideQuery (depending on scope)
GlideRecord | ServiceNow Developers
GlideQuery - Scoped, Global | ServiceNow Developers
But unfortunately, your post doesn't contain enough detail to allow anyone to provide any sort of script solution.
Why don't you give it a try yourself (you can test in a background window, or if scoped app via a fix script)
and come back to the community with specific questions and issues if you encounter any.
