links and rollups: howto

[astrakid]

hi,
i have set up teable successfully and do my first steps. i successfully created a small match-list for a tennis doulbe tournament.
i have the tables "players", "teams" with links to the players (each team has two players) and a team name. this team name is linked in table "matches". the result is entered in table "matches" and i calculate the winner based on the subresults (each tennis-set is entered). in column "winner" on table matches the winner-team is shown, which is the field which is linked to the teams-table.
now i want calculate the ranking based on the wins. and i don't get it to work.
i tried to link a field in "teams" based on "winners" and do a rollup on that field. but that doesn't seem to work.

any ideas to realize that?

[tea-artist]

Hi,

1. First, create a Link field in the Teams table:

   • Add new field
   • Choose Link type
   • Link to the Matches table
   • Select "Has many" relationship (one team can win multiple matches)
   • Choose the winner field from Matches table as the Reference field

2. Then create a Lookup field to count the wins:

   • Add new field
   • Choose Lookup type
   • Select the Link field you just created as the reference
   • Choose Count as the Aggregation method

After this setup, the system will automatically count how many times each team appears as a winner, which gives you the number of wins.

With this wins count field, you can:

• Set sorting rules
• Create a ranking view
• Add additional calculations (like win rate)

Please let me know if these steps work for you or if you need any further assistance!

[astrakid]

thx a lot for your effort.

• when chosing "link to another record" i have no option to chose a field. i have to use "lookup" to select the field.
• in lookup-field i can't chose any aggregation method. this can be done with rollup. but rollup is not usable on a lookup field.
  so what did i do wrong?

[tea-artist]

Yes, Link/Lookup/Rollup fields work in a different case:

1. Link field:

   • Primary purpose is to maintain relationships between records
   • Always displays the primary key (first column) of the linked record
   • Serves as the foundation for Lookup and Rollup fields

2. Lookup field:

   • Built on top of a Link field
   • Allows you to reference specific fields from the linked record
   • Shows the raw values without aggregation

3. Rollup field:

   • Built on top of a Link field
   • Designed for data aggregation
   • Provides various aggregation methods (sum, average, count, etc.)

To solve your issue:

1. First create a Link field to establish the relationship
2. Then create either:
   • A Lookup field if you need to reference specific fields
   • A Rollup field if you need to aggregate data

This is the intended design - you're not doing anything wrong. Each field type serves a specific purpose in this hierarchy.

[astrakid]

i don't get it to work.
in my table "matches" i have a field, which is a script to determine the winner of the match. might this be an issue? because this is the base for calulate the total wins for the ranking.
if({SetsWonT1}>{SetsWonT2},{Team1},if({SetsWonT1}={SetsWonT2},"offen",{Team2}))

matches -> field "sieger" (german term for "winner"), javascript which fills the field with team name of the winners, e.g. "John / Jim"

in table "Teams" i have field "Matches" linked to "Matches" - i get all results, where this team is part of a "matches-row", but not referenced to my "Winner" field.

[astrakid]

[astrakid]

unfortunately i lost a field in matches table, so i need to rebuild everything and make some further screenshots. will take some time...

[astrakid]

so here is a screenshot of the matches table. the first row should be enough for further discussion:

J*******ld is the computed winner team and shown under "winner".

thsi is my teams-table:

now i want to count the rows, where each team is listed as winner in "matches"-table.

[astrakid]

the 3rd row in teams-table should give "2" at this moment. but i don't have a reference to the "winner" field yet.
is the problem, that my "winner" field is not the label of table "matches"?

[tea-artist]

I think I get the key point now. The winner field needs to be a Link field rather than a computed field as it currently is. Since the current winner is calculated, it doesn't establish a proper relationship with the teams table.
There are two possible solutions:
Make winner a Link field where users manually select the winning team.
For Enterprise Edition, utilize the automation feature to listen for changes in the computed winner result and automatically update a separate Link field to establish the relationship
This way, we can properly establish the relationship between matches and teams tables, making it possible to count wins using rollup aggregation.

[astrakid]

thanks for the clarification. the proposed workaround isn't acceptable here. so i have to find another way.
regards,
andre