Create Lists

Author: smoody
Posted in: Systems

CREATE LISTS Information

What is Create Lists?

  • The Millennium report generator
  • Generates customized reports
  • Can find almost anything in the database
  • Lets you sort the records you find
  • Lets you edit the records you find (depending on your authorization)
  • Creates files you can use in other parts of the system (for example, Rapid Update, Statistics, Serials Claiming)

Where can I find Create Lists?

In the Millennium Circulation, Cataloging, Acquisitions, or Serials modules:

• Choose the Create Lists icon
or
• Choose Go > Create Lists

Your initials and password determine whether you are able to run Create Lists. If buttons in Create Lists are grayed out, use the menu Admin > Set Initials to enter your initials and password.

What are the major steps in creating a list?

1.       Choose a file
2.       Search
3.       Sort
4.       List / Export

Basic guidelines

  • Review files numbered above #99 on both Training and Production are RESERVED for Systems
  • Run one query at a time.
  • Do not take multiple large lists at the same time.
  • Use the Training Server for your lists when possible.

Choosing a file

Each line on the screen is a “review file”.

Things to keep in mind when choosing a review file to work in:

  • The file should be big enough to hold your expected search results, but small enough so that you don’t waste space.  Don’t waste a 50,000-record file on a search that you know will retrieve only 5000 records.
  • You can choose a file you’ve already used if you don’t need the contents any more.  But don’t choose a file in use by anyone else unless you have their permission or the date in the name is past.

 

 

The Create Lists search screen

It’s best to fill this screen in from top to bottom.  If you enter data and then change items 2 or 3, you may lose what you’ve entered and have to start again.

Review file naming guidelines:

  • Begin with an “OK to delete after” date. This date should reflect the amount of time the query is needed (no longer than 2 months).
  • Include a brief description of the query
  • End with your Millennium login

Example: 12/19 main call numbers syjl

Lists lacking names/dates may be cleared without warning at any time!

Search efficiency tips:

  • Use a scoped search if you only need to find items at your campus location (or items in your scope). It is MUCH faster.
  • Use a record number range whenever possible, this will make your search much faster. If you want records within a certain date, you can find out the record number range via AskTico (search for “system status” to get the link to the page that archives the System Status screen)
  • Try to put your most specific criteria first, especially if it’s a fixed field value you’re testing. Put text comparisons (especially “has”) last.
  • If you’re doing a complex query, test it on a file that has a record or two you know should qualify and a record or two you know should not qualify first, to verify your logic.  You can can add records to a file once you’re in it by clicking “add” to create a file with specific test records.
  • Use an index or review file to search against, if possible (and if you can’t, search against a targeted number range).
  • If your search fills your file, see the instructions to copy and append below (make sure to empty the filled file when you’re done with it). If your search results are much smaller than the file you chose, copy the results to a smaller file and clear the original one.

 

Exercises

Exercise 1 – Searching Recent Item Records

Create a list of items created in the past six months for your location that have a status of “p” (In process):

NOTE: this example illustrates two different ways of entering search criteria. Steps 7-10 show more of a point and click method that is easier if you are not familiar with the fields/values, steps 12-18 show a faster way to enter the information if you know what to type.

1.       Find an empty file of 500 records and click it to highlight it.

2.       Find the “Search Records” button (above the list of files) and click it.

3.       In the new window that appears, in the “Review File Name” box, type a date (MM/DD) and name (for instance, –1205 new items an status p).

4.       From the drop-down list next to “Store Record Type”, choose “ITEM”. What to search defaults to the range of item record numbers.

5.       Determine the item number range that corresponds to items created in the past three months by looking on AskTico, at the System Status screen document.  Edit the item record number in the “Start” box, to start your search with the item number that was the last item number in Millennium just before/on the date in question. Do this by typing “i” for Item, then pasting in the number.  Since we want to search items created from that date on, leave the “Stop” box to the right that contains the item number to stop at untouched – it represents the last item number in Millennium right now.  NOTE: you could achieve the same results but your search would take MUCH longer by searching for items created after the date six months ago. Searching by item number range makes your search very quick.

6.       Below the “Stop” record number, click the “Use scoped range” box. This will automatically limit your search to just the locations you can edit. This makes your search many times faster, and should ALWAYS be used as long as you only need to retrieve items or holdings for the locations you can edit.

