Rollup / Aggregate by 2 fields?

App Development
1

Hey,

I have the following tables:

  • A Teams table, with 13 records that express a team number (1-13).
  • A Candidates table, where each Candidate record has a connection field to a single Team from the Teams table (Candidate X → Team 1). Each Candidate record also has a number field named “Ranking”, with an integer value between 1-6.

In the Teams table I have a rollup field that counts the number of Candidate records that are connected to each Team record (i.e. Team 13 has 10 Candidates connected to it).

Now what I want to do is to either use an existing table, or create a new table, where I’ll have the following fields:

  • Team number (connected to Teams table)
  • Ranking (1-6)
  • Number of Candidates connected to this Team number with this Ranking (i.e. if Team number is 13, and Ranking is 1 - the third field will count how many Candidate records are connected to Team 13, and also have a Ranking field of 1).

The main issue here is that any rollup / formula field can only aggregate based on that one connected field (in this example - the Team number), but it can’t join on both the Team number and the Ranking value.

Any ideas?

2

Here’s an idea

In the Teams table create 6 x Rollups (one each for the 6 Rankings) and in each count rollup ‘Add data conditions’ to look for the specific Ranking

Would that work for you ?

3

Hey, that works data wise, however I don’t want to display a table with 6 columns for each team, since most of the columns will be 0. For team 1, they’ll have data only in two of these roll up fields (for example 1-2), and in team 2 they’ll have 2-3… all the rest will be 0s.

The solution I found is using only a pivot table component

4

Ha
I was going to suggest a pivot table, but when you said you wanted to create a table I assumed a data table.

Glad it works for you though.