Skip to main content
Question

Recommendation on best way to create hierarchy between client contacts

  • October 12, 2019
  • 0 replies
  • 228 views
Forum|alt.badge.img+11
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 they can show them an activity sheet of all active projects.

However, they also want this to be work up the hierarchy.  For example lets say we have a team of (3) people.  Mark is in charge of the entire east coast and Jeff, Ashley, and Jim all report to Mark.  

If I pull a report on Jeff's projects I only want to see his.  If I pull a report on Mark I want to see any direct projects Mark is working on in addition to Jeff, Ashley, and Jim since they all report to Mark.

So I am thinking I need to take my contacts table and make a relationship to itself with a field.  In this case on Mark as the contact I could add his direct reports in the one to many relationship and pull the data.  Or should I be creating a field somehow on the contact to identify the supervisor so on Jeff's contact page I would select Mark as his boss.

Once I get the relationship right I am assuming it is a straight forward report but wasnt 100% sure I am going about it the right way.

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

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
------------------------------
    Terms & ConditionsAccessibility statement