Adding a AutoNumber to a table after the fact

Steve42 · ‎06-08-2020

I have been asked to create an auto-number system on a table that was not created with one. What I mean is say link on Incident table it starts with INC000001. Is it possible to add a number like this after the table has been created and number what is already there. Not important the numbers for the previous entries but going forward should be in order.

AbhishekGardade · ‎06-08-2020

Hello Steve,

Yes we can have numbers for existing records or table created already who do not have number.

You need to generate the number for old records in sequence using a background script and then update the latest counter in your number table for new records.

var gr = new GlideRecord("cmdb_software_product_model"); // your table name

gr.orderBy('sys_created_on');

gr.query();

while (gr.next()) {

var nm = new NumberManager('cmdb_software_product_model'); // your table name

gr.u_number = nm.getNextObjNumberPadded(); // field you want to set the number

gr.autoSysFields(false); // Do not update sys_updated_on, sys_updated_by, and sys_mod_count

gr.setWorkflow(false); // Do not run any other business rules

gr.update();

}

Check out this blogs,

NUMBER EXISTING TABLES

Numbering Existing Records

Please mark as Correct Answer and Helpful, if applicable.
Thank You!
Abhishek Gardade

Thank you,
Abhishek Gardade

View solution in original post

Steve42 · ‎06-08-2020

I have already marked it as correct from another user post.

Thank you

Dhananjay Pawar · ‎06-08-2020

Hi,

Glad you got your answer. previously it was not there that is why I replied.

Thanks,

Dhananjay.

Mark Roethof · ‎06-08-2020

Hi there,

Yes it's possible to setup auto number even after the table has been set up.

Concerning the numbers already set. Also this is easy to influence. When navigating to Number Maintenance, select your number, select the Related Link "Show Counter". Here you can update the last number.

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

Daniel Gurney1 · ‎06-20-2023

You can't use global.NumberManager from a scoped application. Instead, you will need to generate the number yourself. Something like this:

        var sTable = 'x_0123456789_your_table';
        var oInfo = {
                number: 1000, // starting number
                size: 5, // length of padded numbers
                prefix: 'ABC'
        };
        var total = 0;
        var num = oInfo.number;

        var gr = new GlideRecord(sTable);
        gr.orderBy('sys_created_on');
        gr.query();
        while (gr.next()) {
            if (!gr.getValue('number')) {
                total++;
                num++;
                var numID = ('000000000' + num).substr(0 - oInfo.size); // generate padded number
                gr.setValue('number', oInfo.prefix + numID); // add prefix and set the number
                gr.update();
            }
        }

        // fix up numbers so that a new record will have the correct offset
	var msg = '';
        for (var i=0; i < total; i++) {
            var gr = new GlideRecord(sTable);
            gr.newRecord();
	    msg = gr.getValue('number');
        }
	if (msg ) {
	    gs.info('Updated ' + sTable + ' counter to: ' + msg );
	}