Advice for how to update huge numbers of bib records, for former Millennium user
What would be the Koha equivalent to Millennium Create Lists* (or Alma Normalization Rules and Record Sets)? So far, I see that lists and carts can be used to update records in Koha, but this seems to demand working with a pre-determined list of record numbers, or else by manually adding items to a cart. What if I need to update a huge number of "unknown" records meeting specific criteria? Can anyone suggest what the approach would be to accomplish an objective like this: A non-administrator with limited technical skills needs to gather and update 40,000 bib records in Koha that can't be identified by any single unifying attributes. My Millennium query might have looked like this: * Bib record not attached to order AND * (Location is Y or Location is X or Location is Z) and * Author is not TODD and * Note doesn't have FREE *Millennium Create Lists allows you to create a Boolean Search, with up to about 20 statements, to query the entire database (various methods to search subsets or existing result sets also exist) and saves it to a file that can then be exported, or operated on (to apply global updates to fixed or variable fields, for instance). No knowledge of SQL is needed. Thanks in advance for any tips. As an administrator, I could conceivably do this with SQL, but that's not feasible for cataloging staff. We update large quantities of records all the time. Tasha R. Bales
Hi Tasha, We were also a former Millennium library that migrated to Koha, though it's been a while (migrated in 2016). Reports are essentially Millennium's "Lists" but they require more skill to create. So your example would need to be a SQL Report filtering according to the properties you've laid out (e.g. biblios not linked to the orders table, with item location in (X, Y, Z), biblio.author != 'AUTHOR', etc.). I hosted workshops (here's a recording of one <https://youtu.be/K0zTu4qZ88c>) with our catalogers and other technicians on learning basic SQL and I write the more sophisticated reports for them. You can use runtime parameters to make the reports a little more flexible so that staff can adjust them to changing needs but they're not going to be able to accomplish much on their own without an understanding of SQL. I never use the guided reports in Koha and my memory of the few times I tried was that you couldn't accomplish anything substantial, but then again I prefer working in SQL anyways as it's so much more powerful than abstractions like Guided Reports or Create Lists. Best, ERIC PHETTEPLACE Systems Librarian, Libraries (he/him) ephetteplace@cca.edu | o 510.594.3660 (cca) 5212 Broadway | Oakland, CA | 94618 CCA is situated on the traditional unceded lands of the Ohlone peoples. Black-owned bookstores in Oakland: Ashay by the Bay <https://ashaybythebay.com/>, Marcus Books <https://www.facebook.com/marcus.books/> :(){ :|: & };: On Thu, Jan 21, 2021 at 10:15 AM Bales (US), Tasha R < tasha.r.bales@boeing.com> wrote:
What would be the Koha equivalent to Millennium Create Lists* (or Alma Normalization Rules and Record Sets)?
So far, I see that lists and carts can be used to update records in Koha, but this seems to demand working with a pre-determined list of record numbers, or else by manually adding items to a cart. What if I need to update a huge number of "unknown" records meeting specific criteria?
Can anyone suggest what the approach would be to accomplish an objective like this:
A non-administrator with limited technical skills needs to gather and update 40,000 bib records in Koha that can't be identified by any single unifying attributes. My Millennium query might have looked like this:
* Bib record not attached to order AND
* (Location is Y or Location is X or Location is Z) and
* Author is not TODD and
* Note doesn't have FREE
*Millennium Create Lists allows you to create a Boolean Search, with up to about 20 statements, to query the entire database (various methods to search subsets or existing result sets also exist) and saves it to a file that can then be exported, or operated on (to apply global updates to fixed or variable fields, for instance). No knowledge of SQL is needed.
Thanks in advance for any tips. As an administrator, I could conceivably do this with SQL, but that's not feasible for cataloging staff. We update large quantities of records all the time.
Tasha R. Bales _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
I really appreciate your reply Eric, thank you. I will check out your link. Alas, this is also a bit of a disaster. I see my workload increasing. ☺ If staff will be using SQL, I have reservations about unintentional changes or deletions. Once a Report is created, are you just using SQL to change field data as well, or is there a different tool available that staff may use to actually apply their changes? Thanks for your time, Tasha R. Bales From: Eric Phetteplace [mailto:ephetteplace@cca.edu] Sent: Thursday, January 21, 2021 12:48 PM To: Bales (US), Tasha R <tasha.r.bales@boeing.com> Cc: koha@lists.katipo.co.nz Subject: [EXTERNAL] Re: [Koha] Advice for how to update huge numbers of bib records, for former Millennium user EXT email: be mindful of links/attachments. Hi Tasha, We were also a former Millennium library that migrated to Koha, though it's been a while (migrated in 2016). Reports are essentially Millennium's "Lists" but they require more skill to create. So your example would need to be a SQL Report filtering according to the properties you've laid out (e.g. biblios not linked to the orders table, with item location in (X, Y, Z), biblio.author != 'AUTHOR', etc.). I hosted workshops (here's a recording of one<https://youtu.be/K0zTu4qZ88c>) with our catalogers and other technicians on learning basic SQL and I write the more sophisticated reports for them. You can use runtime parameters to make the reports a little more flexible so that staff can adjust them to changing needs but they're not going to be able to accomplish much on their own without an understanding of SQL. I never use the guided reports in Koha and my memory of the few times I tried was that you couldn't accomplish anything substantial, but then again I prefer working in SQL anyways as it's so much more powerful than abstractions like Guided Reports or Create Lists. Best, ERIC PHETTEPLACE Systems Librarian, Libraries (he/him) ephetteplace@cca.edu<mailto:ephetteplace@cca.edu> | o 510.594.3660 (cca) [https://www.cca.edu/sites/default/files/images/cca-logotype-394.png] 5212 Broadway | Oakland, CA | 94618 CCA is situated on the traditional unceded lands of the Ohlone peoples. Black-owned bookstores in Oakland: Ashay by the Bay<https://ashaybythebay.com/>, Marcus Books<https://www.facebook.com/marcus.books/> :(){ :|: & };: On Thu, Jan 21, 2021 at 10:15 AM Bales (US), Tasha R <tasha.r.bales@boeing.com<mailto:tasha.r.bales@boeing.com>> wrote: What would be the Koha equivalent to Millennium Create Lists* (or Alma Normalization Rules and Record Sets)? So far, I see that lists and carts can be used to update records in Koha, but this seems to demand working with a pre-determined list of record numbers, or else by manually adding items to a cart. What if I need to update a huge number of "unknown" records meeting specific criteria? Can anyone suggest what the approach would be to accomplish an objective like this: A non-administrator with limited technical skills needs to gather and update 40,000 bib records in Koha that can't be identified by any single unifying attributes. My Millennium query might have looked like this: * Bib record not attached to order AND * (Location is Y or Location is X or Location is Z) and * Author is not TODD and * Note doesn't have FREE *Millennium Create Lists allows you to create a Boolean Search, with up to about 20 statements, to query the entire database (various methods to search subsets or existing result sets also exist) and saves it to a file that can then be exported, or operated on (to apply global updates to fixed or variable fields, for instance). No knowledge of SQL is needed. Thanks in advance for any tips. As an administrator, I could conceivably do this with SQL, but that's not feasible for cataloging staff. We update large quantities of records all the time. Tasha R. Bales _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz<mailto:Koha@lists.katipo.co.nz> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
There's an additional step which ferries the data from a report into the batch modification tool (Tools > Batch item/record modification). For bibs or authorities, you need to create a MARC modification template <https://bywatersolutions.com/education/monday-minutes-marc-modification-templates> describing your changes first. It's a relatively new feature but our 19.11 Koha lets you perform batch operations on the visible records displaying on the report results page, but that maxes out at a thousand records. You could also save the visible records into a list and then perform batch operations on the list. That might be more foolproof since you have a stored record of what was modified. If you need to modify more than 1000 records, I think you have to download a list of the record identifiers and then feed that file into the batch modifications tool. If you just make your report's SELECT statement only retrieve the identifier column (itemnumber, biblionumber, etc.) that works, but it also makes the reports less understandable for others. You could download the report CSV and then trim the non-identifier columns. You could also operate on records in smaller batches e.g. a few hundred a time. But something like "modify these 40,000 bibs" is probably always a multistep process. We're small (90,000 items total) so maybe a larger library can chime in on whether there's a better approach at scale. Best, ERIC PHETTEPLACE Systems Librarian, Libraries (he/him) ephetteplace@cca.edu | o 510.594.3660 (cca) 5212 Broadway | Oakland, CA | 94618 CCA is situated on the traditional unceded lands of the Ohlone peoples. Black-owned bookstores in Oakland: Ashay by the Bay <https://ashaybythebay.com/>, Marcus Books <https://www.facebook.com/marcus.books/> :(){ :|: & };: On Thu, Jan 21, 2021 at 2:31 PM Bales (US), Tasha R < tasha.r.bales@boeing.com> wrote:
I really appreciate your reply Eric, thank you. I will check out your link. Alas, this is also a bit of a disaster. I see my workload increasing. J
If staff will be using SQL, I have reservations about unintentional changes or deletions. Once a Report is created, are you just using SQL to change field data as well, or is there a different tool available that staff may use to actually apply their changes?
Thanks for your time,
*Tasha R. Bales*
*From:* Eric Phetteplace [mailto:ephetteplace@cca.edu] *Sent:* Thursday, January 21, 2021 12:48 PM *To:* Bales (US), Tasha R <tasha.r.bales@boeing.com> *Cc:* koha@lists.katipo.co.nz *Subject:* [EXTERNAL] Re: [Koha] Advice for how to update huge numbers of bib records, for former Millennium user
EXT email: be mindful of links/attachments.
Hi Tasha,
We were also a former Millennium library that migrated to Koha, though it's been a while (migrated in 2016). Reports are essentially Millennium's "Lists" but they require more skill to create. So your example would need to be a SQL Report filtering according to the properties you've laid out (e.g. biblios not linked to the orders table, with item location in (X, Y, Z), biblio.author != 'AUTHOR', etc.).
I hosted workshops (here's a recording of one <https://youtu.be/K0zTu4qZ88c>) with our catalogers and other technicians on learning basic SQL and I write the more sophisticated reports for them. You can use runtime parameters to make the reports a little more flexible so that staff can adjust them to changing needs but they're not going to be able to accomplish much on their own without an understanding of SQL. I never use the guided reports in Koha and my memory of the few times I tried was that you couldn't accomplish anything substantial, but then again I prefer working in SQL anyways as it's so much more powerful than abstractions like Guided Reports or Create Lists.
Best,
*ERIC PHETTEPLACE* Systems Librarian, Libraries (he/him)
ephetteplace@cca.edu | o 510.594.3660 (cca)
5212 Broadway | Oakland, CA | 94618
*CCA is situated on the traditional unceded lands of the Ohlone peoples.*
*Black-owned bookstores in Oakland: Ashay by the Bay <https://ashaybythebay.com/>, Marcus Books <https://www.facebook.com/marcus.books/>*
:(){ :|: & };:
On Thu, Jan 21, 2021 at 10:15 AM Bales (US), Tasha R < tasha.r.bales@boeing.com> wrote:
What would be the Koha equivalent to Millennium Create Lists* (or Alma Normalization Rules and Record Sets)?
So far, I see that lists and carts can be used to update records in Koha, but this seems to demand working with a pre-determined list of record numbers, or else by manually adding items to a cart. What if I need to update a huge number of "unknown" records meeting specific criteria?
Can anyone suggest what the approach would be to accomplish an objective like this:
A non-administrator with limited technical skills needs to gather and update 40,000 bib records in Koha that can't be identified by any single unifying attributes. My Millennium query might have looked like this:
* Bib record not attached to order AND
* (Location is Y or Location is X or Location is Z) and
* Author is not TODD and
* Note doesn't have FREE
*Millennium Create Lists allows you to create a Boolean Search, with up to about 20 statements, to query the entire database (various methods to search subsets or existing result sets also exist) and saves it to a file that can then be exported, or operated on (to apply global updates to fixed or variable fields, for instance). No knowledge of SQL is needed.
Thanks in advance for any tips. As an administrator, I could conceivably do this with SQL, but that's not feasible for cataloging staff. We update large quantities of records all the time.
Tasha R. Bales _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Hi Tasha (and Eric) I see Eric has responded and I just wanted to encourage you to persevere as I think the facilities are there in Koha and with the selection using SQL your selection can be very precise. I wanted to draft up some (fixed) SQL that pretty much does what you ask (which was why Eric beat me to it :-) SELECT biblionumber FROM biblio WHERE biblionumber not in (SELECT biblionumber FROM aqorders WHERE orderstatus not in ('cancelled', 'complete')) AND biblionumber in (SELECT biblionumber FROM items WHERE LOCATION in ('BOOK', 'OPENACCESS')) AND author not like '%TODD%' AND biblionumber not in (SELECT biblionumber FROM items WHERE itemnotes_nonpublic like '%FREE%') The above selects the biblionumbers for all orders not closed or complete, that don't have any items at the chosen locations, do not have TODD in the author statement and do not have "FREE" in the staff note for any items. Running this report allows you to choose "Batch operations with 1000 visible records" at a time. The action can either be "Batch Record Modification" or "Add to List". I would suggest from what you say it would be good to create a list for each such global change. That way you can add 1000 at a time to the list so you end up with a list (in your example) of around 40,000 records. This way someone else can cast their eye over the SQL in the report and the titles in the list before committing the change. Once you are happy with the list then you can use the "Batch Record Modification" in Tools to select your MARC Modification Template and use the list that has been created to make the change. Do feel free to ask either on the list or off-list if you want any more help with it. I have seen a few library management systems and facilities for batch bibliographic changes always seem complicated. I think this is mainly because you have to be very careful in what you are doing. The more practice a person gets with a particular way of doing it the easier it will become. Good luck and ask if you need to. Ian On 21/01/2021 18:14, Bales (US), Tasha R wrote:
What would be the Koha equivalent to Millennium Create Lists* (or Alma Normalization Rules and Record Sets)?
So far, I see that lists and carts can be used to update records in Koha, but this seems to demand working with a pre-determined list of record numbers, or else by manually adding items to a cart. What if I need to update a huge number of "unknown" records meeting specific criteria?
Can anyone suggest what the approach would be to accomplish an objective like this:
A non-administrator with limited technical skills needs to gather and update 40,000 bib records in Koha that can't be identified by any single unifying attributes. My Millennium query might have looked like this:
* Bib record not attached to order AND
* (Location is Y or Location is X or Location is Z) and
* Author is not TODD and
* Note doesn't have FREE
*Millennium Create Lists allows you to create a Boolean Search, with up to about 20 statements, to query the entire database (various methods to search subsets or existing result sets also exist) and saves it to a file that can then be exported, or operated on (to apply global updates to fixed or variable fields, for instance). No knowledge of SQL is needed.
Thanks in advance for any tips. As an administrator, I could conceivably do this with SQL, but that's not feasible for cataloging staff. We update large quantities of records all the time.
Tasha R. Bales _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
-- Ian Bays Director, PTFS Europe Limited Content Management and Library Solutions
participants (3)
-
Bales (US), Tasha R -
Eric Phetteplace -
Ian Bays