Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Re: Counting the Number of Children for Each Mother in the Same Table

310 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Tejaa
5 - Automation Enthusiast
5 - Automation Enthusiast

I have a table in Airtable with two fields: "Child" and "Mother" (just an example to explain the structure easily). The "Mother" field is a linked record to the same table. Here’s a sample dataset:

ChildMother
RaviSneha
KaviSneha
BalliPrerna
MalliPrerna
SnehaMother
PrernaMother

I want to add a new field, "Number of Children", that counts how many linked records (children) each mother has. The expected output should look like this:

ChildMotherNumber of Children
RaviSneha0
KaviSneha0
BalliPrerna0
MalliPrerna0
SnehaMother2
PrernaMother2

I tried using a Rollup field with COUNTALL(values), but it counts each linked record separately instead of summing up the total number of children for each mother.

How can I achieve this without creating another table? Any suggestions or workarounds would be greatly appreciated!

8 Replies 8

The easiest way to deal with this would be to separate the mothers and children into two tables and use a Count field

If not, you could try using an automation that triggers whenever the 'Mother' linked field is not empty, and its action would be to:
1. Find all records linked to the 'Mother' record that was just linked to the child
2. Update the 'Mother' record that was just linked to the child with the 'Length' value of the previous step

 

Hi, thanks for the reply, but sadly it doesn't work. The count field is showing "1" for all the fields. 

Moreover, I want to keep them in the same table so that I don't eat up the free usage limitation of 1000 records. 

ScottWorld
18 - Pluto
18 - Pluto

The best way to handle this would be to create backlinks (i.e. reverse links) within the same table. But unfortunately, this is one of the biggest flaws in Airtable. Airtable doesn’t offer automatic backlinking within the same table. Airtable only offers automatic backlinking when it’s a different table.

I would definitely submit this as a feature request to support@airtable.com, but people have been requesting this for 10 years now, and no improvements have been made on this front.

Your only 2 options for backlinking within the same table would be to:

(1) Write a custom JavaScript to handle this for you. Two scripts that do this are available here and here, but they are old scripts, so I don’t know if they still work anymore.

or

(2) For a no-code way of doing this that doesn’t require writing any scripts at all, you can create your own custom automation to create your own backlinks. You may be able to do this with Airtable’s automations, now that they have added the ability to “find records” in their automations. However, I often recommend that people try using Make’s advanced automations for Airtable, which gives you much more power & control than Airtable’s automations.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld 

If you've got two separate tables the Count field works fine?

Screenshot 2025-02-07 at 10.30.52 PM.png

Also, the 1000 record limit is a count from your entire base, not per table, and so keeping it all in one table won't make a difference to the limit either I'm afraid

Since you are trying to keep under 1000 records, you must be on the free plan.

Scripting options won't work for you because scripting is not available on the free plan.

Automations probably won't work for you because you are limited to 100 automation runs per month. 

Using two tables probably won't work for you either because it looks like you are trying to create a pedigree table. Normally with a pedigree table I would recommend creating another same-table link as a backlink and using an automation script to keep the backlink up to date and use a rollup field to get the number of children. (I sell such a script.) However, since you have access to neither scripting nor enough automation runs, that won't work for you. By the way, in a pedigree table, I recommend calling each record a "Person" and not a "Child", as a person could be both a child and a mother at the same time.

You could see how many children each child/person has  by grouping by the {mother} field and seeing the record count of the group in one view. You could then open a second window and create view 2 that is sorted by the child/person field. This way the mothers are sorted alphabetically in the grouped view, and the children/people are sorted alphabetically in the second view so it is easy to match up the two. Then you have to manually type in the number of records in the group from view 1, into the corresponding record in view 2. This method is a lot of manual work and you would have to repeat it periodically to keep the data up to date.


Tejaa
5 - Automation Enthusiast
5 - Automation Enthusiast

@ScottWorld @kuovonne @TheTimeSavingCo 

Thanks everyone for the reply. Automation seems to look a bit complex and then there is a monthly limitation. Sadly I don't have access to script functionality either in the free plan.

Creating another table looks like the only way. Any idea how should I process, if I want to break my pedigree table into two tables(Table-Child & Table-Mother) without any data loss?

Also, one more important thing, some mothers can have a mother too. For example, Here Sneha is a mother with 2 children and has a mother called Maha too :

PersonMother
RaviSneha
KaviSneha
SnehaMaha
MahaMother

 

A two table system would be horrible to maintain for a pedigree table. People would need to appear in both tables. It makes my head hurt just thinking about it.

If you are limited to the free version of Airtable, I think that the best system is to keep one table and manually maintain the same-table backlink field whenever you do any sort of data change.

Yes, you could do it with 2 tables, but each person would need to show up in each table (as @kuovonne mentioned above). So you would need to make sure that each person appears in both tables. That actually could be potentially easier to setup than maintaining backlinks. 

- ScottWorld, Expert Airtable Consultant