Forum Discussion

HansHamm's avatar
HansHamm
Qrew Assistant Captain
8 years ago

Create a Company Hierarchy by relating a table to itself

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

9 Replies

  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    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. 
  • HansHamm's avatar
    HansHamm
    Qrew Assistant Captain
    Matthew can you explain (I will probably need to give you more details) how I might go about this?
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    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. 
  • HansHamm's avatar
    HansHamm
    Qrew Assistant Captain
    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. 
    • MCFNeil's avatar
      MCFNeil
      Qrew Captain
      Contact me or one of the QB Partners via email and I (they) could see what it would take to help you more.
    • HansHamm's avatar
      HansHamm
      Qrew Assistant Captain
      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? 
    • MCFNeil's avatar
      MCFNeil
      Qrew Captain
      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?
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    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?
  • HansHamm's avatar
    HansHamm
    Qrew Assistant Captain
    The 2nd is used to build the manager level... that was all.