Sorting after grouped

[Erich_Sagers]

Is there a way to sort by the count field after you’ve applied a grouping? I have items that are grouped by sales reps and the grouping header gives me a count and I want to sort the groups by count.

[Tuur]

Nope. You need to add the count in a formula and group using that. :winking_face:

[David_Albert-Le]

I have a similar issue where I would like to sort groups based on their summary statistics. Is there any way to do that?
I don’t think the above suggestion works in my case.
The sorting only seems to act on the individual rows, which is nice but doesn’t help me in figuring out the winning team.

Any suggestions?

[Jeremy_Oglesby]

You’d have to use Rollups on conditional fields in a separate table to do what you are wanting.

In the table you posted the screenshot of there, you’d have to create a formula field that says:

IF(School = "McMaster University", 1, 0)

and one of those fields for each school.

Then you’d have to link each record to a “Totals” table, and in that table you’d make a Rollup for “McMaster University” that rolls-up the field noted above and says:

SUM(values)

again - a new rollup field for each school.

You can then pull those SUM values back into the original table with Lookup fields - one for each school. Lastly, create a formula field in the original table that chooses the value that corresponds with the school for the given row:

IF(School = "McMaster University", {McMaster University Sum Lookup}, IF(School = "University of Guelph", {University of Guelph Sum Lookup}, ...etc

You can sort on that last formula field now.

[W_Vann_Hall]

I’m suffering a little brain-deficit at the moment, so I can’t put it into words (or Airtable code), but it seems there should be a way to accomplish the rankings @David_Albert-Lebrun wants without having to add so many additional fields. And I don’t mean by using one of those vertical-to-horizontal remapping schemes I’m so fond of — you know, the hammer that makes everything look like a nail to me? I simply mean it feels like there should be some sort of rollup formula-based solution that could implement the functionality you describe, only in a slightly tidier manner. Right now, though, I’m so tired I’m not even sure which end of the Airtable one is supposed to blow into; I’m toddling off to bed and will see if anything makes more sense when I awake…

[David_Albert-Le]

Wow thank you so much for your response @Jeremy_Oglesby , that’s amazing!
I could be mistaken, but unfortunately I don’t think that works for me because my table is built around competitors, not schools. The screen shot I sent is a specific view of me trying to organize the competitor list to show the winning schools.

But now I wonder if I could create a school table with a field that connects to all competitors from that school. Then write a function to sum the results of those students. The catch is that a school team is comprised of only the top three scores for the guys and the top three scores for the girls. Is there a way for a function to access info from another table, from a specific field only IF school = ‘name of school’?

Thank you for all your help and sorry about all the questions, I didn’t think this would be so complex. I really hope I can get this to work, I would hate to have to go use excel :stuck_out_tongue_winking_eye:

[Jeremy_Oglesby]

I think my suggestion would work - in fact, it works precisely because “school” is a field in the table. Give it a try, because at the very least it will help you to see how Rollups work, and perhaps an idea will come to you to improve on my suggestion! :grinning:

And no, there’s not a direct way to filter lookups and rollups, which is what my suggestion is trying to accomplish, only in a round-About way.

[David_Albert-Le]

Thanks, I did try it and it was very interesting to see the Rollups at work.
Unless I did something wrong, your method essentially seems to tally up how many competitors are in each school. That last function then adds that total number of competitors to each competitor, so if McMaster has 7 people total, then bob from McMaster will then have a field that shows 7 attributed to his name. Sorting by that produced the same results as in my view that I originally shared.

However, it seems to me that if I create a new table listing just the schools, then have a field that links the competitors for that school, and lastly use a Rollup field that sums the final results for those competitors, I am good to go. Last hurdle is to show the total with only the top three highest scores for the men and top tree scores for the women… that would be perfect, but it doesn’t seem to me that there is another option to specify such a thing.

[Jeremy_Oglesby]

Ah right - sorry - you are sorting by some sort of score, not the total # of records. So try changing the formula to this:

IF(School = "McMaster University", {Score Field}, 0)

Then do the same rollup I mentioned in my previous post (all the same steps) - now it will pull the total SCORE for the school into the record for each person in that school, and you can then GROUP on THAT TOTAL SCORE Field to have people grouped by school still (since everyone from the same school will have the same Total Score for the school in that field), but sorted by score highest-lowest.

After grouping by Total Score, you could do a second grouping by school, so that if two schools happen to have the same Total Score, they are still separated within that score group.

[David_Albert-Le]

Well thank you, this does appear to work so long as I do not group the items. As soon as I do that, it takes over any sorting. Sorting is then done within each group and the totals are “ignored”.