Serial numbers in different tables

[RDecker]

How does FM handle serial numbers *in different tables*? If they are required to be unique, does this mean *unique in the table* or *unique in the entire file*? That is, can the same serial number exist for two different, *unrelated* records in the same file (each in a different table)?

(I have a specific perplexity, but if someone can explain the concept here, that may help me figure it out. If not, I'll give an example.)

Thanks,

decker

[Vaughan]

Unique in each table.

It makes no difference that your phone number might be the same as your credit card number or your gas account number, for instance.

[RDecker]

OK, then here's my perplexity. I'm probably not understanding how to do this correctly--but here's what I've done.

I have a multi-file, multi-table project which uses an interface file. In this particular instance I have related records in two tables (same file). If I relate them by serial number, the table with the lower serial number range (call it T2) links to already existing records in the other table (T1)--but those are the wrong records. I want the new record in T2 to create a new related record in T1, linked by serial number. That is, in T2 if the next new record generated is serial # 8, and a record already exists in T1 with that number, it pulls in that info rather than create a new record. At this point I've simply set the serial number sequence in T2 to start at 10000--and that works, but I suspect I've not designed this correctly.

Thanks for any suggestions. I'll check in the morning...

decker

[Vaughan]

I think you have the relational-thing mixed up a bit.

Records are related when the value in the "local" and "remote" keys match. Say we have a table of usernames, and a table of usergroups. Note that the tables hold different types of data, nothing in common. (If they had something in common then they should probably be in the same table.)

The usergroups table has a field called GroupID which contains a unique serial number for each group. To relate the two tables together we need a matching field in usernames to hold the selected GroupID serial number -- lets name this field GroupID as well to make it easy (it's the same name on both sides of the relationship). So each username record has a number in the GroupID field that relates to a record in the usergroups table. We have a relationship.

If you then decide to serialise the usernames records as well it's no problem. Call the field UserID and enter a serial number. The UserID field might be used to form a relationship between users and, say, records in an invoice database.

Note that the UserID field has nothing at all to do with the GroupID field nor with the relationship between usernames and usergroups, and the GroupID field has nothing to do with the relationship between users and invoices. It matters not that there is a usergroup record with the same serial number as a username record.