Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 26, 2020 03:51 PM
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?
Solved! Go to Solution.
Feb 26, 2020 04:06 PM
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.
Feb 26, 2020 04:03 PM
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.
Feb 26, 2020 04:06 PM
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.
Feb 26, 2020 04:14 PM
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.
Feb 26, 2020 04:31 PM
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.
Feb 26, 2020 09:52 PM
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.
Feb 27, 2020 05:54 AM
Two words - Script Block (and then all these other words to satisfy the need for more words to make a post).
Feb 27, 2020 01:27 PM
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.