Hi, With Koha 16.11.11 / Package Installation/ Ubuntu 14.04 Desktop with MySQL - mysql Ver 14.14 Distrib 5.5.57 We use, students' register number as card number for circulation. And regarding bulk 'card number' modifications : I have a situation, where, first semester borrowers will only get 'permanent register number' during the time of examinations. Till that, I should manage borrowers' circulation with 'temporary card number' (assigned by me). After receiving 'permanent register number from the college office, I have to replace 'temporary number' by 'permanent card number'. Going editing/modifying each patron one by one is very tedious and time consuming, therefore, Is there any way I could use sql and some kind of script to replace card number? (at the terminal) Your suggesstions or best practices would be very helpful. Thank You. Satish MV Librarian Govt. Engineering College, Hassan www.gechassan.ac.in --
Hello Satish, I'm not sure I fully understand your situation but I'll try to provide help based on what I could figure out: *We use, students' register number as card number for circulation. Andregarding bulk 'card number' modifications :* Does this mean you use the patrons' borrowernumber as cardnumber? So borrowernumber = cardnumber? *After receiving 'permanent register number fromthe college office, I have to replace 'temporary number' by 'permanent cardnumber'.* How exactly is the temporary card number going to be assigned? What's the pattern? Are you using the already present cardnumber and concatenating something like "temp" to it? If what I stated above is true, then this should help: UPDATE borrowers SET cardnumber = CONCAT("TMP", borrowernumber) WHERE categorycode = 'STU'; UPDATE borrowers SET cardnumber = borrowernumber WHERE cardnumber LIKE "TMP*"; If what I stated above is false and/or makes no sense, then ignore everything I said :) GL, Pedro Amorim On 18 January 2018 at 04:57, SATISH <lis4satish@gmail.com> wrote:
Hi,
With Koha 16.11.11 / Package Installation/ Ubuntu 14.04 Desktop with MySQL - mysql Ver 14.14 Distrib 5.5.57
We use, students' register number as card number for circulation. And regarding bulk 'card number' modifications :
I have a situation, where, first semester borrowers will only get 'permanent register number' during the time of examinations. Till that, I should manage borrowers' circulation with 'temporary card number' (assigned by me). After receiving 'permanent register number from the college office, I have to replace 'temporary number' by 'permanent card number'.
Going editing/modifying each patron one by one is very tedious and time consuming, therefore, Is there any way I could use sql and some kind of script to replace card number? (at the terminal)
Your suggesstions or best practices would be very helpful.
Thank You.
Satish MV Librarian Govt. Engineering College, Hassan www.gechassan.ac.in
-- _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
Hi Pedro, Thank you for the reply. I try to make my question little clear now, I am not referring to 'borrowernumber' (assigned by Koha) instead referring to 'cardumber' Temporary cardnumber does not prefix with TMP ( now, I understand it's importance) Current patron "cardnumber " designed by the library, is in following format: 17ME01, 17ME02, so on (upto 250+) 17= 2017, ME =Mechanical Engineering, 01,02 = Serial Number of the student as per the List provided by the College Office. Because, every year, to the 'first semester' admission, it takes few months to get the register number assigned by the University. Till that, I need to manage with some 'cardnumber' to take care of circulation. I use, University Registration Number as 'cardnumber' in Koha as it is easily trace-able across the various sections/departments when no-dues verification needed by each department. If I use my own format, it may conflict. New number assigned by the University is: 4GH17ME18, 4GH17ME19, so on....and it is referred as permanent for the patron for future use and Library should use this number. So, my current requirement is: "cardnumber" = 17ME01, 17ME02, ....to be changed as = 4GH17ME18, 4GH17ME19 in bulk. And I request, any scripts or sql to modify in bulk, would be useful. Thank you Satish MV Librarian Govt. Engineering College, Hassan. Karnataka.
Hi the easiest way is to create a temporary table in Koha where you store the old and the new cardnumber and with a sql statement update the borrowers.cardnumber with the new value Kindest Regards 2018-01-18 12:57 GMT+01:00 SATISH <lis4satish@gmail.com>:
Hi Pedro,
Thank you for the reply.
I try to make my question little clear now,
I am not referring to 'borrowernumber' (assigned by Koha) instead referring to 'cardumber'
Temporary cardnumber does not prefix with TMP ( now, I understand it's importance)
Current patron "cardnumber " designed by the library, is in following format: 17ME01, 17ME02, so on (upto 250+) 17= 2017, ME =Mechanical Engineering, 01,02 = Serial Number of the student as per the List provided by the College Office.
Because, every year, to the 'first semester' admission, it takes few months to get the register number assigned by the University. Till that, I need to manage with some 'cardnumber' to take care of circulation.
I use, University Registration Number as 'cardnumber' in Koha as it is easily trace-able across the various sections/departments when no-dues verification needed by each department. If I use my own format, it may conflict.
New number assigned by the University is: 4GH17ME18, 4GH17ME19, so on....and it is referred as permanent for the patron for future use and Library should use this number.
So, my current requirement is:
"cardnumber" = 17ME01, 17ME02, ....to be changed as = 4GH17ME18, 4GH17ME19 in bulk.
And I request, any scripts or sql to modify in bulk, would be useful.
Thank you
Satish MV Librarian Govt. Engineering College, Hassan. Karnataka. _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
-- *Hugo Agud - Orex Digital * *www.orex.es <http://www.orex.es>* <http://www.orex.es/> [image: www.orex.es/koha] <http://www.orex.es/koha> [image: www.orex.es/vufind] <http://www.orex.es/vufind> Directo Calle Sant Joaquin,117, 2º-3ª · 08922 Santa Coloma de Gramanet - Tel: 933 856 138 hagud@orex.es · http://www.orex.es/ No imprima este mensaje a no ser que sea necesario. Una tonelada de papel implica la tala de 15 árboles y el consumo de 250.000 litros de agua. Aviso de confidencialidad Este mensaje contiene información que puede ser CONFIDENCIAL y/o de USO RESTRINGIDO. Si usted no es el receptor deseado del mensaje (ni está autorizado a recibirlo por el remitente), no está autorizado a copiar, reenviar o divulgar el mensaje o su contenido. Si ha recibido este mensaje por error, por favor, notifíquenoslo inmediatamente y bórrelo de su sistema.
Hello Satish To me it seems the easiest way to proceed might be to use the "Import patrons" feature (in the 'Tools' section). This can also be used to *update* patron information -- and this can be single fields, including cardnumber. The only caveat here is that the records to update are either selected by cardnumber or by username (field "userid"). Because you want to change cardnumbers you will have to select by usernames -- and in case you haven't set these you will need to do so in a first step where you select by cardnumber. Hope that is of help -- Best regards Jens _________________________________________ Jens Weber Pädagogische Hochschule Schaffhausen Didaktisches Zentrum Ebnatstrasse 80 8200 Schaffhausen www.phsh.ch
-----Ursprüngliche Nachricht----- Von: SATISH [mailto:lis4satish@gmail.com] Gesendet: Donnerstag, 18. Januar 2018 06:58 An: Koha Betreff: [Koha] SQL help - Bulk Card Number modification
Hi,
With Koha 16.11.11 / Package Installation/ Ubuntu 14.04 Desktop with MySQL - mysql Ver 14.14 Distrib 5.5.57
We use, students' register number as card number for circulation. And regarding bulk 'card number' modifications :
I have a situation, where, first semester borrowers will only get 'permanent register number' during the time of examinations. Till that, I should manage borrowers' circulation with 'temporary card number' (assigned by me). After receiving 'permanent register number from the college office, I have to replace 'temporary number' by 'permanent card number'.
Going editing/modifying each patron one by one is very tedious and time consuming, therefore, Is there any way I could use sql and some kind of script to replace card number? (at the terminal)
Your suggesstions or best practices would be very helpful.
Thank You.
Satish MV Librarian Govt. Engineering College, Hassan www.gechassan.ac.in
--
participants (4)
-
Hugo Agud -
Jens Weber (PHSH) -
Pedro Amorim -
SATISH