Koha is a Library Management System (LMS) used worldwide by approximately 15,000 libraries. The Koha team at Catalyst are passionate about using Koha and helping libraries (big and small!) get the most out of their Koha LMS.
This is the second blog in the Koha tips and tricks series to help you make the most out of your Koha instance.
To read from the beginning of the tips and tricks series: Koha tips and tricks
1. How do I write a SQL report using parameters?
Koha’s reporting module (accessible from the staff client interface) allows you to retrieve a wide range of detailed information from your Koha instance’s database. The database contains the bibliographic, circulation, acquisition and patron data of your Koha site.
Reports can either be created using a GUI (Graphical User Interface) form which does not require you to write SQL (Structured Query Language) code to query the database.
The second method is a SQL report. The Koha community wiki site has a page of pre-written SQL reports which you can use here.
If you plan to write a custom SQL query then here are some basics of SQL which you’ll need to know:
- Koha reports only retrieve data from the database, they do not modify or delete existing data or insert new records. Therefore you will only be writing SQL SELECT queries.
- To narrow down the number of results in your SQL query you will need to use a WHERE clause in your SELECT statement. This means only records matching a specific condition in the WHERE clause will be returned.
- To specify the condition to filter on in the WHERE clause you need to specify parameters. What does this mean? Well, when you run your report an input field will be displayed for you to enter a value, this value will be used to filter the report results.
In the SQL of the report, the parameters must be written in a specific format which is <<Text to be displayed by the input field|authorised value>>
For example: SELECT * FROM biblio left join items on biblio.biblionumber=items.biblionumber WHERE biblio.frameworkcode=<<Enter the framework you want to retrieve bib records and items for>>
This simple report will return all bibliographic records and their associated items which have a framework value in the biblio database table which match the entered framework.
The parameter text contained inside the << >> characters is what displayed when running the report.
In the below screenshot a parameter of ‘ACQ’ is entered, this will be substituted for <<Enter the framework you want to retrieve bib records and items for>> and any biblios with a ‘frameworkcode’ value matching ‘ACQ’ will be returned along with their associated items.
As of the most recent major Koha release (Koha 18.05) using parameters in SQL reports got easier, now parameters can be re-used in reports.
This means if you want to use the same parameter multiple times in a SQL report instead of having multiple input fields displayed where you have to write in the same value, only one input field for the parameter is displayed.
2. How to merge bibliographic records?
Human error can happen in a library. One such error is the creation of multiple records for the same bibliographic item.
These multiple bibliographic records can easily end up containing different data leaving an issue of having to transport the unique, valuable data from one of the records to the other when trying to remove one of the duplicates. Well, with Koha this annoying task is solved by through the Cataloging modules ability to merge records. This consolidates data differences between the records into a single record.
Here are the steps to merge records in Koha:
1. In the Koha staff client go to ‘Cataloging’.
2. Perform a cataloguing search, by writing a search query term in the search box at the top of this page with the ‘Cataloging search’ option highlighted underneath. As you can see in the below screenshot. An effective search term to use is the title of the bibliographic record then the duplicate records will be returned as search results.
3. Select the check boxes beside the duplicate records in the search results and select the ‘Merge selected’ button.
4. Select which of the selected records you want to be kept and select ‘Next’. This selected record is referred to as the ‘Reference’. The other record(s) which aren’t selected will be deleted from the biblio and biblio_metadata database tables, but their unique data will not be lost, it will simply be added to the reference record.
5. Merging the records to a single reference record is a good chance to clear out unwanted MARC field and subfield values from the two (or more) source records you are merging as you can decide exactly what data is consolidated into the merged record. Simply select (to keep) or unselect (to remove) data from the two records. By default, all data in the reference record is selected. Be sure to select ‘Merge’ button at the bottom of this page.
6. The final screen in the Merge record workflow shows you the outcome of the merging. In the case of the screenshot below the merge action was successful. The reference record (biblionumber 1) was kept and contains the selected data (selected in step 5) from the deleted record (biblionumber 40).
Click here for the next post in Alex’s series: Koha tips and tricks 3
Catalyst Koha
If you have any questions or comments about this blog post, or would like some support with your Koha instance you are welcome to email us at koha@catalyst.net.nz
Follow Catalyst Koha on Twitter!
