Feature Request: Improvements to SQL searches

[DrJJWMac]

I find the SQL searches are invaluable in managing the integrity across citations. Find empty abstracts. Find "funky notes". Find ...

Please consider these two improvements.

* Add more fields to the SQL. Three important ones that are missing include DOI, PMID, and PMCID. Searching on missing DOI is particularly useful as a time-saver to find and then fix references so that they can subsequently be Autocompleted.

* In the Fields pop-up list to create an SQL search, split the User1 through User20 fields below all other primary fields (but before the id, type, "hit" and other meta-fields).

[Jon]

You misunderstand the purpose of the popup list. It contains the actual names of the fields in the database, which you'd have to use if you create your own SQL search. There is no field named "doi", and a search for the contents of field "doi" would generate an error. A search in field user17, however, would find a DOI. I can add a hint to the list for those fields (e.g. user17 = DOI) to remind users of its typical content.

As a side note, that list is primarily intended as a cheat sheet to remind users of the actual names of the fields. For experienced users, it's far faster to type the field names when you're creating a search than to use this menu to insert the name for you.

Jon
Sonny Software

[DrJJWMac]

OK. I now see where <doi> and <pmid> are noted to be mapped to user17 and user18 on page 299 in the User Manual. This appears to be the only place where the association is made.

In summary, these changes would help

* Page 208 in User Manual -- include a map to describe how all userN fields fit by default to specific field titles where appropriate
...
Code u17, Category User17, Default Label DOI
Code u18, Category User18, Default Label PMID
...

* Page 356 in User Manual -- repeat the same Default Label information as a reminder
...
user17 (DOI)
user18 (PMID)
...

With this, you would have no need to modify the pop-up dialog.

In general, I have to question why 20 fields have to be called by their official userN names. Yes, this generic approach gives the appearance for great flexibility. But darn if this approach does not cause confusion for someone trying to remember which userN field contains what key data. I would almost wish for only two or three userN fields, replacing all the others by fixed meta-content relevant names (e.g. u17 = user17 gets replaced by doi).

[Jon]

We have people from many different disciplines with different needs. And remember, when the Bookends database schema was designed over 20 years ago DOIs were a novelty and PubMed Central didn't even exist. We created a lot of multi-purpose User fields and over time have used them ourselves to add DOI and PMCID and other Type-specific field labels (note that not all Types have the same User field label. For example, User18 isn't labeled PMID for Books and most other Types). If we were creating a database from scratch today we would probably hard-wire those fields, but no doubt still have "user fields" for future flexibility.