5 Replies Latest reply on Mar 1, 2010 9:50 AM by philmodjunk

    Lookup: 2 fields into one?

    barrettisgold
    barrettisgold Feb 25, 2010 11:59 AM

      Title

      Lookup: 2 fields into one?

      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?

       

       

       

       

      • 80 Views
      • Tags:
        • 1. Re: Lookup: 2 fields into one?
          philmodjunk
          philmodjunk Feb 25, 2010 12:24 PM (in response to barrettisgold)
            

          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.

          • 2. Re: Lookup: 2 fields into one?
            barrettisgold
            barrettisgold Feb 25, 2010 2:39 PM (in response to barrettisgold)
              

             

            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

            • 3. Re: Lookup: 2 fields into one?
              philmodjunk
              philmodjunk Feb 25, 2010 3:08 PM (in response to barrettisgold)
                 Try clearing the "do not replace existing value checkbox" and see if it now works for you.
              • 4. Re: Lookup: 2 fields into one?
                barrettisgold
                barrettisgold Feb 28, 2010 1:55 AM (in response to barrettisgold)

                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!

                • 5. Re: Lookup: 2 fields into one?
                  philmodjunk
                  philmodjunk Mar 1, 2010 9:50 AM (in response to barrettisgold)

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