'catalognumber is not unique' error

[willem]

In one of my collections I’m getting an error message saying that my catalog numbers are not unique, and I am unable to create a new collectionobject.
Specify 7 Crash Report - 2025-03-10T13_09_05.999Z.txt (424.3 KB)

[Grant]

Hi @willem,

Thanks for your message! On your instance it seems there is already a collection object (CO) with that catalog number.

Crash Report Breakdown

Your crash report indicates the following: Collectionobject must have unique catalognumber in collection

The error includes the catalog number for the CO you were trying to create as well as the identifier for the CO that is in conflict.

{
  "table": "Collectionobject",
  "localizationKey": "childFieldNotUnique",
  "fieldName": "catalognumber",
  "fieldData": {
    "catalognumber": "000052750"
  },
  "parentField": "collection",
  "parentData": {
    "collection": "Collection object (4)"
  },
  "conflicting": [
    68753
  ]
}

It looks like the catalog number Specify is trying to assign was 000052750, but this is already in use by this record in the National Fish Collection: /specify/view/collectionobject/68753

Suspected Issue

When I search for CO records in the database and sort by catalog number in descending order, Specify is treating ⁠000052749 as the highest number. This is because it is using string sorting rather than numerical sorting.

It seems the auto-incrementing logic is following this pattern, but in doing so is trying to generate a catalog number that exists.

You are currently using a development version of Specify from September 2024. Can you back up your database, update your Specify 7 instance to the latest stable release (v7.10.1 as of today), and let us know if the issue persists?

Thank you!

[Grant]

Hi @willem,

The issue seems to be due to one of the catalog numbers in the “National Fish Collection” being captured, at the database level, as simply a 5-digit number rather than conforming to the 9-digit standard for the collection.

CollectionObjectID	CatalogNumber	TimestampCreated	TimestampModified
106494	52749	2004-06-28 08:35:48	2025-02-27 15:40:59
410594	001412022	2025-02-26 11:46:08	2025-02-26 11:46:08

After I ran the following SQL command on the database, the issue was resolved:

update collectionobject set CatalogNumber = '000052749' where CollectionObjectID = 106494;

In practice, simply remove the old catalog number, re-enter it, and save the record.

Now you can auto-increment without an error!
This is on your live database as well.

The issue now is that there are two collection objects with the same catalog number (you can verify this by searching for ‘000052749’ in simple search).

Since Specify expects each catalog number to be unique within the collection by default, you won’t be able to modify these records until one of them is assigned a new number (or if you remove the number).