Librarians and Open Source: We Need Code, Too!

In-House Options Are Sometimes Not Helpful, Too

The library stopped subscribing to collectionHQ in 2015, when it was quite clear that we had exhausted all the use we were going to get out of it, returning to our own ingenuity and systems for collection management. So what do we have as in-house solutions? I know that our library system keeps track of at least three counts relating to checkout associated with each item:

• The number of checkouts (circulation) the item had this calendar year
• Its circulation from the last calendar year
• Its circulation since it was added to the collection.

The system also knows when an item was added to the collection. Given this set of knowledge, it should be easy enough for someone with sufficient knowledge of the field names and SQL syntax to construct a query that asked for all records that met a certain combination of those aspects, as well as limiters like which branch the item belongs to, and whether or not the item itself is sitting on the shelf right now, as it is fiendishly difficult to evaluate an item that is currently out being used.

We have some in-house reports, created by our IT department, that can handle some of these queries and produce reports that can be exported to one of many useful file formats.

• One tells us which items haven't circulated since a certain date, based on collection and branch.
• One tells us all the items that are in a collection at a particular branch and have circulated above the threshold asked for in their lifetime.
• We can look at a particular book's bibliographic record and see a "preview" of how each book in the system did in terms of the three circulation counts. Which would be great, except that records collected in the staff client aren't necessarily exportable to any useful file format with the permissions that a standard librarian gets, or the power search and SQL search options built into the client would be a whole lot more useful.

There is no report that produces any of these elements all together, or that allows someone to select based on branch, acquisition date, and circulation criteria.

This is where I confess ignorance to the intricacies of SQL, so I genuinely do not know whether or not constructing such a query would be awful and horrible and a pain in the tuckus or not. Most of the languages and frameworks I have worked with, like Ruby on Rails, abstract out query formation in favor of using their language's syntax to build requests from the database. Even if I knew how to build the SQL, I don't know the table names where the records are stored and the names of the elements that I'm asking to match against.