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.
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.
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:
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:
Supervisor Level 2
Supervisor Level 3
Then build a summary report with Big Boss the upper summary break and each supervisor a summary break and the employee in the body.
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.
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.