Summarize information from a base table

[diegoaguilar]

TLDR;

From an “events” table that hosts records from different events I need to determine the highest value in two columns grouping by an event_id.

So being events referring to football matches, the highest value would correspond to the last record with source_id 't3_nk5nz4'.

What I want to achieve

A single row with a coulmn that shows what which is winner team or it’s a draw.

What I’ve tried

• Database view
• Grouping
• Formula
• Linking tables records

What’s worked best and what’s missing

Linking records from events grouping by match_id

Missing is going over the different events, taking last considering there’s a timestamp field and comparing the two relevant columns to set a winner or a draw.

[Kamille_Parks]

Start by taking a look at how something very similar can be accomplished in this video: BuiltOnAir Podcast Season 8 Episode 2 - YouTube

The method shown in the video involves linking records together as you’ve already done, and then adding Lookup or Rollup fields with conditions applied to return the linked records with the highest value.

[diegoaguilar]

Hi! @Kamille_Parks thanks for the support.
I have a problem with linking fields.
What i want is know how to link the fields local_team_goals and visitant_team_goals to another table, something like i have in this table idea what i create:

Then i want to rollup them to add a formula and know which team was the winner. Something like:

So, in the example tables even i haven´t the fields linked right so again, what i don´t know and i want to know is how link my fields right.

[Kamille_Parks]

Your first post seemed to have a perfectly acceptable linking structure, go back to what you showed there. Each [Event] record links to a record in [match_id]. [match_id] should have the following Rollup-type fields:

• name: {local_team}, aggregation: ARRAYCOMPACT(ARRAYUNIQUE(values))
• name: {visitante_team}, aggregation: ARRAYCOMPACT(ARRAYUNIQUE(values))
• name: {local_team_goals}, aggregation: SUM(values)
• name: {visitante_team_goals}, aggregation: SUM(values)

Then if you just want to see which column has more goals a simple formula field, still in the [match_id] table, could do it:

IF(
   AND({local_team_goals}, {visitante_team_goals}), 
   IF(
      {local_team_goals} = {visitante_team_goals},
      "draw",
      IF(
         {local_team_goals} > {visitante_team_goals}, 
         {local_team}, 
         {visitante_team}
      )
   )
)

In the Events] table, the {local_team_goals} and {visitante_team_goals} fields should be Number-type fields not Single line text or Link to another record.

[diegoaguilar]

Thank you @Kamille_Parks! This really has been a lot of help, i ​still have a couple of questions though
1 - what is ‘values’? what exactly value should I put for this param, and if the ‘values’ params are the number coming in, for example from local_team_goals , so how could I refer to these data/param?
2​ - How can I get the linked fields on a new table?
When linking fields with match_id they’re generated on the same table, but I’d like to get them on a different one.

[Kamille_Parks]

@diegoaguilar

1 - what is ‘values’? what exactly value should I put for this param, and if the ‘values’ params are the number coming in, for example from local_team_goals , so how could I refer to these data/param?

In a Rollup field, “values” is just a placeholder word Airtable uses. You don’t change or edit anything, just type SUM(values).

@diegoaguilar

2​ - How can I get the linked fields on a new table?
When linking fields with match_id they’re generated on the same table, but I’d like to get them on a different one.

How do you mean? In your first series of screenshots it looks like you already have a table just for match ids. What needs to change?

[diegoaguilar]

Oh sure, i made that table just as reference to give an idea of what i look for. In theory that’s exactly what i mean but the fields are not linked of a right way.

Then, if i follow the steps that you describe i get something like:

so, in orange is remarked the field that is created in the same table, now my question is how i could send that new fields linked to match_id that are created there to other table as the reference table.

[Kamille_Parks]

In your first screenshot you have two links to the Events table, you only need one. Make sure your two lookup fields are pulling from the {events} field not the redundant {events 2} field.

Your second screenshot doesn’t have a match_id table.

To put this topic to rest, just copy this example base I made.

Sports Project - Airtable