1 of 1 people found this helpful
My BIG questions: should all employees exist on a single table?
Yes. Just add a job category or Job Description field formatted with a value list to distinguish between supervisors and assistants. Keep in mind that some assistants might get promoted to supervisors and you can manage that promotion by updating their Job Category.
how do I create the relationship between different types of employees (e.g. supervisors, assistants)
You aren't limited to a single table occurrence (box on the relationships graph) for any given table. You can set up two occurrences of your employee table and link them in a relationship to show the supervisor to assistant link. Two get two occurrences of the same table, you can click the original occurrence box to select it and then click the duplicate button (two plus signs). When setting up layouts, portals, calculations, etc. you can treat these two occurrences of the same table as separate tables even though they are actually two different ways of referring to data from the same table.
Assuming that you always have only one supervisor for a given assistant, you'd set up this relationship between them
Employees|Supervisors::PK_empID = Employees|Assistants::FK_assistantID
You do not need the FK_supervisorID field. FK_assistantID will be empty for all Supervisor records in the employee table.
Be careful of how you set up layouts based on these two occurrences of the Employees table. Selecting Employees|Supervisor as the table occurrence for a layout does not automatically limit the records shown to just Supervisor records. You have to do additional scripting to limit the found set on such a layout to a single job category.
Thank you. This was my first time venturing into self join tables. However, in solving a problem it also created one (or two). I have created a value list based on employees. When selecting a supervisor from a drop down menu on a Employees|Assistant layout, the drop down menu shows a list of all employees, rather than supervisors only. The current set up allows any employee to supervise, when this should be limited to supervisors. Here is/are my question(s)
1: Is there a way to filter a value list to display a subset of values (i.e., supervisors only)? This will eliminate the possibility of assistants supervising assistants.
2: A supervisor's license limits them to two assistants. Is there a way to limit the selection of supervisors to those who have less than 2 assistants? ( I guess this would be an extension of the first question)
I did a few more searches on filtering value lists. I found your file on conditional value lists. I set up the my relationship like the basic conditional value list section of your file and it's working like a charm. Now, only supervisors come up on the value list. I am still trying to figure out how to modify your "clear field" script to filter out supervisors who have 2 assistants working under them. But thanks for all your work. That section of the solution is definitely serviceable and functional.
1 of 1 people found this helpful
You might also take a look at the "hardwired" options as this can do the job with one less field and no added relationships just to support a value list. Not only simpler, but a value list that can be used on any layout in your solution where relationship based value lists have to be used with an eye to having the correct "context".
Yes. Thank you. this is perfect.
I cannot find find your tutorial on filtering value lists. Can you please send a link or attachment?
Best practice is Supervisor should not have a relationship with Assistant.
I couldn't let this one go.