Forum Discussion

AdrianLloyd's avatar
AdrianLloyd
Quickbase Staff
4 years ago

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 is responsible for managing inventory for a wholesaler at one of their large warehouses. Jesse does a weekly audit of the on-hand quantity of the different items they sell, and she is responsible for placing purchase orders to restock. The company has a specific numbering process for their POs; each PO should be sequential based on the number of POs for that year for that vendor. For example, Jesse has already placed 3 orders with Vendor A for the year, so the next PO should automatically adjust to be number four for that Vendor. Jesse's format looks like this, Vendor A – 4 – 21. Currently, Jesse is manually creating these PO Numbers.
Let's help Jesse remove her manual process and create a formula to automate the custom numbering.
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.



We know Jesse's end goal is to create a format that lists the vendor's name followed by the sequential PO number and then the last two digits of the year.
Vendor Name – Sequential Number – Last two digits of the year


On the POs table we have:

Field Name

Field Use

Field ID

(needed for the query)

[PO Number]

1.  The formula field

 

N/A

[Vendor Name]

1.  Used in the PO Number Formula

N/A

[Order Date]

1.  Used in the query to identify records in the same year

2.  Used in [PO Number] to identify the last two digits of the year

11

[Related Vendor]

1.  Used in the query to identify records from the same vendor

9

[Record ID#]

1.  Used in the query to identify the order in which a record was created

3

 

Step 1:

Write the query variable to identify the number of records that precede the PO being entered where the [Related Vendor] is the same, and the year of the [Order Date] is the same.
Note: We are using variables to make the final formula easier to read


Let's break this down.
    • 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#]&"'}")

Note: The colors align with the descriptions below

    • 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:

Concatenate the rest of the formula to make Jesse's custom numbering system.

Let's remind ourselves of the requirements:
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.

Now you can experiment with building your own custom numbering formula with this as your guide!