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'

}

[SimonL898794821]

Works fine in my PDI. Please share the full script you are using.

[SimonL898794821]

Try changing sortByDesc to orderByDesc. I do think this script won't count like -1,-2,-3 but instead write out -1, -11, -111.

[Peter Bodelier]

Nice catch on the orderByDesc. I think it shouldn't add ones, because it's an integer.

[SimonL898794821 · Accepted solution]

Maybe something like this will work (of course with your own fields):

(function executeRule(current, previous /*, g*/) {
var gr = new GlideRecord('<table>');

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

gr.orderByDesc('number_2');

gr.setLimit(1);

gr.query();

if (gr.next()) {
var currentCount = parseInt(gr.getValue('number_2').split('-')[1]);
current.number_2 = current.getValue('number_1') + '-' + (currentCount + 1);
} else {
current.number_2 = current.getValue('number_1') + '-1';
}
})(current, previous);

[Community Alums]

In Line no. 10, variable currentCount is storing the value as"NaN".