SQL script to clear contents of Extra fields

iphiphiph

Using different citation count addons has led to my Extra fields becoming an unsortable mess, so I would like to batch clear them before running zotero-citationcounts which does precisely what I want to do.

Simply running zotero-citationcounts is not enough, since it does not overwrite the Extra field but simply appends citations to old entries, including those from outdated citation addons. This is unlikely to change in future versions (see https://github.com/eschnett/zotero-citationcounts/issues/2).

I would like to do this in any way possible, including raw SQL scripts. Yes, I do understand the risks involved.

There is an old thread from 2010 about this issue (https://forums.zotero.org/discussion/12992/a-way-to-delete-all-info-from-all-extra-fields). The proposed SQL script there sadly does not work as of 2021-01-21.

bwiernik

Don’t use SQL. Use the JavaScript API with that Run Script tool in the Tools menu. That’s safe.
https://www.zotero.org/support/dev/client_coding/javascript_api

iphiphiph

Thank you! With zero experience of JavaScript, I managed to modify one of the examples shown on the page you linked to achieve what I wanted:

var fieldName = "extra";
var newValue = "";

var fieldID = Zotero.ItemFields.getID(fieldName);
var s = new Zotero.Search();
s.libraryID = ZoteroPane.getSelectedLibraryID();
var ids = await s.search();
if (!ids.length) {
return "No items found";
}
await Zotero.DB.executeTransaction(async function () {
for (let id of ids) {
let item = await Zotero.Items.getAsync(id);
let mappedFieldID = Zotero.ItemFields.getFieldIDFromTypeAndBase(item.itemTypeID, fieldName);
item.setField(mappedFieldID ? mappedFieldID : fieldID, newValue);
await item.save();
}
});
return ids.length + " item(s) updated";

bwiernik

Great work!

dstillman

@iphiphiph: Note that, by doing this, you'll wipe out some valid metadata saved by translators to Extra in advance of additional fields being added to Zotero. E.g., DOIs for some item types, PubMed IDs, and various other fields that haven't yet been added, some of which may very well be required for proper citations. You'd be better off having the script selectively strip the lines containing the citation count formats you no longer want.