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