To construct an unique column

[Krishnam Prabhu]

Query:
I have e-books in my library with a few custom columns. One such custom column labeled as "Accession Number" so that to avoid duplications (i.e.) No e-book record should have the same Accession Number―An Unique Column. Now when adding a new set of e-books to the same library, but unfortunately if I specified a previous mapped Accession number to the new e-book, then it would be a tragedy!

Suggest:
I need to set a rule or validation message warning when I repeat the same Accession Number. This also implies to have the same structure to ISBN [Pre-Defined Column], so that no e-books have the same ISBN.

Gist:
Finally, to say that all records should be unique but not identical.

[kovidgoyal]

Use the identifiers for that.

[ownedbycats]

Originally Posted by Krishnam Prabhu

Query:
I have e-books in my library with a few custom columns. One such custom column labeled as "Accession Number" so that to avoid duplications (i.e.) No e-book record should have the same Accession Number―An Unique Column.

Due to the way the database works, Calibre already has a unique ID for each book record. Just add {id} to a column-built-from-other-columns.

[Krishnam Prabhu]

I'll make it clear. I need the pre-defined column (field) or custom column (field) should be unique. If by mistake I entered the same value, then it should not save. Kindly look at the following sample library structure:

Accession No. Title ISBN
AN-001 Book01 9781234567890
AN-002 Book02 9781234567891
AN-003 Book03 9781234567892
AN-003 Book04 9781234567893
AN-005 Book05 9781234567894
AN-006 Book06 9781234567895
AN-007 Book07 9781234567896
AN-008 Book08 9781234567897
AN-009 Book09 9781234567897
AN-010 Book10 9781234567899

The "AN-003" and "9781234567897" are unfortunately duplicated in Accession No. and ISBN respectively. Now my request is to get a warning message to state that the new value that is entered is already existing in the library. So software should prevent me from input duplicate data.

[theducks]

Originally Posted by Krishnam Prabhu

I'll make it clear. I need the pre-defined column (field) or custom column (field) should be unique. If by mistake I entered the same value, then it should not save. Kindly look at the following sample library structure:

Accession No. Title ISBN
AN-001 Book01 9781234567890
AN-002 Book02 9781234567891
AN-003 Book03 9781234567892
AN-003 Book04 9781234567893
AN-005 Book05 9781234567894
AN-006 Book06 9781234567895
AN-007 Book07 9781234567896
AN-008 Book08 9781234567897
AN-009 Book09 9781234567897
AN-010 Book10 9781234567899

The "AN-003" and "9781234567897" are unfortunately duplicated in Accession No. and ISBN respectively. Now my request is to get a warning message to state that the new value that is entered is already existing in the library. So software should prevent me from input duplicate data.

It is not the software's job to prevent you from making 'GIGO errors'.
Find Duplicates Plugin (identifiers: ISBN or identifiers: Title. 2 passes required to find both) will locate those issues for YOU to resolve.
Some of us WANT edition x-references references to exist.

[itimpi]

What is wrong with using the existing standard column ‘id’ that is guaranteed to be unique? Not sure what is the purpose of trying to replicate its functionality.

[theducks]

Originally Posted by itimpi

What is wrong with using the existing standard column ‘id’ that is guaranteed to be unique? Not sure what is the purpose of trying to replicate its functionality.

{ID} is per Library. If you copy/move a book, it gets a new ID
(I can't remember if a rename of Title or Author causes this also)
I remember using -{id} at the end of my file template when I first started with my EZreader. when I shifted computers/Libraries (on device status was all ???)
Just believe that {id} is not fool proof, either.

[chaley]

I understand you as saying that you want the ability to specify that a single-valued column's value be unique in the database. There is nothing in base calibre that will do such a check.

It can be done in the Action Chains plugin using a chain that checks for duplicates when a book record is modified, a process that will probably be slow but has the advantage of not needing any programming. Ask in that thread if you want to try this and need help.

Best would be to write a custom plugin that uses calibre's metadata_changed event and cache.get_item_id(). The plugin would check if a value (an 'item_name') is already in use and if so then show a warning. It could mark the books. I think this can only flag the change, not prevent it from being made. I also wonder what would happen if the change is being made by something like bulk metadata edit. My guess is that you would need to write the plugin, as no one who does plugins has needed/wanted this ability.

[BetterRed]

Originally Posted by theducks

{ID} is per Library. If you copy/move a book, it gets a new ID
(I can't remember if a rename of Title or Author causes this also)
I remember using -{id} at the end of my file template when I first started with my EZreader. when I shifted computers/Libraries (on device status was all ???)
Just believe that {id} is not fool proof, either.

↑ ↑ ↑ ✔

See ==>> Change Author - Version 0.9.4

Why not a File Type plugin that generates the Accession Number value by incrementing a preference setting (global or per library) when a book is added. Along with a Library Closed plugin that checks that a column is unique.

BR