Can anyone tell me which is the best type of field to use for a relationship field. I know that in a lot of databases it is best to use a numereic ID. I have also heard it said that in filemaker it is fine to use Either number or text. What are the real facts about this. Also why should we avoid using calculation fields?

I think the easiest and best field to use for relationships (called primary key) should be a Record ID field. It doesn't have to be strictly numeric, it can be alpha-numeric (combination of text and numbers).

I made a relationship once from the last name field, but this presented problems when records in the related file were deleted, their last name sometimes still appeared in the main file. Other than that, it worked ok. Although you should probably stick with using the record ID field.

Did that answer your question?

Ken

What field you use is totally dependant upon the type of relationship and how you want it to function. That said, you should always use TEXT fields on both sides, whether they be calcs or not just depends upon the specifics of the relationship.

Number fields are not good, because you can only use numbers and this is not always what you wanna use for relating records.

It is interesting that for FM the use of text is used for relationships as on most systems it is better to use Numeric IDs. I will admit though that people prefer to think in text terms rather than Numbers. We remember our customers by their names and faces not their telephone numbers. I have heard that it is better not to use calculation fields as it can cause problems. What are these problems does anyone know?

Use text, they can be longer then numbers, though FM only looks at the first 20 charactors of a field.

Relations based on calculations should be avioded if possible, since they slow stuff down sometimes and you can't create records through a portal with them