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]

Not necessary to do this by script.

Just use condition builder of the Business Rule, to check if the u_vendor_po_number field is empty,

[Community Alums]

Hi @Peter Bodelier 

If I clear the PO number and vendor PO number of previous records and try to give the same PO number for new records , vendor PO number is generating by considering the previous Vendor PO number order.
Ex. In the below screenshot, I have cleared the PO number and Vendor PO number in all the  records which has PO number 1000, now I'm trying to give same PO number that is 1000 for my new record and when I save it Vendor PO number is populating as 1000-9, I'm expecting that should be 1000-1 because I don't have any records which have PO number 1000 in the table.

How can I fix this..

Thanks,
Chaithanya

[Peter Bodelier]

Could you share the script you are now using?

[Community Alums]

Hi @Peter Bodelier 
Please find the script.

Thanks,
Chaithanya

[Peter Bodelier]

Hi @Community Alums,

Add gr.addNotNullQuery('u_vendor_po_number'); to your query.