Incrementing Number based on parent record total - Legacy Accounting Software requirement

Question

My company's accounting software requires that we create Purchase Order Numbers with nine digits starting with the first 5 numbers being the Job Number and the last four digits being an incrementing number starting with 0001. Now, I'm well aware that this an annoying method and if I could use just the record ID #, I would. Unfortunately, I'm trapped with this accounting system requirement. I'm imagining a means in which I use a relationship to count/summaries the number of Purchase Order records associated to a Job and then having an Action stamp that value +1 to a field in the new record. At least, this is the part where I'm getting lost and not sure how to do this last set of steps.

forrestparker · Answer

Not too hard if all of the purchase orders will be added by clicking the "Add Purchase Order" button in the Jobs table.

If that is the case then you can achieve this in 3 steps:

Create a summary field in the jobs table to count the number of purchase orders called "# of Purchase Orders"
Create a formula text field in the jobs table called "Next Purchase Order Number"
Modify the "Add Purchase Order" formula URL field to write the "Next Purchase Order Number" to the purchase order number field in the Purchase Orders table

Formula for the "Next Purchase Order Number" field

var text jobNumber = ToText([Job Number]);
var number numberOfFollowingZeros = 4-Length(ToText([# of Purchase orders]));
var text followingZeros =
    Case
    (
    $numberOfFollowingZeros
    ,3,"000"
    ,2,"00"
    ,1,"0"
    ,0,""
    );
var text finalNumber = ToText([# of Purchase orders]+1);
$jobNumber & $followingZeros & $finalNumber

Sample formula for the "Add Purchase Order" field. (I was writing to field number 8 in the purchase order table)

URLRoot() & "db/" & [_DBID_PURCHASE_ORDERS] & "?a=API_GenAddRecordForm&_fid_7=" & URLEncode ([Record ID#])& "&_fid_8=" & URLEncode ([Next Purchase Order Number])  &"&z=" & Rurl()

lee_sgilmore · Answer

Good afternoon Forest,Thank you very much for your help. I'm trying to digest it and replicate this in my app as we speak. I'll report back shortly with my results.

lee_sgilmore · Answer

Good afternoon Forest,Your instructions were very clear and easy to follow. I was able to replicate this function in my app perfectly. I've also made sure to hide the green Add PO button from the users so that they will only use the modified Add PO field. I've also made the PO Number read only on the form view so they're unable to modify the value. Thank you again for your help, I really appreciate it!

quickbasecoachd · Answer

.. also be sure to prevent any users from deleting Purchase Orders.&nbsp; If necessary allow them to marked as "Cancelled", but not allow anyone, even the Admin Role to delete.

forrestparker · Answer

That's a great call-out.&nbsp; If a purchase order is deleted, then there is potential for a duplicate Purchase Order number.&nbsp;&nbsp;

Forum Discussion

Incrementing Number based on parent record total - Legacy Accounting Software requirement

Related Content

Master’s Thesis: Request for Quick Feedback on Low-Code Platform Requirements

Pipeline to Concatenate Text and Increment Numbers

The magic of Service Accounts

Exact form Font Size increment for Child table records

Required Fields