I'm not sure how to tie all these individual tables back together again using relationships. Do I need to create new FK's then delete the original PK's in these sub-tables?
Whatever you do to create relationships to link these tables, you'll do it in Manage | Database | Relationships by dragging from a field in one tabel occurrence box to a field in another. If the field has a value that correctly and exactly matches to the Primary Key of the correct record in the parent table, you should be able to use it for the same purpose in Filemaker, just make sure that the field type matches that of the Primary Key field.
Hi Phil, thanks for replying...
I planned on entering the 3-digit code just once in the 'Sites' table like I would for company name etc. Then the 'Contacts' table and the other 8 tables would link together using the same auto serial number rather than enter the 3-digit code in every time.
Actually, thinking about it now I might have misunderstood you completely - will test it out and come back later...
I think you've got a good idea here. I was thinking more to immediate needs and you are looking down the road toward a much better final solution.
I would definitely use an auto serial value in the parent table and leave the three digit code as just a "label" field in that table. To get the new FK values in the child tables, however, I'd import the current 3 digit tables and use relationships based on it so that I can then use a script or Replace Field Contents to copy the new PK into the new FK fields. After I've linked my tables by the serial number, I can delete the old FK fields and the relationships that use them from my database.
That's great, exactly the sort of advice I was looking for. Ok, a bit of a guess here but something along the lines of:
if site_id in the parent table = site_id in the child table, then copy the PK from the parent table into the site_id field in the child table
Wow, I think it worked!
If ( site_number = SITES::site_number; SITES::_kp_site_id; "")
Temporarily create this relationship:
ParentTable::site_id = ChildTable::site_id
Then do a show all records on the ChildTable layout, put the cursor in the ChildTable::ForeignKey field and select Replace Field Contents from the Records menu. Use the calculation option for replace field contents and enter ParentTable::PrimaryKeyField as the expression. (Subsitute your field and table names for mine here.)
This will update all the records in ChildTable in one operation. You may want to save a back up copy of the file before trying this as this operation cannot be undone.
Thanks Phil, I really appreciate your help on these niggles. They stop me dead and all I want to do is keep moving forwards...
I did everything you advised except I used a different calculation, see post above yours. It all "looks" ok, will have to do some more checking though (only 160 records in total so...)
Ok, just to double check I'm on the right track...
I renamed the old 3-digit field to _kf_site_id to reflect the fact that it now inherits its values from the parent table (_kp_site_id).
Then I deleted the original relationship parent:site_number = child:site_number and replaced it with parent:_kp_site_id = child:_kf_site_id
I have also ticked the "Allow creation of records in this table via this relationship" on the child side of the relationship.
Should I now change the child side FK to auto incrementing serial numbers or will the parent table take care of that when I add a new record?
The child side should always be just a straight data field (usually number). The auto-incrementing needs to take place in the primary key field. The child record's foreign key field just get's a copy of this value either through the auto-enter feature you enabled in a portal row or through a more direct form of data entry such as selecting a value from a value list of ID numbers.
Excellent, thanks again Phil...