- Koha - lists.katipo.co.nz

Excel to Koha year wise filtration problem
by Mahesh Palamuttath 20 Mar '20

20 Mar '20

How can we add year in marc tag 008 for the bulk data migration from excel? like we do 260$c for the year?. The reason why I am asking this, year-wise filtration works only when we add the year in marc tag 008 07-10 (thanks to IGD sir). we want to import all excel data to koha with year-wise filtration. please help us *Mahesh Palamuttath* M.Phil. Research Scholar Department of Library & Information Science University Of Calicut Mob:9567664972,9544264972 <https://hangouts.google.com/?action=chat&pn=%2B919544264972&hl=en&authuser=0> Blogs: http://libtechnophile.blogspot.com https://ooruthendies.blogspot.com

2 1

Purchase Suggestions in Koha
by Stephen Graham 20 Mar '20

20 Mar '20

Hi All - we've just switched the purchase suggestion form on for our users, and was just wondering how people handle the suggestions once(if) satisfied? I see you can manually delete them, and I see that there is a script which runs from the cron to delete suggestions x days hold. One thing we are interested in is how many books we actually purchase based on a suggestion. Obviously if you delete the suggestions you can't report on this (There's no deleted_suggestions table). We thought about recording in a spreadsheet, but this doesn't feel right - there should be a way in the LMS to record this. Do other institutions create a custom MARC field to indicate if the item was bought based on a suggestion? Or is it not something of interest really? Cheers, Stephen -------------------------------------- Stephen Graham Library Technology Consultant Content and Collections Team Library and Computing Services University of Hertfordshire Tel: 01707 286111 Ext: 77751 Email: s.graham4(a)herts.ac.uk<mailto:s.graham4@herts.ac.uk>

2 1

[KOHA] SQL CIRCULATION REPORT
by muirunyeri 19 Mar '20

19 Mar '20

Hi Rogan, After following your advice to have same filters for both queries, I now are able to get the results I desire. I also removed the join for biblioitems and indeed it was not necessary. No words can express my sincere appreciation for the time you took to help me out. I can only say a big big thank you. Thank you to everyone who tried in their own ways in finding me a solution. Warm RegardsJames-------- Original message --------From: rogan.hamby(a)gmail.com Date: 18/03/2020 15:53 (GMT+03:00) To: muirunyeri <muirunyeri(a)gmail.com> Cc: Koha <koha(a)lists.katipo.co.nz> Subject: Re: [KOHA] SQL CIRCULATION REPORT- how to SUM Hi James, A few thoughts from skimming it. 1) I don't see where you're using biblioitems anywhere in the report unless I'm just not seeing it so you probably don't need it for a join. I don't think it'll hurt anything other than slow the report less than you'll even notice but still ... if not needed I'd cut it out. 2) you're going to want the same filters in the second query as the first to make sure you get the same numbers 3) you are using a SUM on the statistics type but you can only sum numbers you probably want a COUNT there, which you do in the first query but not the second On Sun, Mar 15, 2020 at 12:38 AM muirunyeri <muirunyeri(a)gmail.com> wrote: Hi Rogan, I reintroduced two filters date abd itemtype. So that i can sekect any date be it weekly monthly or quarterly etc. I also have different itemtypes sone of which do not start wih a digit and I need to filter them out yo get accurate results. (Your advice on whether the filters might be sn issue is verywelcome) Below is my query. SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS", count(statistics.type) AS 'NUMBER' FROM borrowers LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber) WHERE statistics.type IN ('issue', 'renew') AND date(datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND items.itype = <<Item type|itemtypes>> GROUP BY SUBSTRING(itemcallnumber,1,1) UNION ALL SELECT "TOTAL" AS "CLASS", SUM(statistics.type) AS 'NUMBER' FROM borrowers LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber) WHERE statistics.type IN ('issue', 'renew') Sent from my Samsung Galaxy smartphone.-------- Original message --------From: rogan.hamby(a)gmail.com Date: 15/03/2020 00:48 (GMT+03:00) To: muiru james <muirunyeri(a)gmail.com>, Koha <koha(a)lists.katipo.co.nz> Subject: Re: [KOHA] SQL CIRCULATION REPORT- how to SUM Hi James, The query I posted worked with my quick test. Could you post your query as it is now? On Sat, Mar 14, 2020 at 9:52 AM muiru james <muirunyeri(a)gmail.com> wrote: Hi Rogan and All, I cant thank you enough for the tremendous effort you have put towards helping me solve my problem. You do indeed understand well what I need. The UNION ALL is working well by adding a new row below the last class group. However the SUM function is returning a 0 (zero). It seems its not adding up the numbers. Please dont give up but help me find out the last remaining bit. We are almost there. Most appreciated James On Thu, Mar 12, 2020 at 2:57 PM <rogan.hamby(a)gmail.com> wrote: Hi James, If I'm understanding correctly you want an additional row giving a sum of all the numbers in addition to the per class one? The simplest way it occurs to me to do that is with a UNION ALL statement. I've taken some filters out for brevity but this illustrates the approach: SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS", count(statistics.type) AS 'NUMBER' FROM borrowers LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber) WHERE statistics.type IN ('issue', 'renew') GROUP BY SUBSTRING(itemcallnumber,1,1) UNION ALL SELECT "All Classes" AS "CLASS", SUM(statistics.type) AS 'NUMBER' FROM borrowers LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber) WHERE statistics.type IN ('issue', 'renew') Basically with UNION ALL you can create other queries that don't have to be tied to the data of the previous queries so long as the columns line up. On Thu, Mar 12, 2020 at 3:04 AM muiru james <muirunyeri(a)gmail.com> wrote: Hi Rogan and Team, I really appreciate your assistance towards my problem. The query does indeed work to answer my need. Another query was also pushed to me towards the same. I'm now looking for a way to SUM my answer to get the total number of books issued. Any suggestions would be most welcome for any of the 2 queries. The other SQL query is as below: - SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS", count(statistics.type) AS 'NUMBER' FROM borrowers LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber) WHERE statistics.type IN ('issue', 'renew') AND date(statistics.datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND statistics.itemtype = 'BK' GROUP BY SUBSTRING(itemcallnumber,1,1) ORDER BY items.itemcallnumber ASC N.B My desire is to SUM the NUMBER column as TOTAL. Please assist team Warm Regards. JAMES On Fri, Mar 6, 2020 at 7:10 PM <rogan.hamby(a)gmail.com> wrote: Hi James, One simple way to handle it is with a case statement. The following example has a static between filter for the dates but it illustrates this approach. You can also do some cleaning to make sure there aren't spaces in front and it assumes that all of your DDC numbers in fact start with a digit. SELECT COUNT(*), CASE WHEN LEFT(it.itemcallnumber,1) = '0' THEN '000s' WHEN LEFT(it.itemcallnumber,1) = '1' THEN '100s' WHEN LEFT(it.itemcallnumber,1) = '2' THEN '200s' WHEN LEFT(it.itemcallnumber,1) = '3' THEN '300s' WHEN LEFT(it.itemcallnumber,1) = '4' THEN '400s' WHEN LEFT(it.itemcallnumber,1) = '5' THEN '500s' WHEN LEFT(it.itemcallnumber,1) = '6' THEN '600s' WHEN LEFT(it.itemcallnumber,1) = '7' THEN '700s' WHEN LEFT(it.itemcallnumber,1) = '8' THEN '800s' WHEN LEFT(it.itemcallnumber,1) = '9' THEN '900s' ELSE 'OTHER' END from (SELECT * FROM issues UNION ALL SELECT * FROM old_issues) i JOIN items it ON it.itemnumber = i.itemnumber WHERE DATE(i.timestamp) BETWEEN '2020-02-01' AND '2020-02-29' GROUP BY 2 ORDER BY 2; On Thu, Mar 5, 2020 at 11:06 AM muiru james <muirunyeri(a)gmail.com> wrote: Dear All, My library uses DDC scheme of classification and we are looking for a report that will count items checked out allowing us to select day(s) of issue, select itemtype and group the statistics by DDC 10 broad classes. *EXAMPLE* IN a date range say 1st-31st March 2020. *DDC Class | NO. of books issued* 000 - 12 100 - 26 200 - 157 . . . 900 - 230 The report can generate statistics for a day, month, quarter, e.t.c. Any assistance however close will be greatly appreciated. Thank you as you prepare to help James _______________________________________________ Koha mailing list http://koha-community.org Koha(a)lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

