When is a status not a status?

by Andrew Fuerste-Henry on Jul 8, 2019

In a recent blog post, I discussed a variety of item statuses and how their behavior can be customized in Koha. That post covered everything that I think of as 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 I’m going to 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.

Note that transfers don’t ever move to a different table (unlike checkouts, which move from issues to old_issues). That means any given itemnumber can appear in the branchtransfers table multiple times -- once for a current incomplete transfer and once for each past completed transfer. Cancelled transfers are deleted from the branchtransfers table.

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.