Point and click method

7.       Find the empty white box under the word “Type” (about one-third of the way down the window).  Double-click this box, then chose “ITEM”, click “ok”.

8.       Double-click the white box under “Field”, then chose “LOCATION”, click “ok”.

9.       Double-click the white box under “Condition”, then chose STARTS WITH, click “ok”. NOTE: If you search using “EQUALS” your search will be faster, but all your sublocations will be excluded.

10.   Single-click the white box under “Value A”, then type the branch level location code (for example, “mf”) and press Enter.

11.   Click ‘Append line” – the new line defaults to “AND”.

Type in values method

12.   In the “TYPE” column, click the box, then type “i” to choose “ITEM” again.

13.   Hit the tab key to move to the next box.

14.   In the white box under “Field”, then type “88” to choose “STATUS”.

15.   Hit the tab key to move to the next box.

16.   In the white box under “Condition”, type “=” to choose EQUALS.

17.   Hit the tab key to move to the next box.

18.   Click the white box under “Value A”, then type “p” for IN PROCESS and press Enter.

19.   Find the “Search” button (at the bottom of the window) and click it.

20.   Click “Yes” to search.  Your search should complete (or it will fill before it completes) in a few minutes at most. See screenshot below for roughly how your search will look.

Exercise 1 – Searching Recent Item Records

 

Exercise 2 – Searching against the previous results to only select items on bibs in English

NOTE: this example shows that you can have a file of one record type, but search against criteria from another record type.

1.       Find an empty file of 500 records or more and click it to highlight it.

2.       Find the “Search Records” button (above the list of files) and click it.

