I think I follow why you don't simply add the name fields from the related field to your layout...
You can use the "calculated value" auto-enter option:
People::LastName & ", " & People::FirstName
is one possibility.
Hi Phil, thanks for the super speedy reply!
I have tried this but still find that when a person changes company only the first name would remain? Perhaps these picks may help. I also find when I select a company all the job fields are automatically populated with the first name in the related list. Do you think it could be a value list problem?
Here is how I have the tables hooked up:
This is my Job layout. The fields are from the Job table:
This is how I have the calculated value setup for the various jobs fields, as you suggested:
Here you can see how the field is setup in the edit database window:
Hope this helps paint a clear picture in order to find a solution!
I really appreciate your help.
Try clearing the "do not replace existing value checkbox" and see if it now works for you.
Still no luck I'm afraid. If I choose a company on the Jobs layout, the fields are still populated by what I presume is the related Peopl record with the earliest ID. Also, if I select a person for the job and then they move company, only their first name remains. I really appreciate your help. This is one of the last bugs before my database gets to a usable state.
This is what I have done:
Created a relationship between Company, Job and People tables via company ID
Created a value list to show only related records, showing a persons first and last name
Applied the value list to the jos fields, Producer, PA, etc.
Set the fields as look ups...then as a calculated value using first and last name as Phil's suggestion
Set the fields as popup menus
Have I done some thing wrong here?
Thanks Phil...or anyine else who may know of a solution!
First, profound apologies for not reading your original posts more carefully.
In order for your lookups to work with your current design, you need separate relationships for each name field so that filemaker knows which name record to reference. You will either need to define a series of such relationships or you'll need to define a "join table" where you associate people records with a specific Job record.
Here's a demo file: http://www.4shared.com/file/232283266/30b30da5/TwoFieldLookup.html that illustrates the multiple relationship approach and the problems it produces. I didn't bother making the drop downs conditional to limit people to just one company as that doesn't seem to be the major issue here.
A join table, Job_Personnel, is likely a better way to go here:
JobTitle (PA, Director, etc.)
Your relationships would look like this:
Jobs::JobID = Job_Personnel::JobID
People:: PeopleID = Job_Personnel:: PeopleID
Rather than add a full name field to Job_Personnel, I'd just place one or more name fields from People in a portal to Job_Personnel. (While their company may change, their PeopleID number should remain unchanged here.)