Validation of two fields for uniqueness

[mossman00]

I am making a database of locker assignments. Each locker can have up no more then two students. Each record has two student ID fields and locker number field. When a student is assigned a locker their number is put into one of the student ID fields. If two students are partnering, the partner is put into the other student ID field. I set up the field validation so that the two student ID fields are set to be unique values. This prevents a duplicate students in their respective field throughout the database but does not check for cross duplicates. An example of this is as follows. The database contains two records each with the following fields: Student ID 1, Student ID 2, and Locker Number. The first record contains the following data: Student ID 1 = 100, Student ID 2 = 150, Locker Number = 500. The second record contains the following data: Student ID 1 = 150, Student ID 2 = 100, Locker Number = 600. The two students with the ID numbers 100 and 150 have been assigned a locker on the first record and then reassigned on the second. How can I set up a validation to catch this and throw an error message. I am not sure how easy this is to do, but I would like to have that error message change depending on what type of error it is or have the field that is invalid change color or something like that. If this cannot be done it is no big deal since the main issue is the one of validation.

[Vaughan]

Try TechInfo article 104191 at the FMI web site, under support.

[mossman00]

I do not think that will work. On record one the ID numbers were 100 and 150. If they are concatenated it would make 100150. On the next record the student ID numbers were 150 and 100. If they are concatenated if would make 150100. Both of these concatenated values are unique. There needs to be a way to check for uniqueness that checks each time a student ID is entered against both of the fields on every record in the database.

[Fenton]

You bent relational rules by having Student1 and Student2. It was, however, the most efficient way to deal with this particular situation.

What you need is not a plain concatenation, but a multi-line one (don't know if there's a special word for such a thing). Create a calculation field, result text, = Student1ID & "

ValidateDupe.zip

[mossman00]

Thank you so much. YOU ROCK. Could you please tell me how this works. How does you concatenation differ then a normal one?

I do have another couple of questions. Is there a way to set a default value in FileMaker Pro? In my student database which is another table in the master database I want the students to start out with a ratio button set to unassigned. When they are assigned a locker number in the master database I want that field to change to assigned. I have it working with the first student on the record but it will not work with the partner. I set up a relationship between the student database and the locker database. The second student is set up in the same way except I used another instance of the student database in the relationship area. This allowed the student numbers to be different. (Should I make the assigned field a global one?)

Next question, if there some way to have a failed validation message be different depending on what part of the validation failed or would I need to use a script to do that?

Thanks for all the help. Thanks, Thanks, Thanks.

[Oldfogey]

It's too late for me to think this through properly but I disagreee sort of with Fenton.

You should be able to 'sort' the two Student IDs, so that the concatenated result in both of your examples is 100150. Being a pessimist, I would always insert a separator as well so you get 100/150. (Believe me, some bureaucrat will decree that student numbers can be 2, 3 or 5 digits just after you've completed your solution.)

[Oldfogey]

Having slept on this, I think you need to modify your design a little. It would save a lot of potential future hassles.

Why don't you store the Locker Number in the Student table when it is allocated? This wouild mean you couln't have the one student with two lockers, which was your original problem wasn't it?

Do you really mean a radio button? I suggest setting up a calculation field called Assigned = Case(IsEmpty(Locker Number), "Unassigned", "Assigned").