Creating Multiple Related Records

[tmac]

If I have a main file with 15 records, once I match the first record in a related file, shouldn't the remaining 14 records automatically be created in the related db? If not, is it possible to make this happen and if so, how? Thanks.

[Vaughan]

I think you misunderstand the concept of related databases.

Lets say the "main" file is people's details (name, address etc). The second database might be courses they have enrolled in (or assets or clothes or anything).

A relationship must first be created between the two databases. Just like your jacket has your name on it so you can tell it apart from other peoples, the records in the two databases need to have "match fields" that are used to identify them. When a record's match field in the second database is the same as one of the record's match field in the first database, they match and are "related."

The choice for match field is critical. The match field must be UNIQUE and INVARIANT. People's names are a bad match field because they are not unique and people sometimes change their name. Developers solve the problem by using some sort of ID number, often based on a serial number. (FMP generates serial numbers really easily.)

So the first step is to create a match field in the main datbase and give everybody an Id number.

Create a match field in the second database, then create the relationship (in the first database) that links the two files together.

If the second database already has records in it then *you* will have to insert the appropriate ID number into each record so it matches up with the right record in the main database. Unfortunattely FMP cannot do this bit for you.

What FMP can do automatically is add the ID numbers to all new related records that you make from now on. The easiest way to do this is create a portal in the main database and check the "allow creation of related records" option in the relationship's defnition. This will list all the related records plus there will be a blank row at the bottom. this is an unborn related record! Enter data into this empty portal row and FMP will automatically create a new related record in the second database and insert the ID of the main record into the match field.

So to summarise, FMP does not automatically match all the other records in the maind atabase because that is not how related databases work. But FMP will do all the hard work for you if you set the database up correctly and begin to add more recods to the database.

[This message has been edited by Vaughan (edited October 29, 2000).]