Basic Create Lists

Handout

Innovative University

Release 2007

Copyright © 2009 by Innovative Interfaces, Inc.

As a Millennium customer, you are permitted to make copies of this handout and to modify it as needed for use by your staff. This document and all other Millennium system documentation is proprietary information owned by Innovative Interfaces.

Published January 12, 2009

Basic Create Lists

Innovative University

Contents

Create Lists - What is it?

Create Lists Window

Boolean Operators

Basic BOOLEAN conditions

EQUAL TO

Viewing the records in the file

NOT EQUAL TO

Limiting records retrieved to the login’s SCOPE

GREATER THAN (or GREATER THAN OR EQUAL TO)

LESS THAN (or LESS THAN OR EQUAL TO)

HAS

BETWEEN

NOT WITHIN

STARTS WITH or ENDS WITH

Special conditions

Absence of a variable-length field

Presence of a variable-length field

Store one record type, search on another record’s fields

Use Existing Search

What can you do with your file?

Sortrecords in a review file

Show Info

List (print) data from records in a review file

Export data from records in a review file

Add records one at a time to an existing file

Remove records from the file

Using Lists in other modules/modes

Examples of things you can’t do in Create Lists

Set Up Needed

Documentation & Resources

Objectives of the Class

  • Learn the basics of creating lists
  • Sorting, listing and exporting fields from the records in the file
  • Things you can’t do

Create Lists - What is it?

  • Powerful report generator used to create review files of all record types in your system based on Boolean search criteria.
  • All data elements in all record types are searchable
  • You can sort result files, list fields for printing, output in text/delimited format or edit records in file one by one
  • Review files can be used with other functions such as Featured Lists, Global/Rapid Update, Delete Records mode and Millennium Statistics, Export MARC records
  • Available in all main modules – Administration, Acquisitions, Cataloging, Circulation, ERM and Serials.
  • Success in Create Lists is directly related to your knowledge of the record types in your system and the structure of the fixed and variable-length fields in those records

Caveat

  • Knowledge of record types and structure (fixed and variable-length fields) is essential to success in creating lists
  • For example: If you are trying to find patron records that meet a specific criteria, you must know how data is stored in the patron record in able to create a Boolean condition that will retrieve records with that data

Authorizations

  • To create, view & maintain review files - function 018 (Create lists).
  • You must be authorized to view the record types within the review file
  • You must be authorized to edit the record types within the review file if you want to edit them

