Create a Company Hierarchy by relating a table to itself

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have been away from QB for quite a few years... and trying to remember a lot of it. Is there a way to create and maintain a manager table based on an employee table? Basically create and then append when needed... I want to create an associate hierarchy table without having to maintain 3 different tables: associate/district/regional levels
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
You can do this with one table, but the self relationship is tricky to set up the right way.  But you will be able to have Regional levels with multiple District levels, and multiple associate levels.

All of the people will be stored on one table, so you can do all of your on-boarding documents, payroll, and/or time cards from that one table.

It could take a decent amount of time to set up, but once you get it, it will give you a lot of flexibility down the road. 
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb
Matthew can you explain (I will probably need to give you more details) how I might go about this?
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
I'm assuming you have a "Resource/Employee" table.  Then you probably have a few child tables like Documents, Time Cards, Payments, Commission, Appointments, etc.

You want to keep all your 'Resources' together, but have a hierarchy without having to create multiple levels of your 'resource' table.

Am I on the right track?

You basically relate the "resource" table to the "resource" table.  When you do this, quickbase will have a conniption fit and do some funky stuff.  This is where it gets tricky, and deleting the correct fields, and naming them properly comes in.

Its really an advanced development technique.  Are you working with a developer currently? If so, they should know how to do it. 
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb
Matthew I am not working with a developer. Yes, this is exactly what I need to do. It seems that I had done this quite some time ago (4-5yrs) when I first worked with QB, but that was with another group and I just don't remember if I did - then how I did it. 
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Contact me or one of the QB Partners via email and I (they) could see what it would take to help you more.
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb
Matthew... I have the relationship built and it works the way I need it to. I used the Employee ID as the reference and the reference proxy is associate name (a formula text field) that I renamed ROM Name. This gives me the ability to select the manager level. One of the fields in the table is position (Rep, Category, ROM) is there a way to create a filter within the relationship so I only have those persons who are ROM level in the ROM Name field? 
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Option 1:
With 3 layers of this 'self' relationship, the best advice i can give is to have the 'record picker' display 2  things.  

1st The level
2nd the name.

Then at least you could have them grouped.

Just to confirm that you have the relationship going, and that each level is basically a child record of the employee level above.

Option 2:
If you have this going, you should be able to make a conditional dropdown, based on a formula field, that shows "next level".
****

What are your 3 levels called? and the fields used to identify again?
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
I see that you have your self relationship for the associates.  I'm curious why you made 2 circular relationships?  What is the 2nd used for?
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb
The 2nd is used to build the manager level... that was all.