Discussions

Expand all | Collapse all

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

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

    Posted 05-15-2018 20:02
    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.


  • 2.  RE: Incrementing Number based on parent record total - Legacy Accounting Software requirement

    Posted 05-15-2018 20:45
    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()


  • 3.  RE: Incrementing Number based on parent record total - Legacy Accounting Software requirement

    Posted 05-15-2018 21:05
    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.


  • 4.  RE: Incrementing Number based on parent record total - Legacy Accounting Software requirement

    Posted 05-15-2018 21:34
    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!


  • 5.  RE: Incrementing Number based on parent record total - Legacy Accounting Software requirement

    Posted 05-15-2018 21:45
    Glad to help.


  • 6.  RE: Incrementing Number based on parent record total - Legacy Accounting Software requirement

    Posted 05-15-2018 21:37
    .. 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.


  • 7.  RE: Incrementing Number based on parent record total - Legacy Accounting Software requirement

    Posted 05-15-2018 21:45
    That's a great call-out.  If a purchase order is deleted, then there is potential for a duplicate Purchase Order number.  


  • 8.  RE: Incrementing Number based on parent record total - Legacy Accounting Software requirement

    Posted 05-15-2018 22:10
    I've removed their delete privileges just now and am a big fan of the cancelled/void/etc option. Thanks!