Create Lists/Review Files – Exporting records 2

In the previous post we looked at selecting data to export. This post will show you how to import the data into Excel.

Save and Export

The bottom half of the Export screen asks a number of questions about how you want to format your data export. Usually you can accept the default options- I will look at these in more detail later.

You must choose where to save your file. Click the Browse button to the right of the File field

browse and save

In the pop-up window, navigate to where you want to save the file and name your file in the File Name box


Click Save and then OK to export.

Import into Excel

To import into Excel, first open Excel. In a new document, go to File> Open and browse to where you saved your file. Remember, Sierra exported the file as a .txt file type so you will need to look for ‘all files’ not ‘All Excel Files’ or you will not be able to see your file.

all files

Text Import Wizard

Excel will present you with a ‘text import window’ box. I recommend making the selections below (though these may change if you did not choose the default Export options in Sierra).

Step 1

Choose file type ‘Delimited’ and click Next

Excel data type

Step 2

Tick delimiter ‘Comma’

In the bottom panel you should see your data divide into neat columns when you tick this.

Click Next

Excel comma delimited

Step 3

Change the data format to ‘Text’. If your data contains any numbers such as barcodes or ISBNs this is really important to prevent Excel from transforming the data and removing leading 0’s (see example below)

Excel barcodes

First, click on the first column in Data Preview. Hold down the SHIFT key. Use your mouse to scroll to the last column in Data Preview and click on this column. Release the SHIFT key. All columns should now be highlighted in black.

Second, select the radio button Text and click Finish

Excel - text

You should now have a nicely formated Excel spreadsheet.


Posted in Create Lists, Sierra | Tagged , , , | Leave a comment

Create lists/Review files – quick tips – change stored record type

Sometimes it’s useful to change the type of record stored in a review file- e.g. from Bibliographic to Item, or Item to Patron. This can be particularly useful if you want to be able to search for data that isn’t available in the record type you want to output. For example you cannot create a review file of item records linked to specific fund codes. Because item records and order records (where fund codes are to be found) are not linked records, it is not possible to ‘store’ item records and search order records. Instead you can ‘store’ bibliographic records (which are linked to order records), then transform your review file of bib records into one containing item records.

This is easy to do, as follows. In this example we will convert a review file containing bib records into one containing item records.

Step 1

Identify an empty review file of appropriate capacity and click Search Records

empty file

Note: If you are changing Bibs to Items, your review file will need to have a higher capacity than the number of bib records stored, as bib records are often linked to multiple item records

Step 2

1. Give your review file a name

2. Select the type of record you want to store

3. Change from Range to Review and select the existing review file you want to change from bib to items

4. Click Search

convert records

Step 3


Sometimes, you might want to Dedupe (deduplicate) your new file. For example, if you are converting a review file from Item to Bib records, you may find that the same bib records are listed in your new review file multiple times, due to the many-to-one relationship between item and bib records. To deduplicate, just select your review file and click the Dedupe button.


Posted in Create Lists, Sierra | Tagged , , , , | Leave a comment

Create Lists/Review Files – Exporting records 1

Exporting data from a review file

The Export Records function in Create Lists allows you to export data from Sierra.

Export button

Click on your review file and then on the Export Records button to start.

Select fields to exported

The first step is to choose what information you want to export from Sierra. You start from a ‘blank slate’ -i.e. no data is exported by default – so you need to list each and every field that contains the information you need.

export no fields

Double click to select a record type and field, using the Append or Insert buttons to add more fields until you have listed everything you need.

To ge the best results you need to know two things:

  1. Which record types are available to you in your export?
  2. Which fields contain the information you need?

Record Types

The first field in the Export screen is Type. When you double-click in this field Sierra asks you to choose a Record Type. Which record type you select will depend on

  • what field contrains the information you require
  • which record types are available to you

Export - record types

The record types available for export changes depending on what record type is stored in your review file. You can only export information from the stored record type and any linked records. If you are not sure what type of records you have stored, you can see this in the Type columntype

The letter represents the Sierra record type;


b= bibliographic




For example, if your review file contains patron records, you can export data from any field that exists in a patron record. You can also export item information if the patron has any items currently checked out. While an item is checked out, the patron and item record are temporarily linked (until the item is checked back in in Sierra).

