Discussions

Expand all | Collapse all

Adding data to new record with URL formula but having problems with order of operations

  • 1.  Adding data to new record with URL formula but having problems with order of operations

    Posted 07-23-2018 18:53
    A while back, I posted a question trying to get an auto-number generator to match the criteria our enterprise accounting system requires.

    https://community.quickbase.com/quickbase/topics/incrementing-number-based-on-parent-record-total-le...

    In that posting, a suggestion was given that solved my need for a Job Number + Next Sequential four digit number value to generate and push to the field upon creating a new record. Unfortunately there are two separate instances where the formula doesn't behave as I'd like.

    • When there are "zero" PO records - I'm assuming the code treats zero children records as null and not as an one digit number?
    • When there are 9, 99 or 999 PO records, the next sequential number will have one more zero than it should. I understand this is because the check for total characters is done prior to the number being generated. Thus, 9 counts as "1" character and then it will add three zero's rather than two.

    I've hit a wall trying to figure out a solution to these two instances.









  • 2.  RE: Adding data to new record with URL formula but having problems with order of operations

    Posted 07-23-2018 18:59
    Len, can you post your code separately, as its not possible to read in a screen shot, nor copy paste to suggest fixes.


  • 3.  RE: Adding data to new record with URL formula but having problems with order of operations

    Posted 07-23-2018 19:10
    This is the formual to generate the next PO number:

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



    This is the Add button URL formula:

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


  • 4.  RE: Adding data to new record with URL formula but having problems with order of operations

    Posted 07-23-2018 19:17
    I just fixed the issue where 9, 99 and 999 add an extra zero by adding a +1 to the character count. The issue with zero POs is still persisting. 


  • 5.  RE: Adding data to new record with URL formula but having problems with order of operations

    Posted 07-23-2018 22:12
    I assume that you have a Summary field to count the number of children.  Is the field property on that field set to treat blank as zero?


  • 6.  RE: Adding data to new record with URL formula but having problems with order of operations

    Posted 07-23-2018 22:41
    In all of my panic, frustration, anxiety and 50 tabs open failed to review that simple "treat blank as zero" checkbox on the field properties. I am very grateful that you were able to point his out as it solved my issue immediately.


  • 7.  RE: Adding data to new record with URL formula but having problems with order of operations

    Posted 07-23-2018 23:00
    Te other option is to wrap a field that might be null with

    Nz([my field])

    The Nz() function will turn a null into a zero, else just the value it is, if not null.

    But I really try to avoid that as it clutters up the code.