Summarizing linked records in a lookup field

[vancouverism]

Hello! I'm trying to figure out how to count the number of times that a linked record appears within a cell in a lookup field, but I'm completely stumped.

My base contains information about hockey leagues and clubs. There are several tables, but the important ones for this problem are:

• Leagues: Each record represents a different hockey league.
• Clubs: Each record represents a different hockey club.
• Seasons: Each record represents an individual season played by each league.

In the Leagues table, I have a lookup field that links to the Seasons table. This field (League Champions) contains a club name from every season, with some clubs appearing multiple times (e.g., the club Färjestad BK appears 9 times).

I would like to be able to use this in an interface for each league, where I can use an element to create a table/list that shows how many times each club won that league (e.g., Färjestad BK were league champions 9 times, Växjö Lakers HC were league champions 4 times). Alternatively, I would even be okay with a formula field that returns a list (e.g., returning something like "Färjestad BK (9 championships), Växjö Lakers HC (4 championships)"). I am struggling to figure out a way to make either of these happen.

To make this slightly more complicated, there are some records in the Clubs table with identical names (e.g., there are 3 clubs called EV Füssen but only one of them won the top-division league championship).

If anyone has some ideas, that would be wonderful!

[Sistema_Aotearo]

Hello! I have some ideas on how to do this. Try using a Dashboard type interface. Using the different graphs you may be able to manipulate the conditions you are looking for. Rather than a count, you could summarize a field.

Also, having a "Count" field for each club might be helpful. That way you have the raw count data on the table somewhere. You can hide the fields and have them as background info to help build displays on your interface.

[vancouverism]

I actually do have a few count fields in the Clubs table, which let me make these nice summaries for championships won and games won in an interface (you can see these in the screenshots). The only issue I have with that is it's a bit blunt: a lot of the clubs in this base have played in multiple leagues over the course of their history. Do you think it would be possible to have some kind of button or drop-down menu to filter the data based on a specific league (e.g., click a button to only show championships and games won in the Deutsche Eishockey Liga)? Or maybe group the championship seasons by league?