1 0

Cataloger Work File Isolation
by Decka David 19 Mar '20

19 Mar '20

Dear Koha Team, One of our library's cataloguing staff is seeking clarity if Koha system can be able to cater to provide and protect individual logged cataloguing records and without other cataloguer staff to work on the same record/item. It's a bit confusing if I can run koha librarian activity report to identify his/her activity for different periods of time. Can anyone kindly clarifies how I can help protect cataloguers work files/activity? Thank you for every support of guide and advice *Decka David* Matheson Library | PNG University of Technology | PMB | Lae, 411 | Morobe Province | PAPUA NEW GUINEA Alternate E: decka.david(a)gmail.com | P: 657 4734353 <%28657%29%20473-4353> | F: 675 4374363

2 1

Delete Biblio if no matching items found?
by Laxmi Lal Menaria 19 Mar '20

19 Mar '20

Hello All, I have imported a MARC file 2-3 times with the same records, getting errors some times so fixed and imported the same again and again. Now when I use search, getting duplicate results for the books, few of the have barcode and others shows 'No items". So fix the issue I have created a query which delete unused biblio from the table, please let me know will it create another issue or not? *delete from biblio where biblionumber not in (select biblionumber from items)delete from biblioitems where biblioitemnumber not in (select biblioitemnumber from items)* -- Thanks, Laxmilal Menaria | +91 982 955 3793 | http://cloud-computing.solutions/

