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 |
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
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?
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.
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
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.
Additionally, Ben, we have a Same Table Linked Records Sync function that’s part of the On2Air Actions app. It automatically creates backlinks in the same table.
The Actions app also includes an additional 60+ features that integrate with Airtable.
Features like creating Google Docs automatically with Airtable data, default field values, financial calculations, compare field data, Google Sheets record and table sync, and more.
