AnsweredAssumed Answered

Lookup: 2 fields into one?

Question asked by barrettisgold on Feb 25, 2010
Latest reply on Mar 1, 2010 by philmodjunk

Like • Show 0 Likes0
Comment • 5

Title

Post

So...how do I have a pop-up menu that shows only related records between company and people, that when selected creates a lookup of both the first and last name in the field (PA for example), and enters the full name into the field?

I have a layout based on a Job Table. I want to select a company from the Company ID field in the Job table and then select a related employee in a number of fields in the Job Table. The tables Company, Job and People are all related via company ID. The fields I have in the job table are PA, Copywriter, Director, etc. I have managed to show only records related between company and people for the jobs field, so I can only select employees, using a pop-up, from the chosen company. I created a value list to show related records from the people occurence connected to the Job table. Great!

My problem begins when a chosen person moves company and their company ID changes. As I'm using a value list based on First and Last name from the people table, only the first name remains.

I basically want to create a lookup that will remain the same in the job table even if a person changes company...I think?

No one else has this question

Outcomes

philmodjunk Feb 25, 2010 12:24 PM

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.

Actions

barrettisgold Feb 25, 2010 2:39 PM

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.

Tom

Actions

philmodjunk Feb 25, 2010 3:08 PM

Try clearing the "do not replace existing value checkbox" and see if it now works for you.

Actions

barrettisgold Feb 28, 2010 1:55 AM

Hi Phil,

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!

Actions

philmodjunk Mar 1, 2010 9:50 AM

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:

Fields:

JobID

PeopleID

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

Actions

5 Replies

philmodjunk Feb 25, 2010 12:24 PM
Mark Correct
Correct Answer

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.
Like • Show 0 Likes0
Actions
barrettisgold Feb 25, 2010 2:39 PM
Mark Correct
Correct Answer

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.

Tom
Like • Show 0 Likes0
Actions
philmodjunk Feb 25, 2010 3:08 PM
Mark Correct
Correct Answer
Try clearing the "do not replace existing value checkbox" and see if it now works for you.
Like • Show 0 Likes0
Actions
barrettisgold Feb 28, 2010 1:55 AM
Mark Correct
Correct Answer
Hi Phil,

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!
Like • Show 0 Likes0
Actions
philmodjunk Mar 1, 2010 9:50 AM
Mark Correct
Correct Answer
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:
Fields:
JobID
PeopleID
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.)
Like • Show 0 Likes0
Actions

Lookup: 2 fields into one?

Title

Post

Outcomes

Related Content

Recommended Content