Help

Count Sum of All Linked Entries

Topic Labels: Base design
Solved
Jump to Solution
8686 7
cancel
Showing results for 
Search instead for 
Did you mean: 
XtremeMaC
5 - Automation Enthusiast
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
XtremeMaC
5 - Automation Enthusiast
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.

See Solution in Thread

7 Replies 7

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.

XtremeMaC
5 - Automation Enthusiast
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.

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.

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.

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.

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

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.