Count Sum of All Linked Entries

Topic Labels: Base design
Solved
9262 7
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hello,

I’ve a Linked Table with many entries which are populated by this linking feature.
I’d like to add a field that counts all linked entries belonging to that entry.
All I could find was the build in Count feature, but it’s only applicable to 1 entry.
I’ve been trying different formula combinations, sum, lookup, count, counta. no success yet…
How to calculate this?

1 Solution

Accepted Solutions
5 - Automation Enthusiast

I’m using Formula Field.
I think I found a work around, which initially I found cumbersome, but I guess it’s currently my best option.
I created new field to count each Linked Field, then added another field to Sum these Count fields.

7 Replies 7
18 - Pluto

Are you using a formula field or a rollup field? You need to use a rollup field.

If you use the formula field, it will give you 0 or 1 depending on the formula you use.

5 - Automation Enthusiast

I’m using Formula Field.
I think I found a work around, which initially I found cumbersome, but I guess it’s currently my best option.
I created new field to count each Linked Field, then added another field to Sum these Count fields.

18 - Pluto

Do you have one field that links to multiple records, or multiple fields that link to multiple records?

If you have one field that links to multiple records, use a rollup field instead of a formula field.

If you have multiple fields that link to multiple records, use a rollup field for each of the linked fields (as above), and then a formula field to sum those counts.

5 - Automation Enthusiast

Ok so, I’ve a database of numerous parts with different parts categories and their suppliers in multiple tables. When I enter a part, I link it to it’s supplier.
Linked table is the Suppliers and this is pretty much automatically populated by AirTable per linking.
For each supplier entry in this linked table, I can see what category of parts they supply.
What I wanted to do was calculate how many entries in total a certain supplier has across all tables.
Sorry for not including this in my OP.

18 - Pluto

Depending on the structure of your base, the workaround that you found might be your best option.

My advice might not quite match your situation as I don’t quite know how you have structured the tables and fields in your base.

For example, suppose you have 5 categories of parts, each in its own table. Each category of parts would have one row per part. Then your [Suppliers] table would have 5 linked-record fields, one for each table of categories. You would also have 5 `COUNTALL` rollups, one for each category. Then you would `SUM` the rollups in a formula field.

It sounds like this might be your situation and the workaround that you found.

On the other hand, suppose you have one [Parts] table, with one row for each part. Then you have a [Categories] table with one row for each category. Each part links to one category. Each category links to multiple parts. Finally, you have a [Suppliers] table with one supplier per row that links to multiple categories. In this case, you would have a {Count of Parts} rollup field in the [Categories] table that counts all of the parts in the category. Then in the [Suppliers] table, you would have a rollup that sums the {Count of Parts} to find the total number of parts across all categories.

The second situation assumes that all suppliers have all of the parts in a category, not just some. If suppliers have only some parts within a category, there would have to be a different data structure.

So, you can see that the solution really depends on the structure of the data.

17 - Neptune

Two words - Script Block (and then all these other words to satisfy the need for more words to make a post).

5 - Automation Enthusiast

Thanks. I’m currently on free version and during trial I didn’t see scripting.
However, now looking into the videos and screenshots, I think that it’d add unnecessary complexity for my current use scenario and force me to upgrade, which again is not something I need now. But thank you for showing me that it exists.

I’m going to stick with my workaround for now.