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

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered
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.







Photo of Lee Gilmore

Lee Gilmore

  • 762 Points 500 badge 2x thumb
  • inadequate and stuck

Posted 4 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,346 Points 50k badge 2x thumb
Len, can you post your code separately, as its not possible to read in a screen shot, nor copy paste to suggest fixes.
Photo of Lee Gilmore

Lee Gilmore

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

Lee Gilmore

  • 762 Points 500 badge 2x thumb
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. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,346 Points 50k badge 2x thumb
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?
Photo of Lee Gilmore

Lee Gilmore

  • 762 Points 500 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,346 Points 50k badge 2x thumb
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.