Related records without valid key

[Cable]

I am having a serious problem with a FileMaker solution I have created. The database is hosted on FMP Server 7 on a Windows 2000 Server machine. It is accessed by a mix of Windows 2000 Professional and Windows XP clients.

The particular issue I'm having is with two tables. Table 1 has a primary key (pk) and has a one to many relationship with Table 2. Table 2 has records which are selected and related to records in Table 1 by populating the foreign key field (fk) with the serial number in Table 1's pk field.

The problem is that I am increasingly experiencing issues where records in Table 2 are showing up in a portal as being related to Table 1, and the "go to related record" function correctly takes you to the related records, yet the fk field for some of the records is blank! There is nothing in this field, yet it is perfectly behaving as if it was related.

Usually, I can fix the problem by typing the correct key into the fk field, or by doing a "replace" function. The real problem is that it sometimes occurs as I am trying to "unrelate" records in Table 2. In this case, the fk field is blank, but it is still showing up as a related record. I can sometimes fix the problem by entering in a false value in the fk field, and then clearing the false value; but that doesn't always work.

In the past, whenever I had this problem I would unhost the database, recover the database, make a copy of the recovered database, and run file maintenance on it. This would clear up the problem for about a week. Recently, it has begun happening every day.

It has also been accompanied by other problems, such as "ghost records" that show up as related records in portals and in "go to related" found sets, but don't actually exist. For example, there could be 10 total records and 6 in a found set (including the blank one). When the blank record is deleted, the found set goes to 5, yet the total records is still 10. Going back to the parent and again performing "go to related records" again shows 6 out of ten with the blank one there again.

I have also had account names go corrupt and act funny.

This is a very large and complex database that would take months to rebuild from scratch, yet these problems are becoming more and more troublesome. Does anyone have any ideas?

[Vaughan]

Firstly, make sure the data type of primary and remote key fields is the same. Weird stuff happens if they are mis-matched, like one is text and the other is number.

Secondly, recovered files are NOT repaired files intended to be put back into production. The Recover command only fixes up the data, and often often repairs it at the expense of the database sturcture (scripts and layouts etc). The recommended process is to recover the damaged file, then import the recovered data into a known-good clone of the database. If you don't have a known-good copy then frankly you're in deep doo-doos.

Part of the trick to getting good at this game is to have a known-good copy of the solution burned to CD, and to be organised enough to make sure the copy is updated each time a modification is made to the structure (layouts, scripts, fields).