Number Maintenance - Adding a Year (YYYY) to the Prefix of a Table

Munna1 · ‎01-28-2024

While creating new Request it must follow this format REQF-SN-YYYYXXXXX.

Here YYYY denotes the Current Year. For e.g. 2024. Kindly note that this should get incremented as the year changes.

XXXXX would be a five digit auto incremented value. Here, this series should start from ‘10000’ series and get incremented by count of 1.This should get reset after the end of the year.
e.g. Jan 1 2024 entitlement should be like -> REQF-SN-202410001

and Jan 1 2025 entitlement should be like -> REQF-SN-202510001

I am trying with the Before insert BR. But it is not working as expected.

I will appreciate the explanation with the Script.

Thanks In Advance.

Saurabh Gupta · ‎01-28-2024

Hi @Munna1
You can reset the "sys_number_counter" within the same scheduled job as mentioned by @Tai Vu

Sample Script:

var gdt=new GlideDateTime();
var t=new GlideRecord('sys_number');
t.addEncodedQuery('category=your_table_name');
t.query();
if(t.next())
{
	t.prefix="REQF-SN-"+gdt.getYear();
	t.update();
	var tt=new GlideRecord('sys_number_counter');
	tt.addEncodedQuery('table=your_table_name');
tt.query();
if(tt.next())
{
	tt.number=0;
	tt.update();
}

}

C	If the provided solution meets your needs, kindly consider marking it as helpful and accepting it as the solution. This helps others who may have similar questions.

Thanks and Regards,

Saurabh Gupta

View solution in original post

Mark Roethof · ‎01-28-2024

Do be aware that changing the prefix will break some functionality. One example, using the search in the platform UI on older tickets won't work anymore! So do be aware of this, and do communicate such with your stakeholder, platform owner, etc..

Kind regards,

Mark Roethof

Independent ServiceNow Consultant

10x ServiceNow MVP

---

~444 Articles, Blogs, Videos, Podcasts, Share projects - Experiences from the field

Mark Roethof · ‎01-28-2024

So wouldn't it be better to NOT include the year in the Prefix, though include the year in the Number Counter instead?

Kind regards,

Mark Roethof

Independent ServiceNow Consultant

10x ServiceNow MVP

---

~444 Articles, Blogs, Videos, Podcasts, Share projects - Experiences from the field