We have a shared database that is intended to have permanent, unique IDs, but duplicates keep slipping in. I’ve identified three ways this could be happening, and I’ve only found solutions for 1.5 of them.
- Manually editing the ID field and typing in a pre-existing ID. This is prevented by the “no duplicates” setting in the design sheet for that field. (Resolved)
- Creating a new record (by hitting “return” or the “Add New Record” or “Insert New Record” buttons in the sidebar). The “+” setting in the design sheet causes Panorama to automatically increment the ID number, but if the “next record ID#” is set wrong, this feature is worse than useless. The next record ID# sometimes becomes wrong, and since we don’t notice right away, I have no idea what circumstances lead to this. Once I notice it, it’s easy enough to reset it (but with many vexing inventory discrepancies to clean up each time). (Half resolved)
- Duplicating an entire record and then selectively editing it, and forgetting to change the permanent ID. I don’t know how to safeguard against this. Pasterecord doesn’t seem to trigger .NewRecord, so I’m not sure what else to try. A warning would be enough to satisfy me, or making the permanent ID field active and clearing out the duplicate ID, or setting the ID based on the “next record ID#.” (Not resolved)
Any suggestions for robustly defending our database against duplicate IDs?