Generate unique Number when field is empty

[cipla]

Hello all,

I have made a table App Number, and its form has a field App No. My requirement is that when any record is created with its App No. field entry as empty, a unique number is generated for all empty entries entries in sequential order.

As per the above screenshot , in all records which are empty I want some number to be auto generated. Like for First empty entry Number is EMP1 for second its EMP2 and so on.. and the number should not be duplicated, they must be unique.

Can anybody help me with this requirement?

[asit2]

Hi Arpita,

You can try to solve the same with business rule-

I have wrote one rule that will run before update or insert of the table App Number(u_app_number).

that will check the highest number and increment the same when the rule will fire.

here is the output-

Here is the code of business rule-

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

//selecting max number

      var curr_max_num=0;

      var next_num=0;

      var gr = new GlideRecord('u_app_number');  

      gr.addQuery('u_app_no','CONTAINS','EMP-');  

      gr.orderByDesc('u_app_no');  

      gr.query();

if(gr.next())

{

var max_str=gr.u_app_no;

                                              var array = max_str.split('-');

                                              curr_max_num=parseInt(array[1]);

}

var nxt_val=curr_max_num+1;

var nxt_val_str='EMP-'+nxt_val;

current.u_app_no=nxt_val_str;

})(current, previous);

App Number

[cipla]

Hi Asit,

Thanks for the help, your code is perfectly working when I create new records and leave App No empty. But in case of existing records it is not updating the empty entries. I also want that it should query for all empty records and autonumber in sequence.

[astha_chaubey]

Hi Arpita,

I was quite curious to know if this scenario works without scripting. I tried your scenario on my personal dev. Below are the steps I followed-

1. Created a new table- Applications

2. Created Two fields in it, Number and Short Description

3. Default value for Number is set to- javascript:getNextObjNumberPadded();

4. Created an exel sheet where I created two columns- Number and Short description,shown below

5. Created a transform map, selected source table as my import set table, and target table as Application table.

6. I imported records and all went well by default, here are the records imported-

I think this works by default without any scripting involved.

Thanks & Regards,

Astha Chaubey

[cipla]

Hi Astha

This works but the numbers are not sequential, it starts from APP0001002. Ideally it should be APP0001001 for first empty entry, APP0001002 for second empty irrespective of the position of the record.

[cipla]

Hey Asit,

The BR you have made is working fine till APP-10 (I have change the suffix from EMP to APP), but after APP-10 it is again creating APP-10 for all next empty records.