4 Replies Latest reply on Feb 10, 2012 9:42 AM by fred@kca-inc.com

    Brain teaser???

    fred@kca-inc.com

      Title

      Brain teaser???

      Post

      I have a Users table which contains all of the normal user information including a unique user_id as well as a "reports_to" user ID (another user in the same table).  The idea is to provide organizational hierarchy to any depth.  The problem I'm having is extracting all of the direct and sub-reports to a user (direct reports, reports to the direct reports, etc.).  I designed it this way to allow any number of levels in an organization.  Any ideas out there on how to extract all of the direct and indirect reports for any user in the table?

        • 1. Re: Brain teaser???
          philmodjunk

          Sounds like a classic recursive tree structure. Have you defined a self join relationship to implement the "reports to" linkage?

          Are you wanting to produce a list from the bottom up or the top down? (Do you want the list of people who report to the current record or do you want to list the people to whom the current record reports?)

          As long as it's a linear path (no branching into a one to many relationship), I can conceive of a loop that uses Go to Related Records to move from record to record in the chain, stopping when you reach an empty "reports to" field.

          • 2. Re: Brain teaser???
            fred@kca-inc.com

             I have not defined a self join.  When I looked at that, I couldn't see how I could go many levels down.  Maybe I'm missing something here.

            I'd like to create a list (maybe in a separate link table) with everyone who reports to everyone else in the table all the way down to the non-supervisors who would have no-one reporting to them.

            • 3. Re: Brain teaser???

              Add a field for Supervisor or whatever term you want to use.

              You only need to know who the persons next level supervisor is and not the entire heirarchy.

              To simplify to two fields:

              Supervisor

              Employee Name

               

              Now create a summary report and make Supervisor the summary field and Employee Name the body field. Sort on Supervisor and see if you like those results.

              Make another summary report and use Employee Name for the summary field and Supervisor for the body field. Sort on Employee Name.

               

              Hopefully one of those reports will do it for you.

               

              If not you may have to consider adding enough fields to cover your heirarchy:

              Employee Name

              Supervisor

              Supervisor Level 2

              Supervisor Level 3

              ....

              Big Boss

              Then build a summary report with Big Boss the upper summary break and each supervisor a summary break and the employee in the body.

              Sort according.

              Create a value list for each supervisor level and assign that to the appropriate field for easy entry. There is an advanced method of creating a heirarchical list but lets save that for later. In fact, it could be designed so that entering one supervisor would automaticaly enter all of the upper level supervisors.

              • 4. Re: Brain teaser???
                fred@kca-inc.com

                 Thanks for the feedback.  The design criteria for this table/app are:

                1) unlimited levels in the organization; President->Vice-President->Director->Manager->Supervisor->etc...... (the easy part)

                2) the ability to see all direct and indirect reports to any person in the organization; I want to see everyone in VP Joe's organization down through all Directors, Managers, Supervisors and individual contributors. (the hard part)

                I've come up with several methods to do this, but it involves embedded loops and temporary tables or arrays. 

                I was hoping that someone else had run across this type of issue and had a nice, clean solution.

                Thanks

                Fred