Custom auto-number generator based on other field

[Community Alums]

Hi Experts, 

I have requirement where I have two fields in a table "PO number" and "Vendor PO number"
When ever the records inserted or updated with the PO number detail, Vendor PO number field has to update automatically.

If the PO number of the firs record is "1000" then the  Vendor PO number should get update as 1000 -1
and for the next record Vendor PO number should be 1000-2, and for third record 1000-3 it should continue like this for the same PO number

Multiple records will have same PO number so we want to differentiate with Vendor PO number, Vendor PO number will be the unique field in our table

Our script has to fetch the PO number and update the Vendor PO number sequentially for the next record, how I can achieve this

Thanks in advance,
Chaithanya

[Peter Bodelier · Accepted solution]

Hi @Community Alums,

I might be thinking too simple here, but I think you could create a Insert/Update Business Rule, with script (depending on your field names and table name):
var gr = new GlideRecord('<<tablename>>');

gr.addQuery('po_number', current.po_number);

gr.orderByDesc('vendor_po_number');

gr.setLimit(1);

gr.query();

if (gr.next()){

current.vendor_po_number = current.getValue('po_number') + '-' + parseInt(gr.getValue('vendor_po_number').split('-')[1]) +1;

}

else{
current.vendor_po_number = current.getValue('po_number') + '-1'

}

[Community Alums]

@Peter Bodelier Thank you for your response, it solved my issue!!

[Community Alums]

Hi @Peter Bodelier 

Now the requirement got changed , new requirement is suffix should start with -001 so that would result in the following sequence of vendor po numbers like;  1000-001, 1000-002, 1000-003, and so on?

I have modified the code and it is working till 1000-009, but it is not appending for 1000-010 so on...

Below is the code

Please let me know How I can modify this, I have tried this in background there it is working fine till 1000-99. 

Thanks in advance,
Chaithanya

[Peter Bodelier]

Hi @Community Alums,

In understand what you're trying to do, but I think you're making it too complex.

I believe this should suffice, replacing your add and pad functions:

function newAdd(str){
    str++;
    if (str.toString().length == 1){
        return '00' + str;
    }
    else if (str.toString().length == 2){
        return '0' + str;
    }
    else{
    return str;
    }
}