Rank Records Using the 'Size' Query Function
Oct 17, 2021Ranking, Ordering, Counting, or Numbering Quickbase records according to your own criteria (and not the Record ID#) is a not-so-easy task.
Until now!
With the "Size" formula query function, it's possible to do with just one formula.
And did I mention, NO relationships are required.
For example, you can use the Size function to:
- Rank records based on how their field value compares to other records in the same table.
- Number records based on a date, record ID, or numeric field value.
- Display the count of records on the same table with matching criteria.
- Create groupings of numbered records, such as "2 of 9".
- Rank the top # of records with remainder grouped as 'other' (great for bar and pie charts).
- Aggregate data from multiple tables onto one.
In this video, I'll demonstrate how to use the Size function to rank opportunity records according to an amount field when the opportunity status is not closed, with the highest amount ranked as #1.
In addition, I'll address possible issues stemming from duplicate/tied values.
There are 3 basic steps to writing a query formula:
- Identify the field IDs for your query (Amount & Status) and the table ID (if the formula is on a different table from your records).
- Write your query. Include field references in the query string as necessary. I prefer to write my queries in a variable to make them easy to read and troubleshoot. See Query Writing 101 for more information on query writing
- Create the formula. Use the Size and GetRecords functions, combined with any other formula functions/field references, to achieve the desired result.
RESOURCES:
Final formula from the video:
If the Status is closed, do nothing. Otherwise, get the Size of the record list where the Amount is greater than the current record's Amount and the Status is not equal to closed. Then add 1 to include the current record.
var text QUERY = "{7.GT.'" & [Amount] & "'}AND{14.XEX.'Closed'}";
If([Status]="Closed",null,Size(GetRecords($QUERY))+1)
Format if sizing records from another table: GetRecords($QUERY,"mytableid")
where mytableid is the 9 digit dbid that comes after the 'db/' in the URL bar. The table alias may also be used.
Query Writing:
More Formula Query Help:
Formula Writing Courses:
Feeling like a Junkie? Subscribe Now!
