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]

If you remove the ParseInt around that, what is it returning then?

[Community Alums]

Hi @Peter Bodelier Thankyou!!
It worked, but instead of adding to the current count, it was adding one more digit,. like if the last record has 1000-1 Vendor PO number instead of 1000-2, it was adding 1000-21, so I tried (++currentCount) like this and it is working , I can see this in logs.

But while doing the testing I just inserted/updated one record manually , but the vendor PO number is not getting update.
Please find the final code screenshot

Please let me know to update the field in the record if any changes required for code,

[Peter Bodelier]

It looks like you are now updating a different record? (gr.update())

I think you changed this to an after Business Rule somewhere, which means you'll have to add current.update() to the button, or leave it as is (without gr.update), and change the Business rule to before.

[Community Alums]

Hi @Peter Bodelier 

Found one issue, each time when the record got updated, vendor_po_number is incrementing. it should not be like this. once it is updated it should be constant, how I can fix this..

Thanks!

[SimonL898794821]

Maybe the script should only trigger if not already executed? Something like this:

(function executeRule(current, previous /*, g*/) {
  if (!current.u_script_executed) { 
    var gr = new GlideRecord('<table>');
  
    gr.addQuery('u_number_1', current.u_number_1);
  
    gr.orderByDesc('u_number_2');
  
    gr.setLimit(1);
  
    gr.query();
  
    if (gr.next()) {
      var currentCount = parseInt(gr.getValue('u_number_2').split('-')[1]);
      current.u_number_2 = current.getValue('u_number_1') + '-' + (currentCount + 1);
    } else {
      current.u_number_2 = current.getValue('u_number_1') + '-1';
    }
  
    current.u_script_executed = true; 
  }
})(current, previous);