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

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. 

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?

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.

@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 :

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