I have a table with tens of thousands of rows, and one non-unique column is ‘asset type’. I can group by asset type, then view the sums of other data columns by that grouping. That’s 80% of what I need!
Now I need to calculate several formulae based on those sums for each group. The simplest example is dividing two of the sums. For each formula I need to calculate and view one number per group.
How do I do this?
I’m pretty new to Airtable and still learning, but is there anyway to create a new linked table where each row contains those summary data from each group? Then I could simply add a formula field for this new table for each formula I need. That would do what I need perfectly.
I’ve looked a little bit at linked fields, but I don’t see how that would work for my case because there’s no way to do ‘automatic linking’ based on my ~1 hour of searching these forums (even though it’s frequently asked about and requested, which is disheartening).
Without automatic linking, I would have to manually go through each asset type (dozens), and for each one select all of the correct rows on the main table (tens of thousands of entries). I would have to make no mistakes, and I need to do this weekly!
That’s a complete non-starter, since the whole reason I’m looking into Airtable is to save my time not add tedious labor.
So, I want to echo the oft repeated request for automatic linking to the Airtable team. A lot of the other posts I see requesting it seem to be from database people. I’m not a database person (I can’t remember what foreign keys are, exactly). So instead I’ll just describe the kind of feature I want:
- Add a field type called “automatic link to another table” or something similar but distinct from the current linked field type.
- Have a condition widget or formula that defines which records from the other table get linked.
Since in my specific case there will be potentially thousands of automatically linked records, the display should probably be different than for manually linked fields, maybe defaulting to the count of items linked.
What about the condition?
For my project work flow, ideally I could somehow create a new “group table” which has a unique field “asset type” where each row is automatically linked to the main table records which have the same “asset type” (which is non-unique in the main table). But I am not aware of a
In my example for this post, I could manually create a row for each asset type (dozens), so that the condition could be ‘any record in the other table whose non-unique “asset type” field value matches this record’s unique “asset type” field value.’ Then I would have to manually create a record for each asset type, and the automatic linking would connect it to all of the main table records for that asset type.
