Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
6 years ago

Recommendation on best way to create hierarchy between client contacts

I had a user request a good feature for my app.  They want to be able to pull a report on a contact showing all of the projects they are associated with.  This way if they are meeting with a client th...
Relationships
EvanWestbrook's avatar
EvanWestbrook
Qrew Cadet
6 years ago
Ivan,

If I understand correctly, it sounds like you have a couple of many to many table situations.

  1. Many Contacts can have many Supervisors, and many Supervisors can have many Direct Reports
    1. Mark could report to both Larry, VP of Sales, and Cindy, VP of Operations
  2. Many Direct Reports can have many Projects, and many Projects can have many Direct Reports working on them
    1. Trucking Accounts can be worked on by Mark and Ashley, and Ashley can be working on Trucking Accounts and Baseball Accounts

With many to many relationships, a "join" table might be more appropriate than relating a table to itself. For example, maybe you would have a "Supervisor Direct Reports" table to track hierarchies, and a "Employee Projects" table to track which employees are on which projects.

------------------------------
Evan Westbrook
PRIME Developer
Harder Mechanical Contractors Inc.
Portland OR
------------------------------
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    6 years ago
    Evan, thanks for the response!  not quite right though.  I am not referring to an internal hierarchy.  I am referring to a client hierarchy.  For example our largest client has the following Hierarchy:

    • President of the Division
    • Multiple Regional Vice Presidents reporting to the President
    • Multiple District Managers reporting to each Regional Vice President
    • Multiple General Managers reporting to each District Manager


    So in essence a pyramid type of setup with (4) layers.  If I pick any contact towards the top of the list I should be able to see all of their opportunities underneath them.

    So if I view the President, I see the entire pyramid.  If I pick a RVP I get the DM and GM opportunities reporting up to that RVP, etc.

    ------------------------------
    Ivan Weiss
    ------------------------------
    • EvanWestbrook's avatar
      EvanWestbrook
      Qrew Cadet
      6 years ago
      Ivan,

      Thanks for the clarification. I'm going to assume your client list table is called "Clients."

      I think an additional table to track hierarchies is still the direction you want to go (maybe a separate table for each hierarchy step if it's going to remain fairly static.) If you relate Clients to Clients, you'll have to add a new table relationship for each additional field (not fun.) Having Parent records automatically know their Children or Grandchildren could get tricky, but once you figure that out, it will be simpler to manage.

      From there, you can set up report links to display the information your form. Lookup fields counting the total # of Child records for each hierarchy step can then feed form rules to display different aspects of the Client form.
      ​

      ------------------------------
      Evan Westbrook
      PRIME Developer
      Harder Mechanical Contractors Inc.
      Portland OR
      ------------------------------
      • IvanWeiss's avatar
        IvanWeiss
        Qrew Captain
        6 years ago
        Evan, so I did not quite follow you.  The table is called Contacts.

        I was assuming I could self relate it and just do it that way.
        But you are saying do a separate table and name it lets say Contact Hierarchy.  I am thinking it should relate to the Contacts table twice
        The first one being the Employee and the second one being the Manager?  Is that correct?  And use that to report out the work since it is linking people?  I guess what I was somewhat confused by is each Manager can have several Employees reporting to them.

        ------------------------------
        Ivan Weiss
        ------------------------------