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

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
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.
Photo of Lee Gilmore

Lee Gilmore

  • 762 Points 500 badge 2x thumb
  • anxious and confused

Posted 6 months ago

  • 0
  • 1
Photo of Forrest Parker

Forrest Parker

  • 598 Points 500 badge 2x thumb
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:

  1. Create a summary field in the jobs table to count the number of purchase orders called "# of Purchase Orders"
  2. Create a formula text field in the jobs table called "Next Purchase Order Number"
  3. 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()
(Edited)
Photo of Lee Gilmore

Lee Gilmore

  • 762 Points 500 badge 2x thumb
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.
Photo of Lee Gilmore

Lee Gilmore

  • 762 Points 500 badge 2x thumb
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!
Photo of Forrest Parker

Forrest Parker

  • 598 Points 500 badge 2x thumb
Glad to help.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
.. also be sure to prevent any users from deleting Purchase Orders.  If necessary allow them to marked as "Cancelled", but not allow anyone, even the Admin Role to delete.
Photo of Forrest Parker

Forrest Parker

  • 598 Points 500 badge 2x thumb
That's a great call-out.  If a purchase order is deleted, then there is potential for a duplicate Purchase Order number.  
Photo of Lee Gilmore

Lee Gilmore

  • 762 Points 500 badge 2x thumb
I've removed their delete privileges just now and am a big fan of the cancelled/void/etc option. Thanks!