5 Replies Latest reply on Dec 11, 2015 6:49 AM by brian47933

    Using a lookup


      We have a Student ID that identifies the students' faculty and department. Unfortunately if a student changes their faculty they are issued a new Student ID. Currently I use information pulled from another system that provides two fields - one for faculty and one for program. What I would like to do is have FM look at the Student ID and populate a faculty and program field automatically based on digits in the Student ID field. This way I would only need to replace the ID.


      The Student ID numbering system is like this:

      011403122 =

      First two digits identify the faculty (01 = Faculty of Education)

      Digits three and four identify the year entered (14 = 2014)

      Digits five and six identify the program (03 = Elementary Education)

      Digits seven through nine identify order of acceptance


      I would like FM to see the Student ID number recognize the first two digits and populate a Faculty field from a value list(?). Then recognize digits five and six and populate a Department field.


      Am I on the right track? I assume I need value lists for the two fields. I'm also guessing there is a need for a calculation or script?


      I appreciate any guidance or examples.


      Thank you!

        • 1. Re: Using a lookup

          Why make someone learn a code? They already know the student's name. Let them use that. If there's more than one student with the same name, guide them through picking from the duplicates.


          I would highly recommend that if the student ID is EVER changeable, it should NOT be the basis of a permanent relationship. Use it only as a "secondary key" (or just use the name, as I've suggested, as a "secondary key". Create a "primary key" that's a unique serial number or a UUID, something that no user can ever change, nor need to because it has no other use except as a database key. Use secondary keys (such as the "student id") to look up a primary key (such as the student primary key) and then use that primary key for permanent, reliable relationships. That way, if the student id changes, or you get a 100th program and have to go to three digits, you don't break relationships.


          Generally, id's that string together a bunch of data aren't as helpful as they seem. Presumably, once you've selected a faculty, you not only have a code, you have the full name and many more details. You have a date that doesn't just have the year of entry, it has the entire date. If you're linked to a program, you have the program name. And so on. Why create a code, however "simple", when users can view and search on the actual full names of things?  Aside from trying to fit key data onto a tiny label or something, the id is really a giant step backwards for clarity and usability... and a poor match field, as you've discovered.


          "Informative" id's like this are a throwback, and were very helpful for manila folder tabs in a filing cabinet. If you leverage the database and interface properly, you should find that you can completely delete this field and not miss it.


          Chris Cain


          • 2. Re: Using a lookup



            Thanks for your reply. I am in a position that I have to work with what I have been given. When I set up the database I created a unique serial number for students generated by FileMaker. The problem for me is that the school is using this informative way and I need to figure how to update the faculty and program based on the system they have created.


            I need a way to sort students by their respective faculties and programs.



            • 3. Re: Using a lookup

              Extensitech wrote:


              "Informative" id's like this are a throwback, and were very helpful for manila folder tabs in a filing cabinet.


              Which explains why the government still loves them so much.  

              • 4. Re: Using a lookup

                In what you are describing, one way is to create two text fields with auto-entered calculation values like FacultyID with Middle(StudentID,1,2) and ProgramID with Middle(StudentID,5,2). Then either relate to the other programs that your pulling the information from or create two new tables. Faculty with a text field for FacultyID and FacultyName and Program with a text field for ProgramID and Program or Department. Finally on the student records you would show the related fields FacultyName and Program.


                You wouldn't really need the value lists unless your creating the student ID in your system and want to combined the faculty, year, program, and order of acceptance fields.

                • 5. Re: Using a lookup

                  Blue22 I think this is exactly what I need. I will create an auto-entered calculation field on the student table that will look at the Student ID and then return the faculty name that is associated with that particular number.


                  Thanks for the explanation.