3.       In the new window that appears, in the “Review File Name” box, type a date (MM/DD) and name (for instance, 1205 items from #[give review file number from exercise above] lang=eng ).

4.       From the drop-down list next to “Store Record Type”, choose “ITEM”. What to search defaults to the range of item record numbers.

5.       Change the drop down from “RANGE” to “REVIEW”

6.       Find your review file. Unfortunately only the titles show, not the numbers. Hint: some people use a special character at the front of all their review file names so they can type that character to “jump” to their lists in this window. Since I started my file name above with the dash “-“ if I type “-“ I’ll jump to my files in the list. How ever you get to it, click on the file you created in Exercise 1.

7.       Find the empty white box under the word “Type” (about one-third of the way down the window).  Double-click this box, then double-click “BIB”.

8.       Double-click the white box under “Field”, then double-click “LANGUAGE”.

9.       Double-click the white box under “Condition”, then double-click “=”.

10.   Single-click the white box under “Value A”, then choose the language code to search for – in this example we use English which is coded “eng”.

11.   Your results should probably be fewer than in your original file. See the Exercise 2 screenshot for an example.

 

Exercise 2 – Searching against the previous results to only select items on bibs in English

 

Exercise 3 – Select serial and mono item types In Repair for your location

NOTE: This shows how parentheses must be used carefully if you need a mix of AND and OR conditions.  See notes on mixing and/or under “Boolean logic” below

1.       Find an empty file of 500 records or more and click it to highlight it.

2.       Find the “Search Records” button (above the list of files) and click it.

3.       In the new window that appears, in the “Review File Name” box, type a date (MM/DD) and name (for instance, –1205 at Cons, monos/ser  ).

4.       From the drop-down list next to “Store Record Type”, choose “ITEM”. What to search defaults to the range of bib record numbers.

5.       Click the “Use scoped range” box to limit to locations you can edit only.

6.       Find the empty white box under the word “Type” (about one-third of the way down the window).  Double-click this box, then chose “ITEM”, click “ok”.

7.       Double-click the white box under “Field”, then chose “STATUS”, click “ok”.

8.       Double-click the white box under “Condition”, then chose EQUALS, click “ok”.

9.       Single-click the white box under “Value A”, then type “I” for In Repair.

10.   Click “Append line” – the new line defaults to “AND”.

11.   Change the AND operator on the new line to OR by double clicking or typing in the box.

12.   Set up the next line just like the previous line, except this time make the status = b (Bindery).

13.   Click ‘Append line” – the new line defaults to “AND”.

14.   Set the new query line to search for item type 1.

15.   Click “Append line” – the new line defaults to “AND”.

16.   Change the AND on the new line to OR by double clicking or typing in the box.

17.   Repeat steps 12-16 until you have one line for each itype 1, 2, 10, and 11, each with OR as the operator.

18.   If you run your search now, what will happen? You will get far more records than you expected, and not all will be the status or item type you expected.

19.   To fix this, you need to group your “or” conditions. Select lines 1-2 by highlighting the line numbers .

20.   Click the “Group” button, parenthesis will appear.

21.   Select lines 3-6.

22.   Click the “Group” button, parenthesis will appear.

23.   Search. If it takes longer than you want, you can stop at any point using the “Stop” button at the top.

24.   Extra credit: Use the “Sort” instructions below to sort your list by the tracking note, so you can see a list of records in order by the date you sent them to Cons/Bind.  NOTE: records that have more than one tracking note will be REPEATED in the list after you sort it, once for each tracking note. If there are any records with more than one tracking note, the count in your list will grow and you can lose records if it overfills. Sorting can’t be undone.

Exercise 3 – Select serial and mono item types In Repair for your location

 

Other things to try:

  • Modifying a previous search (my review file #28) or saved search (a great way to redo a search that didn’t quite work as expected). Search for records for your location with status “w”. Or search for unsuppressed items or holdings for your location on suppressed bibs. NOTE: saved search in #28 shows use of “between” to get around the fact that you can’t say “item location doesn’t start with zw”.
    • ITEM Status equal to “w” AND ITEM Location between “a” and “yy”.
    • Create a new search, and give it a name, then say “use existing search”, chose my review file #28, modify it as you like, then search your variation.
  • Searching against an index – this can be very quick. We use this most often to do a search where only results within a given call number range are desired. Chose “index” instead of “range” and enter the begin and end points in the index you want to search.

Viewing

View the records in your list.

1.       Find the “Show Records” button and click it.

2.       Double-click the first entry in the list.

3.       Check a field you searched on to verify your search worked as expected.

4.       Click File > Next Record. (Note that you are now looking at the second record in the list).

5.       Press Ctrl + ] (hold down the Ctrl and right bracket keys simultaneously). (Note that you are now looking at the third record in the list) .

6.       Use the Close icon (or Alt + Q) to close the record.

7.       Use the Close icon to close the list.

Sorting

Sort the records in your list.

WARNING: Sorting can make your list grow if you sort by a field that may be duplicated, i.e., a variable length field. It can cause records to be lost from your list if your list is near the size limit and the duplication sends it over.

1.       Start on the main Create Lists screen, with your review file highlighted.

2.       Find the “Sort Records” button and click it.

3.       Double-click the white box under “Type”, then double-click “ITEM”.

4.       Double-click the white box under “Field”, then double-click “Tracking note”.

5.       Click “Sort/Save”.

6.       Wait for the sort to finish, then click “Show Records”. Are the entries in a different order? Double click into a record near the end of the list and check the “Tracking note” field. Records with no tracking note will fall all together to the beginning or end.

Exporting example

1.       Start on the main Create Lists screen, with your review file highlighted.

2.       Find the “Export” button and click it.

3.       Double-click the white box under “Type”, then double-click “ITEM”.

4.       Double-click the white box under “Field”, then double-click “Record number”.

5.       Double-click the white box under “Type”, then double-click “ITEM”.

6.       Double-click the white box under “Field”, then double-click “Total Checkouts”.

7.       Double-click the white box under “Type”, then double-click “BIBLIOGRAPHIC”.

8.       To export title, the simple way is to double click on “title” but there can be more than one “title” tagged field which can lead to duplication in your export. A better choice is to select “MARC tag” then type simply “245” when prompted and click “OK”.

9.       Click “Browse” next to the white box for the file name and chose a directory and type in a filename in the white box.

10.   Click “OK”

11.   Wait for the export to complete.

12.   When it is complete, you can open Excel, select file type “all” and chose your file. Tell Excel it is delimited and click “Next”. Then tell Excel that comma is a delimiter. Click “Next” then “Finish”.

13.   Diacritics don’t export well into Excel 2002 but apparently work in Google docs and Excel 2010.

Reporting

Email a report to yourself from this review file.

1.       Start on the main Create Lists screen, with your review file highlighted.

2.       Click File > Select Printer > Standard Printer.

3.       In the new window that pops up, click “Send to email address”, then click “OK”.

4.       In the Email address box, type an email address. Click “OK”.

5.       Find the “List Records” button and click it.

6.       In the new window that pops up, chose criteria for which fields to include.

7.       In the box next to “Page heading” type in a title for your report.

8.       Click the box next to “Number of blank lines between records”, and type “2”.

9.       Check the box in front of “Number the records in the list”.

10.   Click “OK” three times.

11.   Check your email for the report you just sent.

Emptying – be a good neighbor!

Empty your review file.

1.       Start on the main Create Lists screen, with your review file highlighted.

2.       Find the “Empty” icon (at the top of the screen) and click it.

3.       Read the message carefully to be sure you are emptying your own file, then click “Yes”.

Saving Searches

You can reuse any search that anyone has saved.

After you click “Search Records”, click “Retrieve Saved Query” at the bottom of the “Boolean Search” window. Click the name of a query and it will appear in the upper part of the window for inspection.  When you have found the query you want, click “Select” and it will be copied into your search window.  You can edit it before searching.

To save a search, choose “Save” or “Save As”. (The difference is the same as in word processing.  “Save” uses the same query name if it was previously saved. “Save As” always prompts for a new name.) You have to save a search before you perform it.  Include your initials in the name so we know who to ask before deleting it.

Using Existing Searches

Using an existing search is a very easy way to modify a search that just failed; you can try again without having to retype the entire search.

Stay in the same review file and click “Search Records”. The system will warn that you are overwriting an existing file. Answer “Yes”. Click “Use Existing Search” and find your search in the list. (Tip: You can sort the list.) If you click on a line, the search will appear in the upper part of the window for you to examine.  When you find your search, click “Use” and it will be copied into your search window, where you can edit it before searching again.

Merging and Appending

It’s easy to merge two review files:

  1. Highlight the first file.
  2. Click “Append”.
  3. Change “Range” to “Review”.
  4. Choose the second review file (the one you want to merge with this one).
  5. Click “Search”.
  6. Answer “Yes” to “Retrieve all records in range?”

To append, perform the same steps, but before you click “Search”, enter some search criteria.

Often when appending what you want is to use the same search criteria but with a new range of records (for example, all records added since you last performed the search). “Use Existing Search” is very helpful for this.

After merging or appending, you should dedupe the new file.

Copying a File

There are several reasons why you might want to copy an existing review file:

  • You performed a search in a review file that was too big; you want to copy your results into a smaller file and empty the original file.
  • You performed a search in a review file that was too small; the search stopped when the file filled up, and now you want to copy your results into a larger file and continue the search where it left off (by appending).
  • You want to work with someone else’s review file.

To copy:

  1. Highlight an empty review file. (It must be at least as large as the file you want to copy.)
  2. Click “Copy”.
  3. In the list of review files that opens next, find and highlight the file you want to copy.
  4. Click “OK”.

Your formerly empty review file now has a copy of the original file, with the same name and today’s date.

If the original file was yours, empty it.

Searching System Created Files

The system produces a number of review files automatically. You can search within these files using the following steps:

  1. Highlight an empty review file. (It must be at least as large as the system-created file.)
  2. Click “Copy”.
  3. In the list of review files that opens next, scroll to the end.
  4. Find the review file you want to copy, highlight it, and click “OK”. Your formerly empty review file now has a copy of the system-created file, with the same name and today’s date.
  5. Highlight another empty review file.
  6. Click “Search Records”.
  7. While filling out the screen, change “Range” to “Review” and choose the review file you just copied into.
  8. Complete your search.

After completing your search, empty the review file you copied into.

FOR YOUR REFERENCE:

Boolean Logic

And

“And” means: “I want both terms. Only one term won’t do.”

Remember that the system searches within records. It’s a contradiction in terms to ask it for item records that have Location A and Location B, since an item record can have only one location code.

Or

“Or” means: “I want one, the other, or both.”

To find all the items in Location A plus all the items in Location B, you need to use Location = A OR Location = B.  The system will find all items with Location A, all items with Location B, and add them together.

Mixing And with Or

Things get complicated when you need to use both And and Or in a search. Consider this situation: You want to find all the items that have several different item types (for instance, all MAP item types or all ATLAS item types) in several different branches. The logic you need to use is:

(Item type = A OR Item Type = B) AND (Location = X OR Location = Y)

The way to create this search is to put all the ORs of one type in adjacent lines, then all the ORs of another type in another set of adjacent lines. Select all the OR lines of the first type and group them with the Group button. Select all the OR lines of the second type and group them. Make sure the first line of the second group begins with AND.

Conditions

Greater than, Less than, Greater than or equal to, Less than or equal to

These are the easiest conditions to work with, since they’re the most intuitive. They work as you would expect with both numbers and dates:

MONEY OWED > 150.00

TOT CHKOUT <= 3

CAT DATE >= 01-01-2010

Be careful when using “Less than” and “Less than or equal to” with dates. Blank dates match these conditions, so you may be better off setting a lower boundary with the “Between” condition. In other words, DUE DATE < today’s date finds not only everything that’s overdue, but everything that’s not checked out (everything that has no due date).

Between, Not within

These conditions allow you to specify a range. They work with both numbers and text:

BIRTH DATE between 01-01-1982 and 12-31-1998

“Between” is inclusive. The search above will find 01-01-1982, 12-31-1998, and all dates between.

Has, Starts with, Ends with, Matches

“Has” means “contains anywhere”. “Has” can be difficult to use. For instance, if you want to find item call number fields classified in the “Q”s, you can say call number has “Q” and you will retrieve call numbers classified in the “Q” range. But you will also retrieve call numbers classified in “HQ” and “PQ ranges, as well as call numbers cuttered with “q”.

If the text you’re looking for is at the beginning of the field, use “Starts With” instead.  If it’s at the end of the field, use “Ends With”.

If the text you’re looking for is a single word, either use a space in front of it OR a space behind it.  In other words, INTERNAL NOTE HAS “<space>w/d” OR “INTERNAL NOTE HAS w/d<space>. (Use the space bar, not the word “space” in angle brackets.) Why does this work?

“Matches” tells create lists that you’re going to be using a regular expression for your query.  From Wikipedia: a regular expression is a specific pattern that provides concise and flexible means to “match” (specify and recognize) strings of text, such as particular characters, words, or patterns of characters. Regular expressions are very powerful but not as user friendly.

Equal to, Not equal to

These conditions are best used with numeric fields.

P TYPE = 2

I TYPE != 0

“Equal to” means exactly equal to. MESSAGE equal to “circulation desk” will find nothing, since the text of a MESSAGE field may contains more than just the word “circulation desk”.  If you want to find text inside a field, use “Has”, “Starts with”, “Ends with”, or “Matches”.

These conditions can perform a special trick: They can find records with and without specific variable fields. For example, if you want to find all the bibliographic records that do not have a 245 field in them, use MARC TAG 245 is equal to <nothing>. (To indicate <nothing>, just leave the Value A and Value B boxes empty.)

If you want to find all the item records that have a MESSAGE field, use MESSAGE is note = <nothing>.

You can’t use this trick for fixed fields, since they’re always present, even if empty.

Indexes

Millennium Create Lists can use the indexes to search, instead of scanning the database.  This is much quicker if your search involves an indexed field.  To use an index, change “Range” to “Index”, then choose the index from the drop-down list.

Call Numbers

Call number is a very efficient index search. If your search involves call numbers in any way, start with a call number index search. This avoids having to worry about whether the call numbers are in the item records or the holdings records.

NOTE: If you want your finished list to be in call number order, but you are not looking for a particular range of call numbers, start with a call number search over the entire range of call numbers.

For instance, if you are producing a list of all the items in a given location, do a call number search from 0 (zero) to Z, and choose LOCATION = <location code>. The resulting list will be in call number order.

Other training resources.

Legacy Data

Last updated date: 07/23/14
Author: Charis Takaro
Update Group: Systems

Created: April 26, 2016
Last revised: March 24, 2017
Review date: None set

Leave a comment

Your email address will not be published. Required fields are marked *