Create Lists Window

  • Toolbar icons across the top. (#101067)
  • Buttons above the columns.
  • Review files are in rows.
  • Columns are sortable by any column header – e.g sort by initials. Sort is not permanent.
  • Drop-down menu –can filter view to show only Empty files for example. The Empty files can then be sorted by size by clicking on the Max Records column header.

NOTE: If you copy a review file into an empty file, you can no longer see it with the “Empty” filter operating (since it is no longer empty!).

  • Created [date/time] changes when you sort or manipulate the file. Created Date/time column is empty (as well as the initials column) if the file was created in the Character-Based Innopac and not in Millennium or if a system-generated file was copied.
  • Status bar at bottom will show last record searched while the list is compiling.

Boolean Operators

Boolean operators OR and AND are used with Create Lists. AND is used with an additional search statement to further restrict the results. OR is used to expand the results.

Basic BOOLEAN conditions

EQUAL TO

“Equal to” is useful for finding exact data in fixed fields such as a date or a code where the system understands what is in the field. It is not useful for variable-length fields except when looking for the absence of that field.

SCENARIO: Library wants to find all the “serial” records in its database.

  • Highlight an empty file and click “Search Records”
  • Store record type:Bibliographic

BibBib Level EQUAL TO “s” (Serial)

  • Multiple conditions: This search can be expanded by limiting the retrieval of records to just one location by appending this line:

AND Bib Location EQUAL TO “iu” (Innovative University Library)

Note: The location code in the white box below shows “iu “. The location code is a 5-character code, and if the code is less than 5 characters, the code is completed by adding spaces to the right.

Viewing the records in the file

  • To view the records after the file is finished, highlight the file line and click the Show Records button or double-click on the line.
  • To view the first record in the file, highlight it and click Edit or double-click on the line. If you are authorized to edit this type of record, you are automatically in edit mode and can edit the record as you would in your primary edit mode.
  • To move to the next record without closing the record and selecting the record from the list of records, use <Ctrl<]>. To move to a previous record in the file use <Ctrl<[>. Or choose File | Next Record.

NOT EQUAL TO

“Not Equal to” is most often used with fixed-length fields. It is not useful for variable-length fields except when looking for the presence of that field.

SCENARIO: Library wants to find all items that are currently checked out.

  • Store record type: Item

ItemLoanrule NOT EQUAL TO 0

  • Multiple conditions: You could further limit this if you want to exclude billed items (those with status “n”) by adding another condition:

ANDItemStatus NOT EQUAL TO n

Note: When using multiple NOT EQUAL TO statements, you should use the Boolean operator AND instead of OR.

Limiting records retrieved to the login’s SCOPE

Libraries with a scoped database have the option of restricting the results to just records within the SCOPE assigned to the individual login. If the check box on the screen (just below the ending range number) is checked, only records within that scope will be retrieved. This eliminates the need to add another condition restricting the retrieval to a range of location codes. [This is set in Database Maintenance Options. Instructions are at the end of the handout in the Set up section.]

GREATER THAN (or GREATER THAN OR EQUAL TO)

“Greater than” is most useful with numeric values but can be used with dates or text fields. It could be used with location codes if no closing range is required. It is possible to use it with variable-length fields but is generally not very useful.

SCENARIO 1: Library wants to find all patrons who owe more than $10.00. There is a “Money Owed” fixed-length field in patron records that stores the amount of money billed to a patron (manual charges, fines for returned overdue items, for lost items, or for items that have had a final notice (bill) sent):

  • Store record type: Patron

PatronMoney OwedGREATER THAN10.00

GREATER THAN OR EQUAL TO could also be used if you want to include those who owe exactly $10.00

SCENARIO 2: The library wants to find all checkin cards that have issues that have been claimed at least once, but have still not arrived. Elements of the boxes in the checkin card are searchable under the condition that you choose to store “Holdings (or checkin) AND Card” when choosing the type of record to store.

  • Store record type:Holdings and card

HoldingsClaimGREATER THAN OR EQUAL TO 1

ANDHoldingsArrived copiesEQUAL TO0

LESS THAN (or LESS THAN OR EQUAL TO)

“Less than” is most often used to find dates or numbers in fixed fields. It could be used for location codes if no beginning range is required. It is not useful for variable-length fields.

NOTE: Do not use “Less than” or “Less than or equal to” with Date Fields as it will retrieve blank dates (a blank date is considered “less than” any other date). Use BETWEEN instead

SCENARIO: The library is planning a weeding project and wants to find items that have not circulated very much:

  • Store record type: Item

ItemTotal CheckoutsLESS THAN 5

Multiple conditions: You could further limit this to exclude non-circulating items

ANDItemStatusNOT EQUAL TOo (Lib Use Only)

ANDItemLast Checkout DateBETWEEN01/01/1970 12/31/06

HAS

“Has” is useful for finding data in variable-length fields, but be aware that it can some times find more than you expect.

SCENARIO 1: Library wants all books on Irish history:

  • Store record type:Bibliographic

BibSubject HAS ireland|xhistory

  • You must include the “|x” because that is part of the string even though it isn’t indexed that way and doesn’t display.
  • Multiple conditions: You can add an additional statement with “OR” to expand the search. For example:

ORBibSubject HASireland|xchurch history

BETWEEN

“Between” is often used with date fields or numeric fields. It can be used with location codes to find locations between alphabetic or numeric ranges. It is not generally useful with variable-length fields.

SCENARIO 1: The Library wants to create a file of all order records paid during the previous fiscal year. The segments of the paid variable-length field are searchable separately:

  • Store record type: Order

Order Paid DateBETWEEN 07/01/06 and 06/30/07

  • BETWEEN uses both Value A and Value B in the search criteria line

SCENARIO 2: Library wants to find patron records that were created during a specific year:

  • Store record type: Patron

PatronCreated DateBETWEEN 01/01/07 and 12/31/07

SCENARIO 3: Library wants to find all records within a call number range E 200-E 299.999 (the LC call number range for the US Revolutionary War period). The library stores call numbers in the bibliographic record.

  • Store record type: Bibliographic

BibCall no.BETWEENe^^^200 and e^^^299.999

  • For LC and NLM call number indexing the normalization rule left-justifies the letters (3) and right-justifies the numbers (4) (up to the decimal point). Therefore, you must always enter them in this fashion, using the entire 7 spaces when using the BETWEEN condition. This is not necessary for Dewey or separate Local call number indexes because the rules index character by character.

SCENARIO 4: The library wants to create a review file of all item records for a particular branch. Their location codes are hierarchical in nature, and thus they can use BETWEEN to create this review file:

  • Store record type: Item

ItemLocation (79) BETWEENi and izzzz

NOT WITHIN

“Not Within” is useful to exclude records.

SCENARIO: The library discovers a “weird” volume field in an item record. They want to see if there are any other “non-standard” volume designations:

  • Store record type: Item

ItemVolume (v)NOT EQUAL TO <blank> (means it does exist)

ANDItemVolume (v)NOT WITHIN a z

ANDItemVolume (v)NOT WITHIN09

STARTS WITH or ENDS WITH

“Starts with” can be used to find data at the beginning of a field. This is particularly useful with a non-MARC field or a MARC field that does not have subfields (when you can’t search for the string |axxxxx).

SCENARIO: A library has its OCLC numbers in various formats – number only, number preceded with ocm and number preceded with ocn. They want to remove the “ocm” and “ocn”:

  • Store record type: Bibliographic

Bib! (Marc tag) 001 STARTS WITH oc

Note on using MARC tag: The question mark (?) can be used to wildcard any position of the MARC tag or indicators. E.g. 24? or 6?? (#100673)

“Ends with” is used to find data at the end of a field. This can be used in fixed-length fields, like location code, as well as variable-length fields.

SCENARIO: A multi-branch library has created location codes so that each library has a location for its Reference collection that begins with the letters for each branch, but ends in “ref.” If the library wanted to find all reference items without having to create a long multiple-condition statement, “ends with” can be used.

  • Store record type:Item

ItemLocationENDS WITHref

Special conditions

Absence of a variable-length field

Libraries often want to see if they have any bibliographic records in their database that do not contain a specific variable-length field as part of database maintenance. The strategy for finding records that do not have a certain variable-length field is “field” EQUAL TO “blank” (or nothing). Some common examples are:

Bibliographic Title (t) EQUAL TO<blank>

ItemBarcode (b)EQUAL TO<blank>

PatronEmail(z)EQUAL TO<blank>

HoldingsHoldings (h) EQUAL TO <blank>

SCENARIO 1: Library wants to find bibliographic records without titles:

  • Store record type:Bibliographic

BibTitleEQUAL TO<blank>

SCENARIO 2: The library wants to work on adding Holdings Statements to all its holdings records. They need to find all holdings records that do not contain a holdings (h) statement:

  • Store record type: Holdings

HoldingsHoldings field (h)EQUAL TO <blank>

Presence of a variable-length field

To search for the PRESENCE (or existence) of a variable-length field you would use: “field” NOT EQUAL TO “blank” (nothing). Some special fields have only two conditions – exist or not exist.

SCENARIO 1: Library wants to find all its bib records that have a URL in them. This field is in the “y” field group, which has many MARC fields in this group. So, to narrow the search, they search by the specific MARC tag instead of the “y” field-group tag.

  • Store record type: Bibliographic

Bib! (Marc Tag) 856NOT EQUAL TO<blank>

SCENARIO 2: Item records that have Message fields cause a message to pop up when the item is checked in or out at the checkout desk. The library wants to find all those items to review whether or not the message is still relevant.

  • Store record type: Item

ItemMessage (m)NOT EQUAL TO<blank>

SCENARIO 3: Library wants to find holdings records that ARE routed.

  • Store record type: Holdings

HoldingsRouting (4)EXIST

Store one record type, search on another record’s fields

SCENARIO 1: The Library needs to store patron records because they want to use the file to send a statement to those patrons. But the criterion for retrieving those records isn’t in patron records, but in a “linked” record – in this case the item record.

  • Store record type:Patron (Patrons with billed items checked out)

Item StatusEQUAL TOn (billed)

SCENARIO 2: The library wants to find items that were purchased with a specific fund. The fund information is in the order record, not the item record. The file must store bibliographic records not item records.

NOTE: You can’t store items and search on fields in order records (or holdings records) or vice-versa. These attached records have no “link” to each other – only to the bibliographic record. So, if you need to search or print (list) information from both item and order (or order and holdings) you would need to store bibliographic records in your file to have access to the fields in all the attached records ( .

  • Store record type:Bibliographic (Bib records purchased with a specific fund)

Order FundEQUAL TO iu2f

Use Existing Search

Using the search strategy of any existing review file (via Use Existing Search) is extremely useful.

  • You can “use” (or copy) any strategy from any other review file as long as it was created in Millennium Create Lists (and not character-based).
  • Other examples of when to Use Existing Search are when you find that you have used a file that was too small and need to use a larger file, or you want to re-run a file you have created, but need to make a small change to the search strategy

Note: If you copy an existing search that searched the entire range of record numbers, it also copies that range. To “refresh” the range numbers so that the ending record number is the current number of the last record in your database, change the “Range” drop-down to “Review” and back to “Range.” This will display the current record number range. You can also click in the “Stop” range box and type the letter of the record type you are searching (b for bib records, i for items, etc.) and the ending range will refresh.

SCENARIO 1: You have just created a review file and it filled up the file with the maximum number of records. You know you need to re-run this with a larger file.

  • Highlight a larger empty file, click Search Records. From the bottom of the next screen, click Use Existing Search.
  • From the displayed list of files and strategies, highlight the other file that you just ran, and click Use.