Auto-numbering

[Anish Reghu]

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?

[Anish Reghu]

I tried this, but still the error occurs for Unique key violation.

This is because we are creating User ID numbering depending on the COUNT.

The COUNT of records although increases on new record being added, will not increase when update operation is performed. 

[Mark Roethof]

Sorry, can't reproduce it here. Works fine here 😞

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

[Anish Reghu]

Can you please share the screenshots of your existing BR tabs? When to run and Script.

[Mark Roethof · Accepted solution]

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

[Anish Reghu]

This was on update and just verified, also on INSERT.