Creating Sequential Numbering Unique to your Business Powered by Formula Queries

Have you ever wanted to be able to create a custom numbering sequence for records in your app (see [PO Number])?

Common use cases where this comes up include creating purchase orders, invoices, tasks, budgets, etc. Quickbase has had a variety of complex and multi-step solutions to create these in the past (some combination of summary fields, lookup fields, snapshot fields, and Pipelines), but with formula queries, we are able to drastically simplify the process. In the article below we talk about how you can use formula queries to implement a system of your own.

Use Case:

Jesse's app has five tables: Vendors, Items, Vendor Items, POs, and PO Items. For the sake of this solution, we will be focusing on the PO table specifically.

On the POs table we have:

Step 1:

Note: We are using variables to make the final formula easier to read

• • var number ponumbertrue

    • "var" tells Quickbase you are starting a variable
    • "number" establishes the result type of the variable (numeric result)
    • "ponumbertrue" is the name we will use to reference the variable later in the formula (this value can be anything you want it to be)
  • Size()

    • A new function available in the Formula Queries Beta, which counts the number of values in a recordlist, textlist, or userlist data output, gives us the number of records returned in the query – always a numeric result
  • GetRecords()

    • A new function for using Formula Queries creates a list of records (recordlist) of all the records returned within the query
  • The Query (to learn more about Query structure and basics click here  – each PO should be sequential based on the number of POs for that year for that vendor
    • "{9.EX.'"&[Related Vendor]&"'}AND{11.CT.'"& Year([Order Date])&"'}"&If(not IsNull([Record ID#]),"AND{3.LTE.'"&[Record ID#]&"'}")

"{9.EX.'"&[Related Vendor]&"'}
AND
{11.CT.'"&Year([Order Date])&"'}"&
AND
If(not isnull([Record ID#]), "AND{3.LTE.'"&[Record ID#]&"'}")

• • First Query String: "{9.EX.'"&[Related Vendor]&"'}…
    • We want records that have the same [Related Vendor] as the record on which the formula is evaluating
    • Find records where field ID 9 ([Related Vendor) is exactly equal to this PO's [Related Vendor]

AND where…

• • Second Query String: "{11.CT.'"&Year([Order Date])&"'}&…
    • We want records that are from the same year as the record the formula is evaluating on
    • Find records where field ID 11 ([Order Date]) contains the same year as this PO's own [Order Date]

• • Third Query String (conditional on a record ID# existing): If(not isnull([Record ID#]),"AND{3.LTE.'"&[Record ID#]&"'}")

AND where…

• • • We want the records that precede or are, the record on which the formula is evaluating
    • Find records where field ID 3 ([Record ID#]) is less than or equal to this PO's [Record ID#]

Note: the third query sting is conditional because the formula won't correctly evaluate until a record ID# exists. Making it conditional allows us to forecast what the PO Number will be prior to saving by querying just on a year and related vendor.

Step 2:

Vendor Name, the sequential number, and the last two digits of the order date's year

• • [Vendor Name] &"-"& $ponumbertrue &"-"& Right(ToText(Year([Order Date])),2)
    • We call the formula query variable using the "$" and the variable name
    • Then we need to take the last two digits of the order date's year using the Right() function which requires us to convert the Year([Order Date]) to text since it outputs a numeric value

Step 3:

Enjoy the result.



[PO Number] is our formula, and you can see how the number in the middle increases sequentially based on the order the POs were entered ([Record ID#] shows you the order they were entered).

Note: Please be aware that if you delete a record, the formula will adjust to the new number of records found by the query. An easy solution is to create a checkbox or multi-select text field that users can select to "delete" or "archive" a record that removes it from their permissions.