3 Replies Latest reply on Mar 23, 2013 10:27 AM by arby

    Proper Relationship with ID Number

    samjs

      I have a Student Database and I'm creating a solution to add a student to a class. In my class portal, I want a drop down list of the students names and I want the capability of auto fill.

       

      I really want the relationship based on the student ID number, however, I'm having problems indexing the names so I can use them in a drop down list. The only way I can get it to work is when I build the relationship based on the student's name. How do I build a sound relationship based on the student ID number and yet have a clean looking interface without showing ID numbers along with the capability of a drop down list with auto fill.

       

      Here is an example of how I want it to work...

       

      Relationship.png

        • 1. Re: Proper Relationship with ID Number
          LyndsayHowarth

          3 things:

           

          FileMaker valuelists allow you to have a first and second value. You can also opt to have only the second value show in the list. I suggest you make one with ID as the first value and full name as the second... and only show the 3 things.

           

          Your relationships are Students::pk_ID= Class::fk_ID_Student ... etc.

           

          On a layout... on the portal, add one field on top of another. The bottom field is the real related fk_ID_Student and has your valuelist above assigned. The top field is from the portal table and it either looks up of calculates the Studen Name from the relationship based on the student ID. It is filled with white so you can't see the field behind and the field's behaviour is set to not be enterable in Browse mode.

          The effect of this is that when you click on the top field it actually selects the one behind with the valuelist.

           

          I hope this helps and makes sense...

           

          - Lyndsay

          • 2. Re: Proper Relationship with ID Number
            samjs

            Thanks Lyndsay, that makes sense!

            • 3. Re: Proper Relationship with ID Number
              arby

              What if two students share the same name? By showing the identifier in the value list, the user can select the correct one. In a large database of thousands of students, it's quite possible to have

               

              Smith, John

              Smith, John

              Smith, John

              Smith, John

               

              So the user picks one and then has to click a button to take them to the student record to verify that they've selected the correct one. Even though identifiers don't carry information, users have an easier time double checking that S1234 is the correct selection and not S4332

               

              Smith, John S1234

              Smith, John S4233

              Smith, John S8733

              Smith, John S4332