Please help us to help you.
Please describe the tables you've set up to do this, then we can step your through the process of linking them in relationships and setting up looked up value field options that will work for you.
8 different name drop downs seems a bit unusual, it may be that there's a design change that can simplify things so you don't have so many different drop downs.
Essentially, it is a job assignment sheet. It has the job name, number, who is assigned to what position, machinery used, and dates. I would like for their phone numbers to pop up next to their name. Usually there are up to eight people working on a job at once--hence the 8 necessary fields. So unfortunately, that part cannot change.
I have set up two tables - the main one which will contain all the information for the job: number, project, location, machinery used, dates, position and personnel. In hopes to create a relationship I created a second table that contains strictly names in one field and phone numbers in the next.
In the personnel section I have used value lists so that I could make a pop up menu for each. There are 3 different value lists because not every employee is able to work in every position. Could this be the problem?
I hope this is enough information and my problem can be solved.
Thank you in advance to everyone who contributes to this thread.
Usually there are up to eight people working on a job at once--hence the 8 necessary fields. So unfortunately, that part cannot change.
Actually that can change and probably should be. Using a portal and a join table, you can list these in a portal so that you only have one field and one relationship to link to your personnel table. That makes it possible to place the phone number field from the personnel table next to your drop down. When you select a person in the drop down, their phone number then automatically appears.
With 8 separate fields, you'd need 8 separate relationships one for each drop down field. This can be done but is very tedious--requiring you to define 8 different table occurrences of the same personnel table.
Thank you. How do I go about making a portal and a join table?
For starters on Portals, look that term up in the Filemaker help table--it's got a pretty good introduction on the subject.
THen you can enter "many to many" in the search box above and click search to find any number of threads that give examples of setting up such a join table.
If you still have questions after that, post back here and I'll see what I can do.
I vaguely understand the Portal concept. When I use a value list does that interfere with the lookup? - example: I want to be able to choose a name from a pop up menu (attached to a value list) and then have the person's phone number appear in a field right next to the name.
I hope that makes sense.
No, that's one of the most frequently used implementations of this technique.
OK great. How do I go about implementing it?
There are two basic methods. Which is best for you depends on what you need.
One method copies the data from another table. Changes to the data in this other table don't automatically update existing values in the current table. That's useful for things like a table of prices in an invoice system where you don't want a price change to affect the invoices your created prior to the price change.
The other method simply displays the field from the related table. With this approach, a change in the other table automatically updates existig values.
Which is the option you need?
In either approach it starts with the right relationship.
Let's say your tables are called Main, Team and Personnel. Main is the table your layout is based on. You have a portal to Team and Personnel stores the phone numbers.
You have a relationship for your portal
Main::MainID = Team::MainID
You have a relationship for your phone numbers:
Team::Name = Personnel::Name (I usually use ID numbers for this)
To copy the phone number:
Add a field in Team for the phone number, In field options, select looked up value and select the Phone Number field from Personnel.
Place this field in your portal next to your Team::Name field that you've formatted as a drop down list or pop up menu.
When you select a name, the number is copied into the phone number field.
To just display the number:
Simply place the phone number field from Personnel in the portal next to your Team::Name field.
When you select a name, the matching phone number is displayed in the the Personnel:: PhoneNumber field.
Thank you! It worked for one position. Do I need to make separate tables for each position?
You shouldn't need to. Using my previous tables for the example, each additional position would be a new Team record joining the two tables. You'd just add a field to the team table for identifying the person's position.
Here's a demo file of the concept: http://www.4shared.com/file/E9D5IMLK/MultipleNamePhone.html
I added two fields for describing a team member's "role". Role is specific to the join table, Team. Selecting a role in this field identifies a person's role for the specific Job Record. Job Desc is specific to the person's personnel record. It will show the same Job Description for all instances where a given person is added to a Job Record's team.