3 2

Search in not working
by Vanesa E. Berasa 19 Mar '20

19 Mar '20

Hi, In the administration interfaz koha advanced search, authorities search and periodicals search is not working. Just give me a page in blank with the words not found (like dont found the page not the term). It works in koha opac. Any idea about what is happening? -- *Lic. Vanesa E. Berasa* Biblioteca Central Facultad de Agronomía Universidad de Buenos Aires (UBA) ----- Av. San Martin 4453 Ciudad de Buenos Aires - Argentina tel. (005411) 5287-0422 - (005411) 5287-0307 Biblioteca Arata Correo Electrónico:* berasa(a)agro.uba.ar <berasa(a)agro.uba.ar>* *Biblioteca Central* Sitio Web: http://www.agro.uba.ar/biblioteca Catálogo Ceiba: http://ceiba.agro.uba.ar/ FAUBA Digital http://ri.agro.uba.ar/ <http://ri.agro.uba.ar/cgi-bin/library.cgi> Estamos en Facebook: http://www.facebook.com/BibliotecaCentralFauba *Biblioteca Arata* Sitio Web: http://www.agro.uba.ar/biblioteca/arata/ Catálogo Arata: http://catalogobiblioteca.agro.uba.ar/Arata/arata.asp Facebook: http://www.facebook.com/ArataBiblio 2020 - Año Internacional de la Sanidad Vegetal <https://undocs.org/es/A/RES/73/252> 2020 - Año del General Manuel Belgrano <https://www.boletinoficial.gob.ar/detalleAviso/primera/224308/20200103>

1 0

Virtual Hackathon
by Renvoize, Martin 18 Mar '20

18 Mar '20

