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()

Sign up

Login to the community