unique validation error

[VFXdbGuy]

In my database I have Project (parent) and Shot (child) tables. The child ID is Shot_ID which is has an auto-enter serial with unique validation.

As I prepare for its launch I have been deleting all the records and testing the script that adds the child records. The script uses a portal on the parent table.

The weird thing is that while the first record (Shot_ID="SHOT00001") gets added just fine, the second record (Shot_ID = "SHOT00002") triggers a validation error saying the Shot_ID is not unique. But I have debugged the script and can clearly see the value is different! All subsequent shots add fine. If I overirde the warning I get the kind of strange behaviour you'd expect from having duplicate keys. (ie: ghost records in portals connecting the child records to tables below)

This is completely reproducable at will

How can this happen?

[VFXdbGuy]

Update: If I set the Shot_ID field to start at SHOT00003, the problem never occurs.

[T-Square]

Perhaps if you simplified your shot ID to be a simple serial number, rather than one that embeds secondary (and unnecessary) information, the problem will go away. To wit: putting "SHOT" in the ID field isn't necessary, since the records in the table are, by definition, shots.

If it makes you uncomfortable to use unadorned numbers, you can always hide these numbers from your users, and display a calculated field that looks that way instead...

David

[LaRetta]

I have no problem with including part of a descriptor within the serial ... after all, I've been known to have IDs from different tables within the same field/table and this would be the ONLY way to identify one from the other. The process works very well.

An auto-enter serial doesn't need to validate as unique - it will always be unique and I suspect we have another issue here. If the child record is created from portal on a just-created parent, it may be a timing thing on commit and when that field validation fires (before the concatenation?). Exactly how is your auto-enter ShotID created? Anyway, I would question needing the validation ... if you remove it, it will probably work fine.

[VFXdbGuy]

Thanks LaRetta,

I have been using text-numeric IDs for years without a single issue for much the same reasons you do. But I do not create them from concatenation. The serial is set to start at "SHOT00001" and increment by 1. As for the firing of the validation, I have a commit records step and naturally that is where it happens.

I am worried I have a corruption issue and nothing irks me more. To my mind the corruption issue is one of the things that makes FileMaker a junior database dev tool. Has anyone ever encountered unrepairable corruption in a SQL 7 or 2K DB? No, it is a professional enterprise tool. And before anyone says "just go back to a previous versio" how far back? How to tell where the corruption crept in?

I really hope it is something else but it's becoming clear that little else could explain such bizarre behaviour.

[LaRetta]

Hi Grant, yes, we now don't need to concatenate on the IDs. Well it sounds like you've got your bases covered ... I can offer no other suggestions. For me, corrupted data crashes system when I click into a field - not simply fails validation or doesn't increment. Very strange indeed ...

[LaRetta]

Grant, have you tried creating a new test file established in exactly the same way to see if you can make it fail there as well? I would assume you have but I've been known to forget this test-step. It will be the only way (that I'm aware of) to pin down whether it is corruption or process. Commit and validation have changed since 7/8 and there are known issues involving (not) commit of parent before child and it can mess with your head.

[LaRetta]

Oh. You have Advanced! Can you step through your process and see what happens under Debugger?