Hi All, As many of you are already aware we had to cancel the annual community Hackfest in Marseille this year due to COVID-19. However, we are intending to run an open to all virtual Hackathon as an alternative. Some ideas have been added to the Wiki <https://wiki.koha-community.org/wiki/Virtual_Hackfest_2020>and a Trello <https://trello.com/b/8PHH9gJ2/virtual-hackfest2020bugzilla> board exists to capture peoples intentions and work efforts over the week. To get us started, ByWater have offered to host a couple of live streams: * Wednesday, March 25th Koha Virtual Hackfest - Documentation 9:00am EST / 1:00pm UTC https://www.youtube.com/user/bywatersolutions/live ------------------------------ * Friday, March 27th Koha Virtual Hackfest - Code Spaghetti 11:00am EST / 3:00pm UTC https://www.youtube.com/user/bywatersolutions/live I intend to host at least one myself aimed at getting people started with how to pick bugs to test and use the community sandboxes to sign them off. IRC <https://koha-community.org/get-involved/irc/> will be full of people keen to help and get involved to do pop in. I look forward to seeing many of you online next week, *Martin Renvoize* <https://www.ptfs-europe.com> Development Team Manager Community Release Manager (19.11, 20.05) *Phone:* +44 (0) 1483 378728 *Mobile:* +44 (0) 7725 985 636 *Email:* martin.renvoize(a)ptfs-europe.com *Fax:* +44 (0) 800 756 6384 www.ptfs-europe.com Registered in the United Kingdom No. 06416372 VAT Reg No. 925 7211 30 The information contained in this email message may be privileged, confidential and protected from disclosure. If you are not the intended recipient, any dissemination, distribution or copying is strictly prohibited. If you think that you have received this email message in error, please email the sender at info(a)ptfs-europe.com

1 0

Elasticsearch setup
by Tom Hanstra 18 Mar '20

18 Mar '20

In the documentation I'm reading about the use of elasticsearch, all information is completely Debian specific, including both the use of kohadevbox and debian software installations of the koha-elasticsearch metapackage. For those of us who don't use Debian (yes, there are some of us), is there any documentation of more complete ElasticSearch setup commands and information of what is inside of the koha-elasticsearch metapackage and other supporting software? I'd rather not have to reverse engineer those pieces from Debian, but the documentation seems to assume installation has one or the other of the above Debian based packages. I'm assuming that Koha can work with Elasticsearch on any Linux distribution, with the right setup. If so, can someone help fill in the gaps between Debian and the other Linuxes. Thanks, Tom -- *Tom Hanstra* *Sr. Systems Administrator* hanstra(a)nd.edu

4 8

Re: [Koha] [KOHA] SQL CIRCULATION REPORT- how to SUM
by muirunyeri 18 Mar '20

18 Mar '20

