Hi Kerrie, The workflow that we've developed utilizes patron lists. When the list of prospective graduates is prepared by the registrar's office, I put them all into a patron list. Yes, this involves looking up individual students by name, but I only have to do this once. After they are in the list, I use a report to see who has an outstanding account balance and/or books still checked out. I use this information to email the students to let them know what they need to pay and/or return by our deadline for graduation. When it is time to convert their accounts to alumni status, I use the batch patron modification tool. This is super easy to use with patron lists and is linked both from the patron list tool and the batch patron modification tool. This year I also wrote a new report that gives me the email addresses for all the patrons on the list, which made it easier to email them all when their accounts were converted to congratulate them and let them know about their alumni library privileges. I will paste my SQL reports below in case you decide to go this route and use patron lists. Good luck, Myka Graduating Students Account Status SELECT CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber, cardnumber, surname, firstname, email, COALESCE(i.CO, 0) AS checkouts, f.Owes FROM borrowers b JOIN patron_list_patrons USING (borrowernumber) JOIN patron_lists ON (patron_list_patrons.patron_list_id = patron_lists.patron_list_id) LEFT JOIN (SELECT borrowernumber, COUNT(issue_id) AS CO FROM issues GROUP BY borrowernumber) i USING (borrowernumber) LEFT JOIN (SELECT borrowernumber, FORMAT(SUM(accountlines.amountoutstanding), 2) AS Owes FROM accountlines GROUP BY accountlines.borrowernumber) f USING (borrowernumber) WHERE patron_lists.patron_list_id = <<Enter patron list ID>> AND (COALESCE(i.CO, 0) > 0 OR f.Owes > 0) Email Addresses from a Patron List SELECT CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber, cardnumber, surname, firstname, email FROM borrowers b JOIN patron_list_patrons USING (borrowernumber) JOIN patron_lists ON (patron_list_patrons.patron_list_id = patron_lists.patron_list_id) WHERE patron_lists.patron_list_id = <<Enter patron list ID>> On Tue, May 26, 2020 at 8:24 PM Kerrie Stevens <kerrie.stevens@ac.edu.au> wrote:
Every year, we need to confirm graduating students have no outstanding loans so they can graduate. And every year I think I must ask for some help in doing it more efficiently than searching borrower numbers one by one... I'd like to be able to copy & paste a number of borrower numbers into search box and get back a table showing those with outstanding loans: (borrower number, name, and number of current loans) Can cutting & pasting or searching on multiple borrower numbers be done in SQL reports? This is way beyond my basic level of SQL understanding, so any advice/assistance would be greatly appreciated.
Thank you and have a great day wherever you may be!
Kerrie Stevens AALIA(CP), MAppSci(LibMgt), BBus(Info&LibMgt) Director of Library Services, Alphacrucis College Librarian - Melbourne Campus HDR Liaison Librarian _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
-- Deaconess Myka Kennedy Stephens, MDiv, MSLIS Seminary Librarian Associate Professor of Theological Bibliography 555 West James Street Lancaster, PA 17603 717-290-8704 mkstephens@lancasterseminary.edu https://library.lancasterseminary.edu