So if you have a review file of patron records you can export, for example:

  • Patron name
  • Patron barcode
  • Item barcode (for any items currently checked out)
  • Item due date (for any items currently checked out)

Export - patron itemPNG

You can’t export title or author information because title/author information is stored in bibliographic (bib) records and patron records are not linked to bib records.

However it is possible to export patron, item AND bib data at the same time if, instead of creating a review file to store patron records, you create a review file to store item records. Item records are linked to bib records so when exporting data from an item review file you can export:

  • Patron name (if the item is currently checked out)
  • Patron barcode (if the item is currently checked out)
  • Item barcode
  • Item due date
  • Bib title
  • Bib author

Export - bib patron item

I’ve listed the primary record relationships at the bottom of this post.


When you have selected a record type you can select specifc fields to export. If you are unsure which fields hold the information you need, look at some records in Sierra to identify the field names.

Suppose you want to know how many items a group of patrons currently have on loan, plus any fines or charges outstanding and the date on which they last interacted with the library. If you look at any patron record you can easily identify which fields contain the relevant information.

Export - patron fields

If you also want to know which items the patrons have on loan that are overdue, you can identify the fields you need by looking at an item record.

Export - patron item fields

You can now list the fields to export in Create Lists

Export - circulation fields

Record relationships


  • Items (if there are items attached)
  • Orders (if there are orders attached)
  • Checkins (if there are checkins attached)

Bib records can exist without being linked to any other records, if there are no item, order or checkins attached.

ITEM – linked to

  • Bib
  • Patron (temporarily link when the item is checked out to a patron)

Items records are ALWAYS linked to a bib record. An item record cannot exist by itself with no linked bib.


  • Item (temporary link when an item is checked out to the patron)


  • Bib

Order records are ALWAYS linked to a bib record. An order record cannot exist by itself with no linked bib.


  • Bib

Checkin records are ALWAYS linked to a bib record. A checkin record cannot exist by itself with no linked bib.

Note that orders and items are not linked record types. The only way to export order and item information together is by storing bib records in your review file.


Posted in Create Lists, Sierra | Tagged , , , | Leave a comment

Create Lists/Review files – introduction

This is the first in a series of posts about the Create Lists function in Sierra.

function create lists

Create Lists allows you to find records to match specific criteria and  extract information from Sierra in an Excel friendly format. Common uses of Create Lists include:

Find all books purchased for a particular department

Find books that haven’t been borrowed for several years, to identify suitable items to withdraw

Find items that have an in-transit status but are actually on the shelf

Understanding the Create Lists screen

In the main area of Create Lists there are 8 columns:

File> Name> Current Records> Max Records> Type> Status> Login> Created [date/time]

You can sort the list of review files by clicking on any of the column headings. Click a second time to reverse sort (e.g. reverse alphabetic or reverse date).


For example, click on ‘Name’ to sort alphabetically by the name of the review fileSort by name

Sorting the list by Name is useful when you want to find an empty review file to use, as un-named (empty) files will be listed first.

Name sort empty files

Created date

If you have just created a review file, sort by Created[date/time] for a quick and easy way to find it. The most recently created review files will display at the bottom (or top if you use revserve sort) when sorted by Created date/time so just click the column heading and your file will be at the very bottom (or top if reverse sorted) of the list.

Created reverse sort


If you want to find all of the review files you have created, click the ‘Login’ column heading to sort by Sierra login. You can then scroll down to find all of your review files grouped together.

login sort

Sometimes the initials in the Login column are red. The red colour indicates that the file has been ‘owned’ by someone. This is a feature of Create Lists that allows you to lock a review file for your use. You might do this if one of your review files contains important information and could not be easily re-created. Owning the review file will prevent any other user from accidentally over-writing it. Owning a review file is also useful if you need to reserve one of the larger files for an important or urgent job and you want to ensure you have a sufficiently large review file available for your use.

login sort - owned files

Owned review files sort out of order to non-owned review files so sometimes you will need to scroll through the owned and non-owned files to find all the ones you have created.





Posted in Create Lists, Sierra | Tagged , , | Leave a comment

