4 Replies Latest reply on Jul 22, 2011 5:53 PM by hgscn

    automatically repeat supervisor information

    hgscn

      Title

      automatically repeat supervisor information

      Post

      Hello gurus,

      I have a table of staff, with staff personal info, position(representative, area direcotor, regional director, ), supervisor (a drop down list made from this very table). So a representative's supervisor is an area direcotor, and an area director's supervisor is a regional direcotor. But they are all in this same table.

      Now in another layout notifying customers of services provided, I need to put the representative's name, his phone number, his supervisor's name, and his supervisor's phone number, etc. Since they are all in staff table, I would like it to work this way: put in representative ID and then all other information would appear automatically.

      How could I do this?

      Thank you!

        • 1. Re: automatically repeat supervisor information
          philmodjunk

          Define this relationship:

          Services::Representative ID = Staff::representative ID

          Then you can add any fields you need from staff ( such as a supervisor's name ) directly to your services layout and that info will appear automatically.

          If you need information from the supervisor's staff record, add this relationship to your staff record:

          Staff::SupervisorID = SupervisoryStaff::Representative ID

          SupervisoryStaff is a new occurrence of Staff that you add to Manage | Database | Relationships by selecting staff, then clicking the button with two green plus signs. (Double click the new occurrence to bring up a dialog where you can change it's default name.) You can format SuperfisorId to be a drop down or pop up of Representative ID's and their names to make it easy to link a Staff member to their supervisor's staff record. (This can even be a conditional value list that lists area directors for representatives, and regional directors for area directors.)

          Now you can add fields from  SupervisoryStaff to your Services layout to display information about the representative's supervisor.

          • 2. Re: automatically repeat supervisor information
            hgscn

            Phil,

            Thank you very much for your suggestions! That works!

            But I modified a little. In the new relationship, I didn't use ID fields. Instead, first, in Staff table I created a calculation field: FullName. I further created a drop down list using the FullName field and used this list for SupervisorName field. Then I created this relationship:

            Staff::SupervisorName = SupervisoryStaff::FullName. So in the Service TO, I pulled out the following fields:

            Services::Representative ID

            Staff::FullName

            Staff::Phone

            Staff::SupervisorName

            SupervisoryStaff::Phone

            Now, I just need to choose Representative ID from a dropdown list, then all other 4 fields come out automatically. I think this works only when I have a limited number supervisors and so won't have same names.

            Thank you again!

            • 3. Re: automatically repeat supervisor information
              philmodjunk

              There's a reason why I did not use the supervisor name for this. If a supervisor's name should need to be changed (to correct a typo or because they changed their name), the name change will "break" the connections you've created here. You'll need to find and update all the related records with the new name--which can easily lead to errors. You'll also have problems should you ever get two supervisors with exactly the same name.

              Using an ID number (which is always unique and never changes), will avoid those issues.

              • 4. Re: automatically repeat supervisor information
                hgscn

                You are definitely right! Actually not long ago, I got a lady who changed her name because she divorced. Thank you for your reminding!