Reports and the Evergreen Database
- Commonly Used Tables
- ILS User Table
- Item Table
- Bibliographic Record Table
- Circulation Table
- In-house Use Table
- Non-catalogd Circulation Table
- Non-catalogd In-house Use Table
- Hold Request Table
- Copy Transit Table
- Hold Transit Table
- Billing Line Item Table
- Billable Transaction with Billing Location Table
- Payments: All Table
- Payments: Brick-and-mortar Table
- Payments: Desk Table
- Payments: Non-drawer Staff Table
When you use Evergreen, you are creating and updating records within the Evergreen database. Evergreen’s reporter enables you to create reports to extract those records from the database. Understanding some basics about the Evergreen database and how records are created and/or updated when tasks are performed on the staff client and public catalog will help you when working with the reporter.
Various kinds of data are used by Evergreen to enable you to perform routine actions in Evergreen such as checking out an item, registering a patron, or placing a hold. Data such as patron names, address, patron barcodes, item barcodes, shelving locations, statuses, item prices, check out dates, due dates, check in dates, and more are saved in the Evergreen database. This data must be organized in an efficient and effective way to make sure it can be stored and retrieved easily.
Evergreen uses various tables to keep each type of records. You can visualize a table as a spreadsheet: a specified number of columns with an unlimited number of rows. Each column is called a field in the database terminology, and each row is a record.
There are many tables in Evergreen’s database. Each table contains certain type of records, and the information you see displayed on any interface in the staff client is likely coming from more than one table. For example, in a patron account, you can find the patron’s names, address, phone number, barcode, main (profile) permission group, etc., all displaying as one record on the Edit tab of the patron’s account. However, in the database, the patron’s address, barcode, and main (profile) permission group are saved in separate tables. You don’t need to know which tables the information is being pulled from when editing a patron record on the staff client, but you do need to know when creating a report template involving patron information.
Since various information about one patron is saved in separate tables, there has to be a way to match the information about one patron correctly to make sure all the information is about the same patron. Evergreen does this by assigning each patron with a unique database ID and recording the database ID in every related table. So by recording the ID of a record in another table, two tables are connected. Some database IDs, such as patron and bibliographic, are visible in the staff client, others can only be seen within the database.
The connections among many tables are premade by the reporter. You just need to follow the link to find the data saved in the related table. Below is a simplified diagram showing the connections among some commonly used tables which can be a guide for you to find various fields in different tables.
Simplified Diagram of the Tables and Connections Among Them
|
Some tables do not link directly with each other. For example, there is not a direct link between the ILS User (patron) table and the item table. Instead, these tables link to each other via other tables such as the circulation table and the hold request table. |
The connections between tables are called joins. Evergreen uses a default join when creating report templates which is usually what you need for a template. When working with data that can be null (have no value), you may want to use nullability, which allows you to create an inner join on your selected table.
When you create a template, you are writing a SQL (structured query language) query that will run on the Evergreen database.
This can be viewed in the report output by selecting Debugging Info.
Commonly Used Tables
Database tables are associated with Sources in the Reporter.
|
Some tables and fields in the database still use the term copy while the preferred term item is used throughout the staff client. |
ILS User Table
This table contains patron/user records. Important fields in this table include:
-
Patron names (primary and preferred)
-
Phone numbers
-
Email address
-
Home Library
-
Privilege Expiration Date
-
Record Creation Date/Time
To find more information about patrons, follow the links to the Current Library Card table for patron’s current barcode, the Circulation table to f ind the circulation history, and the Mailing Address, Physical Address, and Main Profile Group, etc.
Item Table
This table contains item records. Important fields in this table include:
-
Barcode (item)
-
Creation Date/Time
-
Active Date/Time
-
Copy Status Changed Time
-
Last Edit Date/Time
-
Price
Pre-catalogd item information including Dummy ISBN, Precat Dummy Title, and Precat Dummy Author is also in this table. When a pre-catalogd item is checked out, an item record is created. If the barcode is already in the table and the item is not marked deleted, the item record will be updated with the new title, author, etc.
To find more information about items, follow the links to the Circulation Modifier table, the Copy Status table, and the shelving location table.
For circulation information follow the link to the Circulations table.
For basic bibliographic information, follow the link to the Call Number/Volume table. From here, go to the Bib Record table and then select Simple Records Extracts. Title, Author, ISBN, ISSN, Publisher, Publication Year, and Record ID can be found in this table.
Bibliographic Record Table
This table contains title information. In most cases you will want the basic bibliographic information, which is found within the Simple Record Extract table. When linking from other tables you usually have to link through the Call Number/Volume table to find the Bib Record table.
Circulation Table
This table contains circulation records, including pre-catalogd item circulations.
|
When an item is checked out, a circulation record is created. When an item is renewed, the existing circulation record is closed and another circulation record is created. |
Important date/time fields in this table include:
-
Checkout Date/Time - the time when an item is checked out
-
Checkin Date/Time - the effective date when the item is treated as checked in
-
Checkin Scan Date/Time - the time when the check-in action is taken
-
Due Date/Time - For all daily loans the due time is 23:59:59 of the day in the local time zone. Hourly loans have a specific time with time zone information.
-
Fine Stops Date/Time - the date when the Maximum Fine limit has been reached, or the item is returned, marked lost, or claimed returned. After this date, the fine generator will not create new overdue fines for this circulation.
-
Record Creation Date/Time - the date and time when the circulation record is created. For online checkouts, it is the same as Checkout Date/Time. For offline checkouts, this date is the offline transaction processing date.
-
Transaction Finish Date/Time - the date when the bills linked to this checkout have been resolved. For a regular checkout without bills, this field is filled with the check in time when the item is returned.
For patron information, follow the link to the Patron table.
For item information, follow the link to the Circulating Item table.
In-house Use Table
This table contains in-house use records for cataloged items. These in-house circulations are done via the In-House Use interface.
For item information, follow the link to the Item table.
Non-catalogd Circulation Table
This table contains circulations for non-cataloged items.
For Non-catalogd item type information, follow the link to the Non-cat Item Type table.
Non-catalogd In-house Use Table
This table contains in-house use records for non-cataloged items.
These in-house circulations are done via the In-House Use interface.
For Non-catalogd item type information, follow the link to the Item Type table.
Hold Request Table
This table contains hold records for holds that are waiting to be filled or waiting to be picked up.
For patron information, follow the link to the Hold User table.
For information on who placed the hold, follow the link to the Requesting User table. The requesting user will either be the patron or a staff member if they placed the hold for the patron.
For information on the items that can be used to fill a hold, follow the link to the Hold Copy Map. Outside of the reporter, this is referred to as the Eligible Copies table.
|
Target Object ID is shown as a link, but there is no linked table in the Source pane. Depending on the type of hold the value in this field could be a bibliographic record ID, a call number record ID or an item record ID. This is one of the rare cases where you would select a link field to use for your display field. |
Important date/time fields in this table include:
-
Activation Date - the date on which a suspended hold will be activated
-
Capture Date/Time - the time when the hold is captured for the hold shelf or sent into transit
-
Fulfillment Date/Time - the time when the on hold item is checked out
-
Hopeless Date - the date a hold became “hopeless,” or unfillable, due to all available copies becoming unavailable
-
Hold Cancel Date/Time - the time when the hold is canceled
-
Hold Expire Date/Time - this could be the date calculated based on your library’s default Hold Expire Interval or a selected date when placing the hold
-
Last Targeting Date/Time - the last time the hold targeting program checked for an item to fill the hold. Usually the time will match the Hold Request Time, as the hold targeter will generally check every 24 hours. It is usually not useful for reporting, but it can serve as an indicator of whether or not the request time has been edited.
-
Request Date/Time - Usually this is when the hold is placed but it is editable on the staff client. So sometimes this may be the request time chosen by the staff.
-
Shelf Expire Time - the date based on the Shelf Time and your library’s Default Hold Shelf Expire Interval
-
Shelf Time - the time the item’s status is updated to On Hold Shelf
For notification information, follow the link to the Notifications table. This table includes Notification Date/Time which indicates when the automatic notification was sent.
Copy Transit Table
This table contains records of item transits. Records are created in this table both when an item is sent back to its circulating library as well as when it is sent to another library to fill a hold.
For item information, follow the link to the Transited Copy table.
Hold Transit Table
This table contains records of item transits specifically when the item is transiting to fill a hold.
For item information, follow the link to the Transited Copy table.
For hold information, follow the link to the Hold requiring transit table.
Billing Line Item Table
This table contains all the billing line items such as each day’s overdue fines and the manually created bills.
The records in this table are viewable on the Full Details screen on Bills tab in a patron’s account in the staff client.
Billable Transaction with Billing Location Table
This table contains the summary records of billings and payments.
Most of the information in these records is displayed on the Bills tab or Bills History screen. The records are updated when either the related billings or payments are updated.
Important fields in this table include:
-
Transaction ID - the bill ID and also the circulation record ID for circulation bills
-
Transaction Start Time - the check out time for circulation-related bills and the creation time for manually created bills
-
Transaction Finish Time - the time the bill is resolved by being paid, refunded, or adjusted to zero
Payments: All Table
This table contains ALL payment records.
When a payment is made in the staff client, payment records are created. This could be one record or multiple depending on the number of bills resolved or partially resolved by the payment.
One bill may be resolved by multiple payments. One payment may resolve multiple bills.
Payments: Brick-and-mortar Table
This table contains all payments made at the circulation desk through staff.