Relative date searching in Create Lists

The last Sierra upgrade introduced some new functionality to Create Lists. You can now select ‘relative dates’ when you are searching on any date field.

What does this mean? With ‘relative date’ searching, you don’t have to enter a specific date (e.g. 14/03/2016), you can choose to search for a date according to its position relative to today.

For example if I want to run a daily review file to find items edited that day, without relative date searching I would need to manually change the date to todays date every time I run the file. With ‘relative’ date searching I can create a saved search that looks for items edited ‘today’ and the actual date will automatically change to todays date as the review file runs.

The strategy below will find all bib records created or updated ‘today’ or ‘yesterday’ – no need to enter actual dates.

saved search today

The new options available for relative date searching are as follows:

relative date selection

T (equals today)- Retrieves records whose specified DATE field is equal to the day on which the search is run.

Y (equals yesterday)- Retrieves records whose specified DATE field is equal to the day before the search is run.

V (within last week) – Retrieves records whose specified DATE field falls within the previous calendar week (Sunday – Saturday).

M (within last month) – Retrieves records whose specified DATE field falls within the previous calendar month.

A (is this many days ago) – Retrieves records whose specified DATE field is equal to the specified number of days before the date on which the search is run.

B (is this many weeks ago) – Retrieves records whose specified DATE field is equal to the specified number of weeks before the date on which the search is run.

C (is this many months ago) – Retrieves records whose specified DATE field is equal to the specified number of months before the date on which the search is run.


Posted in Create Lists, Sierra | Tagged , | Leave a comment

Floating boxes in LibGuides

A new feature has just been made available in LibGuides. You now have the option to remove the header and border for any box with a simple click.

Simply edit the box and tick the ‘floating box’ option

floating box edit

You wil still see the border in edit mode but when you preview your guide the header and border will no longer display

Floating box before and after


Posted in LibGuides | Tagged , , , | Leave a comment

Sierra Create Lists – bugs and known issues

The migration from Millennium to Sierra has unfortunately introduced a number of bugs to the Create Lists module. This blog will keep track of some of these bugs as we come across them.


If you SORT a review file (by e.g. Title) and then do a second sort (by e.g. Record Number) the number of records in the review file may decrease, so you may ‘lose’ a few records from your original count.

Items and Orders

If your review file contains items or order records, when you double click to open the item/order record you would expect to be taken to that record within it’s parent bib record. Instead, Sierra opens the bib record in the Summary tab with the first item or order record selected.

This is also happening in other areas of Sierra.

review files and items

There is a workround for this. Use the record browse (i.e., “CTRL-]” and”CTRL-[“) to move to the next item record in the list and then back to the first. The actual item record from the list should then be selected in the summary display (or item edit window). As long as you continue to use the CTRL-] to move through the records, the item records from the list should be selected in the summary display (or item edit window).

“Starts With” operator

Searching on ‘MARC TAG’  ‘STARTS WITH’ retrieves 0 results.

This search works:

BIBLIOGRAPHIC  OCLC #  starts with  “ss”

This search fails:

BIBLIOGRAPHIC  MARC Tag 001  starts with  “ss”

They should retrieve almost identical results.

Fixed field and “equal to” or “not equal to” [blank]

Searching on “fixed field equals (or not equals) [blank] or empty” fails with an error for the following fixed fields:

Bibliographic records: Date fields, Skip and Number of Revisions
Item records: Due Date, ICODE1, Loan Rule, No. of Overdues, Patron#, Total Checkouts, Checkout Date, Last Checkin, Last Checkout Date, Recall Date, Inventory Date, Year-to-Date Circ, Last Year Circ, Updated Date, Total Renewals, Copy Use, Internal Use
Order records: CDATE, ODATE, RDATE
Patron records: Birth date, Expiration date, Last Circ Activity, Total checkouts, Total renewals, Current checkouts, Current Item A – D, Ptype, Claims returned, Block until

You can try this method of searching instead:

use Create Lists in Sierra’s e (exist) logical operator for “not equal to nothing” and the n (not exist) operator for “equal to nothing”.

Posted in Sierra | Tagged , , , , | Leave a comment