Koha Item Status Reports
In another article, there was a discussion of a variety of item statuses and how their behavior can be customized in Koha. That article covered everything that is a “status” in Koha: Withdrawn, Lost, Damaged, Restricted, and Not For Loan. However, when working with newly-migrated libraries it’s pretty common to find that their mental list of statuses contains a few more entries. In this post we'll discuss what it means for an item to be checked out, overdue, in transit, on hold, or available. None of these conditions are customizable, so there won’t be configuration options to discuss, but it’s still worthwhile to know exactly what these conditions mean and what they look like in a report.
Checked out
Generally, if Koha wants to know whether or not something is checked out, it looks for the item in the issues table.
Issues lists every item that’s currently checked out, tracks its due date, counts its renewals, and so on. When an item is returned, Koha puts a date into the returndate and moves that line from issues to oldissues_issues. So, if you want a report to only show you items that are currently checked out you could include “WHERE itemnumber IN (SELECT itemnumber FROM issues).”
However, in this case we do have a quicker and easier option. Whether or not an item is checked out also gets noted in the items table.
When an item is checked out, the date on which it is due can be found in items.onloan. When an item is not checked out, onloan will be null. So if you want to limit a report to items currently checked out you could include “WHERE onloan IS NOT NULL.”
Overdue
Checking whether or not something is overdue uses the same data we’ve just looked at to check whether or not something is checked out, but adds a little complexity. There’s nothing in the issues or items tables that specifically marks a checkout as overdue. Instead, whenever Koha checks whether or not something is overdue it compares the due date to the current date. We have a couple ways we can do that.
As mentioned above, the onloan field in the items table contains the due date for a checked out item. If we want a report to list items where the date in onloan is before the current date, we can include “WHERE onloan < curdate().” The curdate() function returns the current date at the time the report is run.
Since items.onloan and curdate() are both just dates, they won’t help us with hourly loans that are less than a day overdue. For those, we need to go back to the issues table.
Notice that this report requires us to actually join in the issues table rather than just putting it in a subquery. The where statement now reads “WHERE date_due < now().” The date_due field in the issues table is where Koha stores the full date and time at which an item is due. The now() function returns the current date and time (just like the curdate() function, but more specific). This second report is a bit more complicated, but will work for hourly and daily loans.
In transit
Whether or not an item is in transit from one branch to another is not recorded in the items table. Instead, we need to look at branchtransfers.
When an item starts a transfer, it is added to the branchtransfers table with a datesent but no datearrived. When the item gets checked in at its destination branch, the datearrived gets filled out. So we can see than an item is in transit if it’s in the branchtransfers table without a datearrived.
On Hold
To find out whether or not an item is currently on hold, we need to look at the reserves table. The entire lifecycle of a hold is large enough topic to take up its own blog post. In this post, I’m really only looking at how to tell whether or not an item is currently on the hold shelf for a patron. Koha calls that a waiting hold. The reserves table contains all holds that are waiting, pending, or in transit. The old_reserves table contains holds that have been checked out, have been cancelled, or have expired.
To tell a waiting hold apart from a pending hold or a hold in transit, we look at the found field. When found is “W,” the hold is waiting. A pending hold will have a null found and a hold in transit will have a “T” (and will appear in the branchtransfers table as discussed above).
Available
I’ve left Available for last because it is definitely the most complicated. When an item shows in the catalog as “Available,” that means it’s in its normal place on the shelf, ready for a patron to find it and check it out. The only way to limit a report to only available items is to exclude everything else. Basically, an available item is one that’s not withdrawn, not lost, not restricted, not not for loan, not of an item type that’s been marked not for loan, not checked out, not in transit, and not waiting on hold.
That’s a pretty busy report and I’d expect it to run slowly if looking at a very large collection. Luckily, it’s fairly rare that one needs to use a report to find available items. Generally, one uses reports to look for problem items and uses the catalog to look for available items.
This has been a quick wade into a fairly deep pool, and I was aware of myself skirting around topics that could have been covered at much greater depth. Hopefully this serves as a good starting point as you dig into your database and start playing with your data.
Related Articles
Item Search
If you’re responsible for Collection Development but SQL and creating custom reports feels intimidating and out of reach, Item Search is the powerful little tool you need. Item Search builds an SQL report in the background with no need to know SQL. ...Creating and Running Reports
Koha uses SQL (Structured Query Language) for reports. Basically, you are asking, via SQL, to select certain information from specific fields, where the information matches specified criteria. Of course, libraries who are comfortable with SQL can ...Using Mana to Import Reports into Koha
The Koha community shares reports through Mana-kb. The setup is the same as for Serials and adds a Mana link in the Reports Module. Using Mana in the Reports Module When creating a SQL report, there is a an option in the dropdown - “New SQL from ...Koha Glossary of Terms
Find the basics of Koha's vocabulary here This glossary is in alphabetical order. Use control+F to quickly search this page for specific terms. Authority Record: allows your library to control and search fields in your MARC records such as subject ...Item Types in Koha
One of the key components of your library's collection will be the Item Type designation. Item types are a way for your patrons to identify what the item is and more importantly to Koha how this item circulates in your library. Here is a link to the ...