Auto-numbering

Anish Reghu · ‎10-16-2020

If I add a new prefix - BLM on the sys_number table.

Now help me write a Business rule that this prefix will only auto-number user_name field on the User table if Department is BLM.

How to achieve this?

Mark Roethof · ‎10-17-2020

Maybe a small alternative, which would prevent issues when you are deleting users or removing the department of users: using an actual last number record.

- Just create a record in the sys_number_counter table (don't select a table, just create a record with only a number matching or higher then your last current number)

- Business Rule condition stays on department = your department OR department changes to your department, on insert + on update
Maybe add, user_name does not contain your prefix? But that's up to you.

- Script like (only update the sys_id with the sys_id of your new number record):

(function executeRule(current, previous /*null when async*/) {

	var prefixStr = 'BLM';
	var numberInt = 1000000;
	
	var grCounter = new GlideRecord('sys_number_counter');
	grCounter.get('be237ed32fb3d890b0c2d5ea2799b62a');
	
	var lastInt = grCounter.getValue('number');
	
	current.user_name = prefixStr + (parseInt(numberInt) + parseInt(lastInt) + 1);

	grCounter.addValue('number', 1);
	grCounter.update();
	
})(current, previous);

If my answer helped you in any way, please then mark it as helpful.

Kind regards,
Mark
2020 ServiceNow Community MVP
2020 ServiceNow Developer MVP

---

LinkedIn
Community article list

Kind regards,

Mark Roethof

Independent ServiceNow Consultant

10x ServiceNow MVP

---

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

View solution in original post

Anish Reghu · ‎10-17-2020

Hey thanks, it works. But now a new issue. I enabled Update also on the business rule. and I get this error.

Let's say I update a record. It updates the User ID, if Department matches.

But when I try and create a new user through catalog item, it again tries to reuse the same ID it allotted during update.

And thereby prevents the record being created because this is the unique key on the User table.

Mark Roethof · ‎10-17-2020

Not sure what you mean here. I just ticket the update checkbox, updated the condition to also run on change to department, and just works.

Obviously you would get in trouble, when removing the department or deleting a user with that department. The count would get messed-up. Though that's up to you. I don't know your way of working etc..

So maybe better to store the latest number somewhere (not in a system property!), grab that latest number, and use that instead of scripted count.

Or grab the latest by sorting, though then you would need to change the scripting also doing a split on the user_name, to be able to do a +1.

If my answer helped you in any way, please then mark it as helpful.

Kind regards,
Mark
2020 ServiceNow Community MVP
2020 ServiceNow Developer MVP

---

LinkedIn
Community article list

Kind regards,

Mark Roethof

Independent ServiceNow Consultant

10x ServiceNow MVP

---

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

Anish Reghu · ‎10-17-2020

For some reason, the BR is creating User ID of new records with the existing numbers. And I want this to be avoided. The above error happened when I created a new record. The User ID seen in this record was generated for a updated record.

Anish Reghu · ‎10-17-2020

I think I got this. It is happening because of the COUNT.

While updating although auto-number is generated, the COUNT still remains the same. So, next time a new record is generated once again the same ID is being attempted leading to this error. Hope you understand what I mean here. How to avoid this?

Mark Roethof · ‎10-17-2020

Don't know, it doesn't happen here. Did you change the department on a user that already had a BLM ID? Or deleted such a user for example?

What can you debug on this?

If my answer helped you in any way, please then mark it as helpful.

Kind regards,
Mark
2020 ServiceNow Community MVP
2020 ServiceNow Developer MVP

---

LinkedIn
Community article list

Kind regards,

Mark Roethof

Independent ServiceNow Consultant

10x ServiceNow MVP

---

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