How to increment the field value for the existing records in the table.

[Gowtham3]

Hi All,

There is a table with 10 Records with one of the fields having values like 1,1,2,2,3,3,4,4,5,5.

Let's say

Record 1 has 1

Record 2 has 1

Record 3 has 2

Record 4 has 2

Record 5 has 3 and so on

I want to set a new value for these existing records as "ABC001" and the subsequent records should get incremented to "ABC002", "ABC003" ...... "ABC010".

Is there a way to do this?

Thanks,

Gowtham

[MrMuhammad]

Hi Gowtham,

Please try this.

var gr = new GlideRecord('table_name'); // place your table name
gr.orderBy('field_name'); // field name you need to update
gr.query();

var count = 0;
while(gr.next()){
  count+=1;
  gr.setValue('field_name', 'ABC00'+count);// replace with field name that needs update.
  gr.update();

}

Please mark this accepted/helpful, if applicable.

Thanks & Regards,

Sharjeel

[Gowtham3]

Hi Muhammad,

The incrementation is happening. But there is an issue.

I want it to be incremented as "ABC001, ABC002, ABC003...., ABC010".

But the above logic is incrementing as like "ABC001, ABC002....ABC009" after this it is incrementing to ABC0010. where the length is exceeding more than 6 characters.

The increment should happen within the 6 characters up to "ABC999".

Is there a way to do that?

Thanks,

Gowtham

[MrMuhammad]

Try this one.

var gr = new GlideRecord('table_name'); // place your table name
gr.orderBy('field_name'); // field name you need to update
gr.query();

var count = 0;
while(gr.next()){
  count+=1;
  var name = 'ABC00'+count;
  var nameLen = name.length;
  if(nameLen <= 6){ 
  gr.setValue('field_name', 'ABC00'+count);// replace with field name that needs update.
  gr.update();
 }else{
   nameLen = nameLen - 6;
   nameLen = 6-nameLen;
   name = name.slice(0, nameLen);
   name = name+count;

}
}

[MrMuhammad]

Hi Gowtham, 

Is this answered? 

Kindly mark the appropriate answer as accepted & helpful so that others can be benefited by this and this thread can be closed. 

Thanks & Regards,

Sharjeel