2 Replies Latest reply on Aug 25, 2011 7:53 AM by DavidMuench

    Relational Database Question



      Relational Database Question



      I am a principal at a middle school looking to use Filemaker Pro as a tool to house important information for our student assistance team.  I have been able to navigate setting up tables and creating some relationships between them.  As an example, I have an overall "student information" table populated with demographic information exported from our Student Management System.  I have also created a table entitled "Parent Contacts."  I set up a relationship between the student id in the student information table and the student id in the parent contact table.  This is the only field that will be absolutely unique to the student.

      My issue is that when someone is logging a parent contact in a form in the "parent contact" table they really aren't going to know off hand the student id.  However, that is the only way I know of to bring over some of the other info from the student information table such as the student's phone number.  Is there a way to have the Student ID be a primary key but also have the last name be a look up key so that they can find the right student and not need to know the student ID number?  When I have tried to relate the last name field it does not seem to work for me.

      Any help would be greatly appreciated or if you need further clarification please ask.



        • 1. Re: Relational Database Question

          You don't want to include a name field as part of the relationship. That won't work and if it did, it defeat the purpose of using the serial number field for a student ID.

          There are any number of ways to select a student by name in order for the student ID number to be copied over to a new record in your contacts table. Here are just a few:

          1. Easiest is to establish a portal to contacts on a layout based on the students table. If you enable "allow creation of records via this relationship" for parentcontacts inside this relationship, you can simply start entering data in a blank row in the portal and a new record will be created in ParentContacts and this new records will automatically receive the student Id number.
          2. You can also format the ParentContacts::StudentID number on your Parentcontacts layout as a drop down list or pop up menu of StudentID's from the Students table. You can specify a field from Students to be listed in Field 2 of this value list. If you do that, you can pull down this value list and use the text in field 2 to select a student but the student ID is the value that will be entered into the field. Note that the data listed in field 2 should be unique. You may want to combine a student name and address or other text from the student table in a text field by setting up an auto-entered calculation for this and then this field would be used for the field 2 list of values.
          3. You can also use script triggers and global variables or fields to "synchronize" your student and parentcontacts layouts. Use OnRecordLoad on the student layout to copy the studentID to a global variable or field, then define the ParentContact::studentID field to auto-enter the contents of this field or variable. With this approach, you can perform a find in students to find the student you want, then click a button to switch to parent contact where you can create a new record in Parent Contacts which will automatically be linked to the current record shown on the students layout.
          4. There are also ways to use portals and/or drop down fields with auto-complete enabled lists of student names. This requires scripting to set up, but can make managing long lists of values much easier to work with.  Here's a demo file that illustrates several such approaches but don't try these out unless you have a decent grasp of scripting:  http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html
          • 2. Re: Relational Database Question



            Thanks for the feedback.  I realize my question is very basic in the grand sense, but was a primary issue for me as a novice user.  Your pointers are right on to what I need.  I used your suggestion from # 2 and it works very well.

            Again, thanks.......