Row Counter
Can anyone council me if it is possible to create a report formula to count and display the row number in a report? (I know Quickbase counts the number of records in a report, but can this counter b...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendThis can be done with a Formula Query but only if the short on the report is 100% predictable and you don't use any dynamic filters.
The only other way that I know of is to create a field that calculates to 1 called [Row]
then you need to contrive a summary report, which will actually break on every single record, and include that as a field to summarize on the report, and said that field to be displayed as a running total down the report.
So basically, the trick is to make a summary report which gives you all the rose and all the columns that you would have had on your table report.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
Mark is the undisputed guru so I'd trust his response more than mine. But I'll still take a crack at how I'd try this, even though I know it is not an efficient way to do it.
If your report works as you want, you could build a pipeline to recreate that same data set. Then, write that data set (your report data) to an empty table. That table would also need to include a field that does a record count and populates that field each time a record is added.
The formula I use for this field is:
Size(
GetRecords(
If(not IsNull([Record ID#]),"AND{3.LTE.'"&[Record ID#]&"'}")))
Warning: I am somewhat of a novice at Quickbase so don't bet the farm on my suggestion :)
------------------------------
Aaron B
ab1692@att.com
------------------------------
- MarkShnier__You
Aaron, but the problem will be that report sort is unlikely to be by Record ID# and the Formula Query will rank by Record ID# and it would include all records and not just the ones included in the report filter.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------ The above only works unfortunately until someone makes an adjustment or adds a filter to play with the data view at which point the value of the Row # would still be the same since its based on the entire data set, not on the data in the report. Mark's suggestion is the only native solution / loophole that QB can do, otherwise the only other option is to do a custom code page and you could do something like a API_GenResultsTable or REST API report run and then display it with an additional column inserted to show the row # custom.
------------------------------
Chayce Duncan
------------------------------Thanks everyone.
I use QB at my golf course for a zillion things including event scoring.
In this case, I was scoring a high school tournament in which we had to identify the low overall golfer (easy), and also the low four golfers from each school, calculate the sum of their four scores, then rank against the other schools.
My app has two tables. Parent is the list of schools, child is the list of competitors and where the hole scores are tabulated.
I wanted Quickbase to identify the four low players for each school on the child table and then I'd simply have a summary field on the parent table to tabulate each schools (4 person) team total then sort on this counter total and report.
I couldn't automate the task, so I simply added a checkbox to the scoring table, sorted the scores by school and gross score (with a tie breaking methodology) and checked the check box for the first four scores of each school. So the summary simply counted the check marks from each school.
Simple with my small data set.But it drives me crazy I can write a formula in an excel spreadsheet to automate this exact task in minutes, but on Quickbase, it's so complicated.
Jeff
------------------------------
Jeff Rogerson
------------------------------- MarkShnier__You
I think that you can do this with the relationships in summary fields. When school has many players. Do a summary of the minimum of the score.
I think that you can do this with the relationships in summary fields. When school has many players. Do a summary of the minimum of the score.
Look that up down to players. Call it best score #1.
New summary field of the minimum record ID with score = best score #1..
Call That Record ID of player with best score.
Look that up down to players.
Now calculate best score number two. This will be the minimum of the scores subject to the condition that the score is greater than or equal to best score number one, and the record ID is greater than record idea of player with best score number one.
then you continue on with the pattern above, which identified the record idea of the player with best score number to. Then you keep doing that until you identify the record IDs of all players with the best scores.Then you can summarize the total best scores for players wear. The record ID is equal to the record ID of best player scored number one or two or three or four.
You can also flag who are the players are because they record IDs will match the record ID of the player with best score number one or two or three or four.
I hope this is readable. It was mostly dictated on my iPad. Feel free to post back if you get stuck anywhere.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
