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.
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:
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!
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.
You are definitely right! Actually not long ago, I got a lady who changed her name because she divorced. Thank you for your reminding!