Table of Contents
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 catalogue 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 a 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 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 a certain type of records and the information you see displayed on any interface is 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 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 pre-made 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 Connnections Among Them
Tip
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 the 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 need to use Nullability which allows you to control the type of join used to link two tables.
To find out what the default join is for a table you can view the fm_IDL.xml file for Sitka’s Evergreen.
- Search for the name of the source.
- Find the column you’re interested in and take note of the vlaue for Name.
- Search the page for the Name and look for it to appear in a <links> section.
The reltype indicates the join.
"has_a" = inner join
"has_many" or "might_have" = left outer join
When you create a template you are writing a SQL (standard query language) query that will run on the Evergreen database.
This can be viewed in the report output by selecting Debugging Info.
- ILS User Table
- Item Table
- Bibliographic Record Table
- Circulation Table
- In-house Use Table
- Non-catalogued Circulation Table
- Non-catalogued 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
Note
Some tables and fields in the database still use the term copy while the preferred term item is used throughout the staff client.
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 find the circulation history, and the Mailing Address, Physical Address, and Main Profile Group, etc.
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-catalogued item information including Dummy ISBN, Precat Dummy Title, and Precat Dummy Author is also in this table. When a pre-catalogued 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 click on Simple Records Extracts. Title, Author, ISBN, ISSN, Publisher, Publication Year, and Record ID can be found in this 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.
This table contains circulation records, including pre-catalogued item circulations.
Note
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 Pacific Time. Hourly loans have 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 checkout it is the same as Checkout Date/Time. For offline checkout, 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 checkin 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.
This table contains in-house use records for catalogued items. These in-house circulations are done via the Record In-House Use interface.
For item information follow the link to the Item table.
This table contains circulations for non-catalogued items.
For Non-catalogued item type information follow the link to the Non-cat Item Type table.
This table contains in-house use records for non-catalogued items.
These in-house circulations are done via the Record In-House Use interface.
For Non-catalogued item type information follow the link to the Item Type 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 to 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 refered to as the Eligible Copies table.
Tip
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 -
- Hold Cancel Date/Time - the time when the hold is cancelled
- 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 is calculated based on the Shelf Time and your library’s Default Hold Shelf Expire Interval
- Shelf Time - when 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.
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.
This table contains records of item transits specifically when the item is transitting 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.
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.
This table contains the summary records of billings and payments.
Most of 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 - this is the bill ID and also the circulation record ID for circulation bills
- Transaction Start Time - this is the check out time for circulation related bills and the creation time for manually created bills
- Transaction Finish Time - this is when the bill is resolved by being paid, refunded, or adjusted to zero
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.
This table contains all payments made at the circulation desk through staff.
This table contains payments made using the payment type Cash, Check, Credit Card, or Debit Card.
