Text field is not text field

[MichaelT]

Hi all,

Please help, my student ID, which is a text field, X1234 for example. file maker seems to "not be" able to differentiate between X1234 and Y1234. when I input a class for ID X1234 it also display in Y1234's record. Appreciate any suggestions.

Thanks

[T-Square]

It is difficult to tell without more information, but it looks like you are using special codes for creating your relationships. There are many discussions here on why it is best for you to use straightforward sequentially-assigned serial numbers for your primary keys, which then should be used for relationships.

Guessing here, I'd say the field in your related table is set up as a number field, and that forces the relationship to ignore the alpha character at the beginning.

But I'm only guessing.

David

[MichaelT]

Hi David,

Thank you for the info, yes, the related field was set as number, it seems to have fixed the problem. however, do you think by keeping this format, student's ID = first letter of last name and 4 digits of the phone number. would make my data base to be vulnerable? Again, Thanks

Michael

[T-Square]

Michael--

I strongly urge you NOT to use such a field in your relationships. You REALLY should set up your relationship on an automatically-generated unique serial number; there are boatloads of discussions here and in database design literature to explain why. To raise a simple problem:

Dan Brown - 212-555-1212 gets B1212

Dawn Bootblack - 617-425-1212 gets B1212

Now, which linked records belong to whom? You'll never know!

If, however, you have:

67943 - Dan Brown - 212-555-1212

143567 - Dawn Bootblack - 617-425-1212

there is no ambiguity.

Of course, on screen you can still show your users a calculated field (Left(LastName, 1) & Right(PhoneNum, 4)) on screen anywhere you like. Just don't use it for relationships.

David

[MichaelT]

Thanks David, I did make the field unique, however the possibilities of having the same number keep popping up. If I were to add a field called primaryID and change the relation pointing to this field, so the other ID is now just an ID field. Do you think it will mess up the data base?

Edited June 12, 2008 by Guest

[T-Square]

I don't see why there would be any problem, but I'm not known for my visionary abilities.