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'

}

[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]

Hi @Peter Bodelier 
Thanks for the reply!
I have tried After Insert/Update Business rule with the above code, but it is not working.

Thanks,
Chaithanya

[Peter Bodelier]

Hi Chaithanya,

Is any error thrown, or is it doing something else then expexted?

[Community Alums]

Hi @Peter Bodelier  @SimonL898794821 Thank you for your response, but the script is throwing an error.
Please find the error.

Thanks,
Chaithanya