I'd like to be able to run a report that shows just the top 20 results. For instance, customers - sorted by volume from largest to smallest, only returning the top 20. I can't seem to figure out how to limit the results - i'm getting every customer in the system. Any help?------------------------------Thanks,Chris Newsome------------------------------

You can use a formula query with the Size function in a report formula to evaluate the field containing the volume data to check if it is greater than or equal to the volume. The size will return how many records you have by volume and assign a numeric value. You a filter the report to only show records where size is less than or equal to 20. However, if you have many records that equal the same volume you will have many records with the same size value.

I've never done that before... can you give me an example of how that would look?------------------------------Thanks,Chris Newsome------------------------------

In this video, QBJ shows how to use Size to rank records. It should be done as a report formula in the settings of the Table report. However, if you are dealing with a large data set you may encounter some performance issues. This is why it's suggested that you do it in a report formula vs a formula field at the table level. If there is a performance issue it will only impact that report vs the entire table. If performance is a concern you should look into alternate options. Perhaps a pipeline that will query the top 20 and copy the records to a reporting table and each time it runs it refreshes the table. Rank Records Using the Size Query Function in Quickbaseyoutube.com

This works kind of OK. It's still not really doing what I want it to do. I can't understand why it's not an option to just limit the # of results to X.------------------------------Thanks,Chris Newsome------------------------------

Here is my formula: (64 is the FID for "Total Won Value (YTD)" and 62 is the FID for "Territory") var text QUERY = "{64.GT.'" & [Total Won Value (YTD)] & "'}AND{62.XEX.'Knoxville'}"; Size(GetRecords($QUERY))+1 Here's the results I'm getting: As you can see it's not sorting by that field at all. Also it returned 32 results for #s up to 15 and none of the numbers match.------------------------------Thanks,Chris Newsome------------------------------

Limit # of records in a table report

MannyPinheiro
Quickbase Staff
10 months ago

You can use a formula query with the Size function in a report formula to evaluate the field containing the volume data to check if it is greater than or equal to the volume. The size will return how many records you have by volume and assign a numeric value. You a filter the report to only show records where size is less than or equal to 20.

However, if you have many records that equal the same volume you will have many records with the same size value.
- ChrisNewsome
  Qrew Captain
  10 months ago
  I've never done that before... can you give me an example of how that would look?
  
  ------------------------------
  Thanks,
  Chris Newsome
  ------------------------------
  - MannyPinheiro
    Quickbase Staff
    10 months ago
    In this video, QBJ shows how to use Size to rank records. It should be done as a report formula in the settings of the Table report. However, if you are dealing with a large data set you may encounter some performance issues. This is why it's suggested that you do it in a report formula vs a formula field at the table level. If there is a performance issue it will only impact that report vs the entire table. If performance is a concern you should look into alternate options. Perhaps a pipeline that will query the top 20 and copy the records to a reporting table and each time it runs it refreshes the table.
    
    Rank Records Using the Size Query Function in Quickbase
    youtube.com

Forum Discussion

Limit # of records in a table report