9 Replies Latest reply on Jun 22, 2017 3:17 PM by TimothyRamos

    Supervisor and assistant relationship

    TimothyRamos

      I have a small Speech therapy clinic and I have implemented a solution to track therapy notes and billing. I would like to roll out a solution to track employee relationships (supervisor, assistant). The nature of the relationship is as follows.

      1: A supervisor may perform therapy autonomously

      2: An assistant must work under a supervisor's license to perform therapy

      3: Both supervisor and assistants are employees

      4: the supervisor receives a percentage of each hour of therapy performed by their assistant

       

      My BIG questions: should all employees exist on a single table? Or, given their relationship should they exist on separate tables?

       

      Follow up questions

      1: If employees are on different tables, should I have separate contact table for employees, business contacts, clients for the purpose of company emails news letters?

      2: If they are are in a single table, how do I create the relationship between different types of employees (e.g. supervisors, assistants)? Should I use the

      EMPLOYEE TAbLE

            Primary Key: PK_empID

            Foreign Key(s): FK_supervisorID ; FK_assistantID

       

      EMPLOYEE Table                         EMPLOYEE TABLE (SUPERVISOR)               EMPLOYEE TABLE (Assistant)

       

      Primary Key: PK_empID --------  FK_supervisorID      

                                         |___________________________________________________FK_assistantID

        • 1. Re: Supervisor and assistant relationship
          philmodjunk
          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.

          1 of 1 people found this helpful
          • 2. Re: Supervisor and assistant relationship
            TimothyRamos

            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)

             

            Thank You

            • 3. Re: Supervisor and assistant relationship
              TimothyRamos

              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.

              • 4. Re: Supervisor and assistant relationship
                philmodjunk

                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".

                1 of 1 people found this helpful
                • 5. Re: Supervisor and assistant relationship
                  TimothyRamos

                  Yes. Thank you. this is perfect.

                  • 6. Re: Supervisor and assistant relationship
                    TimothyRamos

                    I cannot find find your tutorial on filtering value lists. Can you please send a link or attachment?

                    • 7. Re: Supervisor and assistant relationship
                      bigtom

                      Best practice is Supervisor should not have a relationship with Assistant.

                      I couldn't let this one go.