Hi Katrin and the rest of the Koha world, Curiouser and curiouser... Possible clue: I started out using 3.12 and I've upgraded the same database since then. Eventually when I can replace the server I'll probably start with a fresh installation. Pretty obvious: I don't know SQL very well. On to the puzzle: Here's one of the records, biblio 13098 (below my signature). I downloaded the whole batch from Rittenhouse and added Koha-specific things, including EBOOK in the 942$c field. If I put the ISBN-10, ISBN-13, or the biblio into the search box, I get the record. Here's the original script George sent me, which worked on a few of the ISBNs. Unfortunately, I didn't export the records before I deleted them, so I can't compare those with the ones that didn't delete. I replaced ISBN-1, ISBN-2, etc., with the real ISBNs. SELECT biblioitems.biblionumber, biblioitems.isbn FROM biblioitems WHERE (biblioitems.isbn LIKE "ISBN-1") OR (biblioitems.isbn LIKE "ISBN-2") . . . etc. When I run this report I get the titles and a URL with the bib number, including 13098: SELECT CONCAT('<a href="http://', (SELECT value FROM systempreferences WHERE variable='staffClientBaseURL'), '/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '">', biblionumber, '</a>') AS 'biblionumber', title FROM biblio WHERE biblionumber NOT IN (SELECT biblionumber FROM items) I went to the Query MARC section and tried this script, which brought up the ISBN for biblio 13098: SELECT ExtractValue(( SELECT metadata FROM biblio_metadata WHERE biblionumber=13098), '//datafield[@tag="020"]/subfield[@code>="a"]') AS ITEM HOWEVER..... This script from the reports library doesn't bring up EBOOKs. Is it because it starts out with select from items? SELECT items.price,items.replacementprice,biblio.title,biblio.author,items.itemcallnumber FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE items.homebranch=<<Home branch|branches>> ORDER BY items.itemcallnumber ASC And when I go to Tools, Export Data there doesn't seem to be any way to export biblio 13098. I've tried From biblio number 13098 to 13098, Item type EBOOK, I uploaded a file with 13098 on one line--each time it comes up with a MARC file with zero bytes. If I export without any restrictions, it includes everything except the ebooks. What I'm probably going to do is undo the import of all the 5000 or so R2library MARC records, download the current set, and start fresh. That will work, but it's not an especially elegant solution. Still, if someone can figure out why these records aren't showing up, I'd love to know. Fred King, AHIP Medical Librarian, MedStar Washington Hospital Center fred.king@medstar.net 202-877-6670 ORCID 0000-0001-5266-0279 MedStar Authors Catalog: http://medstarauthors.org A library in the middle of a community is a cross between an emergency exit, a life-raft and a festival. They are cathedrals of the mind; hospitals of the soul; theme parks of the imagination. On a cold rainy island, they are the only sheltered public spaces where you are not a consumer, but a citizen instead. --Caitlin Moran =LDR 01275cam a2200409 i 4500 =001 1678481 =005 20170321084421.0 =008 150722s2017\\\\maua\\\\\b\\\\001\0\eng\d =010 \\$a 2015943888 =035 \\$9101678481 =020 \\$a9781111542795 =020 \\$a1111542791 =040 \\$aBTCTA$beng$erda$cBTCTA$dYDXCP$dOCLCQ$dIPL =041 09$aeng =042 \\$anlmcopyc =044 \\$9United States =050 \4$aSF745$b.L45 2017 =060 00$a2016 B-261 =060 10$aSF 745 =100 1\$aLawhead, James B.,$eauthor. =245 10$aIntroduction to veterinary science /$cJames B. Lawhead, Meecee Baker. =250 \\$aThird edition. =264 \1$aBoston, MA :$bCengage Learning$c[2017] =336 \\$atext$btxt$2rdacontent =337 \\$aunmediated$bn$2rdamedia =338 \\$avolume$bnc$2rdacarrier =504 \\$aIncludes bibliographical references (page 370-374) and index. =650 12$aVeterinary Medicine =650 22$aAnimal Diseases =650 22$aAnimals, Domestic =700 1\$aBaker, MeeCee,$eauthor. =300 \\$a online resource =538 \\$a Mode of Access: World Wide Web =655 \4$a Electronic books =942 \\$cEBOOK =856 4\$zConnect to this resource online$uhttp://www.r2library.com/Resource/Title/1111542791 =999 \\$c13098$d13098 -----Original Message----- From: Koha <koha-bounces@lists.katipo.co.nz> On Behalf Of Katrin Fischer Sent: Sunday, September 01, 2019 2:41 PM To: koha@lists.katipo.co.nz Subject: [EXTERNAL] Re: [Koha] Can't include itype in report (was Bulk search/delete by ISBNs?) ** ATTENTION: This email originated from outside the MedStar network. ** DO NOT CLICK links or attachments unless you recognize the sender and know the content is safe. Hi Fred, did you set the item type EBOOK in 942$c by chance? If yes, then the itemtype search will find them even if they are no items and we can also craft a SQL query to find them. isbn.biblioitems shoudl be set from the data in your bibliographic record independent of existing items. Can you tell us what you tried? The 'Query MARC' section of the reports library might give you some pointers: https://urldefense.proofpoint.com/v2/url?u=https-3A__wiki.koha-2Dcommunity.org_wiki_SQL-5FReports-5FLibrary-23Query-5FMARC&d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmjyC51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8SfdIcAXTzwY-mYfpOQmsCsT3zjci0LJ0&s=QS-gvkNeaydpkDejg4ytJ9iYYV58FUEKwDSLhggIQ_E&e= Hope this helps, katrin On 30.08.19 19:19, King, Fred wrote:
Thanks to Chris and a script from George Williams, it worked like a charm.
Almost. With a few of them.
After a lot of trial and error, I think I might have found the problem: my SQL queries can’t find the item type EBOOK, or any data associated with bib records of that type. Since there aren’t any items associated with these records, that makes sense, but…
Is there any way to run an SQL report looking for something in the biblioitems.isbn field when there aren’t any items attached? The 020 field has them, and I can put the ISBN in the search box, so the system knows they’re there.
Thanks! And if I’m totally confused, could someone point me in the right direction?
I’m about to leave for the day, and Monday is a holiday here in the States, so I might not be checking e-mail for a while. Have a great weekend everybody.
Fred King, AHIP Medical Librarian, MedStar Washington Hospital Center mailto:fred.king@medstar.net<mailto:fred.king@medstar.net> 202-877-6670 ORCID 0000-0001-5266-0279 MedStar Authors Catalog: https://urldefense.proofpoint.com/v2/url?u=http-3A__medstarauthors.org &d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmjyC5 1IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8SfdIcAXTzwY-mYfpOQmsCsT3z jci0LJ0&s=dgbosaKixmkLdEItpvumGZe1WUOArJbwvUAdfnStmJo&e=
Rest is not idleness, and to lie sometimes on the grass under trees on a summer's day, listening to the murmur of the water, or watching the clouds float across the sky, is by no means a waste of time. --Sir John Lubbock, The Use of Life (1894)
From: Chris Cormack <mailto:chrisc@catalyst.net.nz> Sent: Thursday, August 29, 2019 5:23 PM To: mailto:koha@lists.katipo.co.nz; King, Fred <mailto:Fred.King@Medstar.net> Subject: [EXTERNAL] Re: [Koha] Bulk search/delete by ISBNs?
** ATTENTION: This email originated from outside the MedStar network. ** DO NOT CLICK links or attachments unless you recognize the sender and know the content is safe. Hi Fred
You might be able to write a report to get all biblio with no items attached, of a certain type and then use that list of biblionumbers in the bulk biblio delete.
Hope this helps Chris On 30 August 2019 1:51:31 AM NZST, "King, Fred" <mailto:Fred.King@Medstar.net<mailto:Fred.King@Medstar.net>> wrote:
Esteemed Colleagues,
My library has a collection of about 5000 online books that are in Koha with bib records and no items. The publisher periodically archives titles so they're no longer available, and I have a list of them that I'd like to remove from Koha. The problem is that my list consists of ISBNs and that there are over 450 of them. I could search and delete one-by-one, but that's an awful lot of searching and deleting. Is there an easier way?
We're on Koha 17.05* and Ubuntu 16.04.
*I know, I know, but I'm hoping to get a new server before I upgrade.
Fred King, AHIP Medical Librarian, MedStar Washington Hospital Center mailto:fred.king@medstar.net<mailto:fred.king@medstar.net%3E<mailto:fred.king @medstar.net%3cmailto:fred.king@medstar.net%3E> 202-877-6670 ORCID 0000-0001-5266-0279 MedStar Authors Catalog: https://urldefense.proofpoint.com/v2/url?u=http-3A__medstarauthors.org &d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmjyC5 1IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8SfdIcAXTzwY-mYfpOQmsCsT3z jci0LJ0&s=dgbosaKixmkLdEItpvumGZe1WUOArJbwvUAdfnStmJo&e= <https://urldefense.proofpoint.com/v2/url?u=http-3A__medstarauthors.or g&d=DwMFaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmjyC 51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=E4eNiVIJtiO21Ua5DyrKj9CVOqS-pVLhHLX rzdtSCxA&s=nxjZBoMiTfLJ423yCYGyfFHjNanefLzmsCqbKH1soE0&e=>
Rest is not idleness, and to lie sometimes on the grass under trees on a summer's day, listening to the murmur of the water, or watching the clouds float across the sky, is by no means a waste of time. --Sir John Lubbock, The Use of Life (1894)
________________________________
MedStar Health is a not-for-profit, integrated healthcare delivery system, the largest in Maryland and the Washington, D.C., region. Nationally recognized for clinical quality in heart, orthopaedics, cancer and GI.
IMPORTANT: This e-mail (including any attachments) may contain information that is private, confidential, or protected by attorney-client or other privilege. If you received this e-mail in error, please delete it from your system without copying it and notify sender by reply e-mail, so that our records can be corrected... Thank you.
Help conserve valuable resources - only print this email if necessary.
________________________________
Koha mailing list https://urldefense.proofpoint.com/v2/url?u=http-3A__koha-2Dcommunity.o rg&d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmjy C51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8SfdIcAXTzwY-mYfpOQmsCsT 3zjci0LJ0&s=EGh9Zx7lU1KNDeVVh24O_8nNFzrhJBv3ZZ46fXoubo8&e= <https://urldefense.proofpoint.com/v2/url?u=http-3A__koha-2Dcommunity. org&d=DwMFaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmj yC51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=E4eNiVIJtiO21Ua5DyrKj9CVOqS-pVLhH LXrzdtSCxA&s=6sMFUxsF0xaQemqufEI6aYJ45LI1E5iAfi3JhLkRFC8&e=> mailto:Koha@lists.katipo.co.nz<mailto:Koha@lists.katipo.co.nz> https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.katipo.co.n z_mailman_listinfo_koha&d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVx j8-bJ04M&r=vKh6XwOmjyC51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8Sf dIcAXTzwY-mYfpOQmsCsT3zjci0LJ0&s=55x7b41_Q-1BpXJY8T5PZIjrCv8Z3gLMMAjLH d9vUpg&e= <https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.katipo.co. nz_mailman_listinfo_koha&d=DwMFaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndV xj8-bJ04M&r=vKh6XwOmjyC51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=E4eNiVIJtiO2 1Ua5DyrKj9CVOqS-pVLhHLXrzdtSCxA&s=16UQvxVDiNyisn80ywDdWsKJ0Skgz1BnItHU hoVZrxU&e=>
-- Sent from my Android device with K-9 Mail. Please excuse my brevity. _______________________________________________ Koha mailing list https://urldefense.proofpoint.com/v2/url?u=http-3A__koha-2Dcommunity.o rg&d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmjy C51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8SfdIcAXTzwY-mYfpOQmsCsT 3zjci0LJ0&s=EGh9Zx7lU1KNDeVVh24O_8nNFzrhJBv3ZZ46fXoubo8&e= mailto:Koha@lists.katipo.co.nz https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.katipo.co.n z_mailman_listinfo_koha&d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVx j8-bJ04M&r=vKh6XwOmjyC51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8Sf dIcAXTzwY-mYfpOQmsCsT3zjci0LJ0&s=55x7b41_Q-1BpXJY8T5PZIjrCv8Z3gLMMAjLH d9vUpg&e=
Koha mailing list https://urldefense.proofpoint.com/v2/url?u=http-3A__koha-2Dcommunity.org&d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmjyC51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8SfdIcAXTzwY-mYfpOQmsCsT3zjci0LJ0&s=EGh9Zx7lU1KNDeVVh24O_8nNFzrhJBv3ZZ46fXoubo8&e= mailto:Koha@lists.katipo.co.nz https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.katipo.co.nz_mailman_listinfo_koha&d=DwIGaQ&c=RvBXVp2Kc-itN3g6r3sN0QK_zL4whPpndVxj8-bJ04M&r=vKh6XwOmjyC51IkP1OfsdjQZoWT2vpi6VZl8El8EPRI&m=PyB66W6hWy8SfdIcAXTzwY-mYfpOQmsCsT3zjci0LJ0&s=55x7b41_Q-1BpXJY8T5PZIjrCv8Z3gLMMAjLHd9vUpg&e=