Tips for preserving relational integrity?

[Mandu]

Hi,

I created an Invoice/LineItems database consisting of two tables in a one-to-many relationship for a client about a month ago. Really basic stuff. Recently we discovered it had gotten corrupted: there were orphaned LineItem records, some Invoice records had lost their LineItems, and there were even Invoice records with duplicate Invoice_IDs (used to relate from Invoice->LineItems).

Here are some facts about the database that I think are relevant:

It is being served on a LAN by FMServer 5

In the Invoices table:

-- Invoice_ID is Indexed, Auto-enter Serial, Strict, Required Value, Unique

-- The relationship "LineItems by Invoice_ID" is defined with "When deleting, also delete related records" and "Allow creation of related records"

-- Layout "Invoice with Line Items" contains a portal relating to LineItems via the above relationship. The portal does *not* allow the deletion of related records.

In the LineItems table:

-- Invoice_ID is indexed.

-- LineItems_ID is Auto-Enter Serial, Unique (this field is not used at all!)

-- The relationship "Invoice by Invoice_ID" points back to the parent record, and is *not* defined with either "When deleting, also delete related records" or "Allow creation of related records"

-- There are no layouts. This table should not be directly modified by end user.

I'm at a loss to understand

-- why line items are being lost

-- why there are orphans

-- why I am able to place the cursor in a portal row and type CMD-E, and have not one but *several* rows disappear! (Again, the portal does not allow deletion of related records, at all)

I wish I had more details, but I don't yet. Being pretty new to FM, I'd appreciate it if anyone sees any general red flags in my implementation.

Thanks!

Chap

[transpower]

Have you tried reinstalling FileMaker? Have you tried Recovery? Have you run diagnostics on your hardware?

[Mandu]

I was hallucinating when I saw multiple line items disappear at once; sorry.

The main problem turned out to be an edge case in which I generated duplicate serial no's by mistake.

I'm trying to figure out a super-quick/easy way to check for the existence of duplicates in a table. For example, if the number of index entries does not equal the number of records, this indicates the presence of non-unique values. I don't know think there's a way to get the number of index entries, however.

I don't need to identify the specific duplicate(s), just to identify that the table has gotten out of whack. Anyone know any neat tricks, besides the well-documented self-join/calculation field?

[Brian C]

Using the search function and use of the "!" to locate duplicates is a good way I suppose.

As for prevention, you can set your validation rules for the field containing your key to only allow unique entries, set to enforce and dissallow overide. If you go this route, make sure you test it to make sure there is not some sort of conflict with how your record creation processes work.

[Mandu]

Using the search function and use of the "!" to locate duplicates is a good way I suppose.

Yes, it did the trick quite well; thanks!

As for prevention, you can set your validation rules for the field containing your key to only allow unique entries, set to enforce and dissallow overide. If you go this route, make sure you test it to make sure there is not some sort of conflict with how your record creation processes work.

I'm using an Applescript to bulk-load records, using the 'create new record with data ...' command in particular. Using this form, you have to supply a list of values for *every* field, and when I do this with all the validation rules turned for the unique field, the 'create new record' fails with an error to the effect that I'm trying to modify a protected field. (Unchecking 'prohibit modification of field' in the auto-enter field definition tab solves this, but also apparently allows the storage of duplicate values.)

I may post into the Applescript forum for this. Actually, if I had it all to do over, I'd use Import instead of Applescript - just one of many reasons I should have listened to the FM pros all along.