4 Replies Latest reply on Jul 8, 2017 8:03 AM by philmodjunk

    Field Lookup from related table

    dennismoorejr

      I have the following issue that I am struggling with.

       

      I have a "Participant" Table which consist of the following fields

       

      IDpk

      IDFirstName

      IDLastName

       

       

      I have a seperate "Incident" table consisting of the following fields

      __Incidentpk

      _IDfk

      IncidentFirstName

      IncidentLastName

       

       

      On the incident layout I would like to have the name field populated from a drop down menu which will auto fill and bring along the proper coding.

       

       

      ID record number 1 consist of

      IDpk =1

      IDFirstname = John

      IDLastname = Kennedy

       

      Incident page layout would have fields

       

      [First Name] [Last Name]

       

      I would the drop down menu to auto populate or select John Kennedy then once selected it would fill in the first and last name fields along with the proper IDfk so when back on another related layout a portal under John Kennedy's record would show the incident.

       

      I have the portal part completed.  My issue is the selection of the name from the drop down menu to populate into the proper name field and also attach the proper IDfk.

       

       

      I have the tables related via the IDpk and IDfk.

       

      Thank you in advance.

        • 1. Re: Field Lookup from related table
          dennismoorejr

          I think I found it.  I created a duplicate related table.  It seems to be working.  No my issues is having the first name field and last name field populate when selected from a dynamic list with both fields being displayed from the dynamic list.

           

          When I enter the first name field and enter John   "John Kennedy" appears in the dynamic list. and when selected only John is entered into the first name field, I need the last name to also populate from the same dynamic list.

           

          Hmmmm

          • 2. Re: Field Lookup from related table
            erolst

            1. In Participant, create a calculated field, result type text, as, say, cNameComplete, with the formula

             

            Substitute ( List ( firstName ; nameLast ) ; Char13 ; " " )

             

            2. Create a value list with 1. field Participant::ID, 2. field:Participant::cNameComplete; check "Display values from second field only"

             

            3. In Incident, get rid of the two name fields. Create a field like ParticipantID, with the same data type as the primary key in Participant.

             

            Put the field on an Incident layout, and format it as popup menu, using the new value list.

             

            Create a relationship where Participant::ID = Incident::ParticipantID

             

            Now when you select a name from the value list, you're actually inserting that participant's ID, which gives you a handle to their record and allowing you display data from it – no need to copy anything.

            1 of 1 people found this helpful
            • 3. Re: Field Lookup from related table
              dennismoorejr

              So I am still having an issue with this.

               

              My problem is the I need both the participant name and id number to appear on the incident page.

               

              I would like the incident page to display after selection from the drop down list mentioned aboce

               

              Incident Layout

               

                   1          John Kennedy

                  ID #       Name

               

              Currently when I select John Kennedy from the drop down or pop up list the value 1 is displayed in the name field. 

               

              Not sure what I am not grasping here?

              • 4. Re: Field Lookup from related table
                philmodjunk

                Format the ID field, not the name field with the value list recommended here. The value list is set up so that you use the name to select a participant, but then the value list enters the ID, so you want to set up the ID field with this.

                 

                You then have a choice:

                 

                Use the drop down list option and simply add the name fields from a related occurrence of participants to the layout.

                 

                Use a pop up menu and with the "override" check box selected, then add a second, unformatted copy of the ID field to the layout. (I would personally keep the ID field off the layout in most cases, but you wanted the ID and this is a way to do it.)

                 

                There are other more sophisticated options such as using the name field with an auto-complete enabled value list of names to look up the ID. This requires a bit of scripting, but can be much more user friendly when working with long lists of names. I'd get the above method working first, before trying anything more sophisticated, but if you'd like to try the method that I just described, see this teaching file:

                 

                Adventures in FileMaking #2-enhanced value selection