How to count self linked records

[Ben_Foster]

I have a table with an Employee Name and Manager fields. The Manager field is a self-referencing link to the same table using the Name field.

e.g.

| Name  | Manager (linked) |
|-------|---------|
| John  | Sally   |
| Sally | Peter   |
| Jane  | Sally   |
| Peter |         |

What I would like to do is add a count of the direct reports of each manager. I’m aware I can see this if I group by manager but I need it as a field so I can aggregate and display the count using the org chart app. For example:

| Name  | Manager | Direct Reports |
|-------|---------|----------------|
| John  | Sally   | 0              |
| Sally | Peter   | 2              |
| Jane  | Sally   | 0              |
| Peter |         | 1              |

Is it possible to do this?

[Hannah_Wiginton]

Hey Ben!

Welcome to the community!

Yes, this is possible.

You will need to create another linked record field in your table back to that table (I know, I know!)

While Airtable is great with linked records to other tables, it does not create ‘backlinks’ in the same table.

In this example, Name is my Primary field, Manager is a linked record field back to the same table, and Backlink is a linked record back to the same table showing the connections.

(I also used this awesome script to automatically generate the backlinks in the Backlinks field or you can do it manually)

Create a Rollup field for your Direct Report field

Both of the Rollup fields inside the field need to be the Backlinks field

In the formula field of the Rollup, add

COUNTALL(values)

I haven’t used the Org Chart much, but here’s an idea of what it would look like with your Direct Report field.

[Ben_Foster]

Hi Hannah,

Thanks for your help. Unless I’m missing something wouldn’t that require me to provide values for both the Manager and Backlink fields, making it possible for these to become out of sync?

[Hannah_Wiginton]

Yes, when you add a new Name, you’ll need to add a Manager.

From there, you can then run the script each time you add a new name to have it ‘automatically’ link it. Or you add both the Manager and Backlink manually.

Unfortunately, automatically backlinking to the same table is not an Airtable native feature.

It’s why the Backlink script is useful if you have lots of data or want to ensure you don’t miss any backlinks.

[Ben_Foster]

Thanks, the script works well. Would be great to see native support for backlinks or the ability to count/sum over those relationships.

This gets me a count per manager. I was then hoping to aggregate this by team to get the size at each level of the org chart but I think that’s going to be a stretch

[Hannah_Wiginton]

Yeah, totally agree! I wish links in the same table was a feature!

Be sure to send Airtable support an email about wanting the feature.

Hopefully, you’ve got a good starting point to hash that out if it’s a need.

On2Air also has a Same Table Backlinks Sync function in our Actions app

[kuovonne]

Ben_Foster:

Unless I’m missing something wouldn’t that require me to provide values for both the Manager and Backlink fields, making it possible for these to become out of sync?

I have an automation script for maintaining the links/backlinks going forward. The automation script allows you to freely change the links and backlinks from either field (provided that you set it up to run for both fields) without worrying about manually maintaining them. I just haven’t had time to put the automation scripts up for sale on my Gumroad site yet.

[Hannah_Wiginton]

Awesome, @kuovonne!

That’s the 2nd time in a week that someone needed backlinks. An automatic one would probably be pretty useful.

[kuovonne]

@Hannah_Wiginton This thread prompted me to put up the automation scripts on my Gumroad store. I announced it in this post.

Here are links to the script and video demo .

[Hannah_Wiginton]

Awesome! Thanks for sharing.