Hi Rogan, I reintroduced two filters date abd itemtype. So that i can sekect any date be it weekly monthly or quarterly etc. I also have different itemtypes sone of which do not start wih a digit and I need to filter them out yo get accurate results. (Your advice on whether the filters might be sn issue is verywelcome) Below is my query. SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS", count(statistics.type) AS 'NUMBER' FROM borrowers LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber) WHERE statistics.type IN ('issue', 'renew') AND date(datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND items.itype = <<Item type|itemtypes>> GROUP BY SUBSTRING(itemcallnumber,1,1) UNION ALL SELECT "TOTAL" AS "CLASS", SUM(statistics.type) AS 'NUMBER' FROM borrowers LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber) WHERE statistics.type IN ('issue', 'renew') Sent from my Samsung Galaxy smartphone.-------- Original message --------From: rogan.hamby(a)gmail.com Date: 15/03/2020 00:48 (GMT+03:00) To: muiru james <muirunyeri(a)gmail.com>, Koha <koha(a)lists.katipo.co.nz> Subject: Re: [KOHA] SQL CIRCULATION REPORT- how to SUM Hi James, The query I posted worked with my quick test. Could you post your query as it is now? On Sat, Mar 14, 2020 at 9:52 AM muiru james <muirunyeri(a)gmail.com> wrote: Hi Rogan and All, I cant thank you enough for the tremendous effort you have put towards helping me solve my problem. You do indeed understand well what I need. The UNION ALL is working well by adding a new row below the last class group. However the SUM function is returning a 0 (zero). It seems its not adding up the numbers. Please dont give up but help me find out the last remaining bit. We are almost there. Most appreciated James On Thu, Mar 12, 2020 at 2:57 PM <rogan.hamby(a)gmail.com> wrote: Hi James, If I'm understanding correctly you want an additional row giving a sum of all the numbers in addition to the per class one? The simplest way it occurs to me to do that is with a UNION ALL statement. I've taken some filters out for brevity but this illustrates the approach: SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS", count(statistics.type) AS 'NUMBER' FROM borrowers LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber) WHERE statistics.type IN ('issue', 'renew') GROUP BY SUBSTRING(itemcallnumber,1,1) UNION ALL SELECT "All Classes" AS "CLASS", SUM(statistics.type) AS 'NUMBER' FROM borrowers LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber) WHERE statistics.type IN ('issue', 'renew') Basically with UNION ALL you can create other queries that don't have to be tied to the data of the previous queries so long as the columns line up. On Thu, Mar 12, 2020 at 3:04 AM muiru james <muirunyeri(a)gmail.com> wrote: Hi Rogan and Team, I really appreciate your assistance towards my problem. The query does indeed work to answer my need. Another query was also pushed to me towards the same. I'm now looking for a way to SUM my answer to get the total number of books issued. Any suggestions would be most welcome for any of the 2 queries. The other SQL query is as below: - SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS", count(statistics.type) AS 'NUMBER' FROM borrowers LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber) WHERE statistics.type IN ('issue', 'renew') AND date(statistics.datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND statistics.itemtype = 'BK' GROUP BY SUBSTRING(itemcallnumber,1,1) ORDER BY items.itemcallnumber ASC N.B My desire is to SUM the NUMBER column as TOTAL. Please assist team Warm Regards. JAMES On Fri, Mar 6, 2020 at 7:10 PM <rogan.hamby(a)gmail.com> wrote: Hi James, One simple way to handle it is with a case statement. The following example has a static between filter for the dates but it illustrates this approach. You can also do some cleaning to make sure there aren't spaces in front and it assumes that all of your DDC numbers in fact start with a digit. SELECT COUNT(*), CASE WHEN LEFT(it.itemcallnumber,1) = '0' THEN '000s' WHEN LEFT(it.itemcallnumber,1) = '1' THEN '100s' WHEN LEFT(it.itemcallnumber,1) = '2' THEN '200s' WHEN LEFT(it.itemcallnumber,1) = '3' THEN '300s' WHEN LEFT(it.itemcallnumber,1) = '4' THEN '400s' WHEN LEFT(it.itemcallnumber,1) = '5' THEN '500s' WHEN LEFT(it.itemcallnumber,1) = '6' THEN '600s' WHEN LEFT(it.itemcallnumber,1) = '7' THEN '700s' WHEN LEFT(it.itemcallnumber,1) = '8' THEN '800s' WHEN LEFT(it.itemcallnumber,1) = '9' THEN '900s' ELSE 'OTHER' END from (SELECT * FROM issues UNION ALL SELECT * FROM old_issues) i JOIN items it ON it.itemnumber = i.itemnumber WHERE DATE(i.timestamp) BETWEEN '2020-02-01' AND '2020-02-29' GROUP BY 2 ORDER BY 2; On Thu, Mar 5, 2020 at 11:06 AM muiru james <muirunyeri(a)gmail.com> wrote: Dear All, My library uses DDC scheme of classification and we are looking for a report that will count items checked out allowing us to select day(s) of issue, select itemtype and group the statistics by DDC 10 broad classes. *EXAMPLE* IN a date range say 1st-31st March 2020. *DDC Class | NO. of books issued* 000 - 12 100 - 26 200 - 157 . . . 900 - 230 The report can generate statistics for a day, month, quarter, e.t.c. Any assistance however close will be greatly appreciated. Thank you as you prepare to help James _______________________________________________ Koha mailing list http://koha-community.org Koha(a)lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

2 1

How to add a new display field on the left hand.
by erlanda_simonyan＠flib.sci.am 17 Mar '20

17 Mar '20

Dear List, in OPAC after performing a search, on the left hand column of the main page, under the "Refine your search" we can see the sorted lists by "Authors", "Item Types", "Series", "Holding Libraries", "Places", "Topics". I want to add also the "Name of publisher, distributor, etc." as another display option (field 260 subfield b). E.g., to be able to display the Publishers names. Any advice, how to proceed with this? Thanks

2 1