Item Status (Items table vs. Loans table)

[eliana-flo]

A lot of my reports require me to query an item's status and I find it a little strange that that status can be found in the circulation_loans table. I'm wondering when it might be useful to pull an item's status from that table rather than pulling it from the JSON of the items table or from the folio_reporting derived table.

The description for "item status" in the circulation_loans table is: "Last item status used in relation to this loan (currently can be any value, values commonly used are Checked out and Available)." This leads me to the following assumptions:

1. The "item_status" field in the circulation_loans table doesn't necessarily show an item's current status
2. You can't retrieve every item's status from this table, only those items that have a loan associated with them
3. If an item's status in the circulation_loans table is "checked out," it's safe to assume it's still checked out. If it's "available," however, it might be currently checked out (this would show up in a different row for a different loan)

Am I on the right track here? Whenever I've queried an item's status in the past, I've only ever cared about it's current status. Should I just avoid pulling it from the loans table and get it elsewhere?

Thank you for any clarity you can provide!
Eliana

[jenmawe]

Hi Eliana,

The item status should always be there if I remember correctly in terms of how that field works in FOLIO Inventory for item records. In this way, you can pull the data from the column item_status rather than extract it from the json data. If you only need to work with the circulation_loans table, then you can just use this column item_status. That way you don't need to join to a derived table in folio_reporting.

To answer your questions:

The item status in the circulation_loans table should be the current item status. If you are unsure, then you can join to the derived table of item_ext and grab the item status from there and compare those results. I just ran this in snapshot and the data from circulation_loans and item_ext are the same.

If you only want item statuses, I would use the derived table item_ext. I think you're correct that the circulation_loans table is first and foremost to retrieve loan and circulation information and display any statuses related to the items retrieved.

For your last question, the status will display what the system "knows". If it is checked out, then checked out and available available. There could be errors For example, system glitches or other errors like the item was not properly checked out or checked in.

You can pull item status from circulation_loans.

Best,
Jennifer

[adoerrer]

Jennifer covered that very well. One additional thought I might raise is that if your main task is to obtain the current item_status you should lookup the place where it's natural "home" is supposed to be and that's the inventory_item table. All other item_status found in other tables should be inhertied by that in spite of those that describes the item_status in a particular time period. For a convenient way you also can take the current status from item_ext as well.

[amzoss]

I agree with Axel. If you just want the item's current status and aren't interested in circulation, you should definitely not use the circulation_loans table. Using inventory_item or the derived tables related to that table (especially item_ext) would be best. The circulation_loans table captures an item's status related to the loan activity, but I don't think it's kept up to date after the loan is completed.

[eliana-flo]

Thanks for all of the answers, everyone. I will avoid the item status field in the circ table for now and use item_ext instead.