How to rollup records in different branches of an Org chart?

Topic Labels: Formulas
609 3
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

I want to assign a value to each record based on the 2nd level record it is related to in the org chart. This question is similar to the question posed here but instead of adding salaries, I want to have a column where everyone under “Tamara Muro” is assigned “Tamara Muro”, everyone under “Keith Shearin” is assigned “Keith Shearin” and so on. One of the commenters suggested using a script but I wouldn’t know where to get started with that. Is there not a way to use a lookup with a looping function to keep climbing the org chart until it lands on one of these names?

I also tried this solution but with all the levels in my org chart, this isn’t sustainable.

Thank you for your help!

3 Replies 3

Hi Anna, I’ve attempted to figure out how to do this with lookups and rollups, but it feels like the main issue is the records aren’t directly linked, and so lookups and rollups wouldn’t work (as far as I can tell, anyway!)

I’ve put together a script that accomplishes what you’re looking for that can be found here

It’ll ask you to select the following:

  1. The table where the records are
  2. The field that you want updated with the “Assigned” data
  3. The field that contains the links
  4. The field that tells the script where to stop (I added this as, without it, I figured everyone would end up being assigned to the CEO or something)

Let me know if I can help with the implementation into your base!

Hi Adam, thanks for the helpful script. What I’m wondering is how I might be able to also include a column that displays the “level” of the hierarchy for each record; for instance, in your example, if there were a “Level” column, “A” would return “1,” since it doesn’t have a parent, and “I” would return “5” since the branch is I->H->G->B->A. I figure it would involve a counter within the loop, but I’m a newbie and could use some help with syntax.

Thanks for your help!

Hi Juliet! I’ve updated the base to handle the case you’re talking about, do have a look when you’re free