I don't see a way to create a hierarchy from a table that just shows employee and manager.

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Answered
Is there a way to handle the recursion for any number of levels, so I could have one table that has: 

employee   manager
bill              mary
mary           jane
jane            bob

and from there I can make sure that jane can see the performance review of bill, for example.  How can I start with the above table, with any number of relationships, and end with a table that has:

employee   manager1   manager2   manager3
bill              mary           jane             bob

(As an aside, I have exactly the same problem with serial numbers on parts, that roll up to assemblies with a new serial number, but those assemblies roll together into larger assemblies with a new serial number, with N levels.)

All help greatly appreciated!
Photo of David DeGraaff

David DeGraaff

  • 90 Points 75 badge 2x thumb

Posted 5 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,306 Points 50k badge 2x thumb
A table can be relation to itself. So 1 Manager May have many employees. Except that the managers are actually Employees.

So then the actual relationship is that 1 Employee have many Employees. The field that the system will create called [Related Employee] should be renamed to [Manager] and then it should make sense.

As for Jane seeing Bill, who is two levels down from her, I think that is possible to do.

But having said that, I’m not sure it’s intuative for users when set up that way. There is an arguement for setting it up more traditionally, where each employee has 3 slots called Manager Level 1, Manager Level 2, Manager Level 3.
Photo of David DeGraaff

David DeGraaff

  • 90 Points 75 badge 2x thumb
Thanks for the clue.  I'll see how far I can get.  Note that the intuitive way you're suggesting doesn't work well with more people.  For example, if I move a group to a new manager, it's easy to just update all their managers, but not easy to update everyone all the way up the chain from their new manager.  And when a middle manager changes managers, you need to find them all over the place and update their reporting chain.  It doesn't scale.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,306 Points 50k badge 2x thumb
I agree. It depends on the required scale. If you have over a few hundred employees, then recursive is the way to go.
(Edited)
Photo of Debbie Taylor

Debbie Taylor

  • 604 Points 500 badge 2x thumb
Great to see your name again, Dave.
Definitely do the recursive table-to-itself relationship.
Just rename fields so your users don't get lost.
And, give us a holler at www.cloudbaseservices.comhttps://community.quickbase.com/quickbase/topics/www.cloudbaseservices.com
Photo of David DeGraaff

David DeGraaff

  • 90 Points 75 badge 2x thumb
Thanks Debbie - I very well might.  I hope you're doing well!
Photo of Debbie Taylor

Debbie Taylor

  • 604 Points 500 badge 2x thumb
A few more thoughts:
  • You should only build one recursive relationship (eg employee to manager).  For additional reporting, add look-up fields (eg second level manager.)
  • When you need to change many records, create a report with the records.  Use the "More" button in the upper right hand corner of the screen to access the "Search and Replace in this report" feature.  See below.
  • Consider getting an extract from your company's HR system OR, better yet, integrate with it.  Some changes can then be automated.