Thursday, September 15, 2011

Filtering by MARC fields

Are you subscribed to  the Evergreen Reports Mailing List? If not -- and you are reading this blog -- you might want to consider joining the list.

If you are on the list, then you saw the following question posted earlier this week:
How do you filter by a MARC field in reports? For instance, I'd like to find all bib records where "CGCC" is present in 856 $y. Or I'd like to find all bib records where the 5th position of the leader is "d".
I have created report templates based on the MARC record before.  Specifically, I created a shelflist report based on keyword to help us create thematic book displays, but I lost that template somewhere along the way. (The reports module does not make it easy to stay organized, does it?) I have also created reports based on MARC fixed fields to help with database clean-up. (Check out this handout.)

To figure out how to get started on the requested template, I first needed to review how Evergreen organizes MARC data. You can do this yourself by creating a report template that will spit out all the MARC data for just one particular bibliographic record (using the TCN value as the filter):

Source = Bibliographic Record (BR)

Display Fields
BR -> TCN Value
BR -> Flattened Marc Fields -> Field ID
BR -> Flattened Marc Fields -> Tag
BR -> Flattened Marc Fields -> Indicator 1
BR -> Flattened Marc Fields -> Indicator 2
BR -> Flattened Marc Fields -> Subfield
BR -> Flattened Marc Fields -> Normalized Value

Base Filters
BR -> TCN Value [EQUALS]

Check out the example I ran.

As you see in the example, Evergreen creates a data record for each subfield within each MARC tag.

Therefore, to run a report that creates a simple list of TCNs with title and call number information based on some specific value within a MARC tag, you could create the following template:

Source = Bibliographic Record (BR)

Display Fields
BR -> TCN Value
BR -> Simple Record Extracts -> Title Proper
BR -> Call Numbers -> Copies -> Shelving Location -> Name (Rename "Shelving Location")
BR -> Call Numbers -> Call Number Label
BR -> Call Numbers -> Copies ->Copy Status -> Name (Rename "Copy Status")

Base Filters
BR -> Is Deleted [EQUALSfalse
BR -> Flattened Marc Fields -> Tag [CONTAINS MATCHING SUBSTRING IGNORE CASE]
BR -> Flattened Marc Fields -> Subfield [CONTAINS MATCHING SUBSTRING IGNORE CASE]
BR -> Flattened Marc Fields ->Normalized Value [CONTAINS MATCHING SUBSTRING IGNORE CASE]
BR -> Call Numbers ->Owning Library ->Org Unit ID [IN LIST]

Going back to the original question posted on the list, let's consider the first part of the question:
 "I'd like to find all bib records where "CGCC" is present in 856 $y."

We can run a report with filter parameters set to:
- Tag = 856
- Subfield = y
- Normalized Value= CGCC

Now this is a report very specific to a particular library's needs, so let's consider a couple other possible uses for this template:

LIST OF BOOKS BY A SPECIFIC PUBLISHER
- Tag = 260
- Subfield = b
- Normalized Value= Lonely Planet

LIST OF BOOKS BY SUBJECT HEADING
- Tag = 650
- Subfield = a
- Normalized Value = Astronomy

Now for the second part of the question:

I'd like to find all bib records where the 5th position of the leader is "d".

For this we would set the user parameters to:
- Tag = LDR
- Subfield = ]
- Normalized Value = d

However, this is not necessarily the best solution for every library. The original requester really just wants those records that have "d" in the fifth position in the leader. (The leader is a long string that includes all sorts of information about the record.) Unfortunately, the reports module does not allow us to filter for data in a specific position in a string. If you have back end access to the template, a SQL report could provide this level of granularity in the filtering no problem.

If you clicked the link above, you will see that there are 3 different reasons why "d" would appear in the leader:

05 - Record status = Deleted (THIS IS WHAT WE WANT)
06 - Type of record =  Manuscript notated music
07 - Bibliographic level =  Subunit

If you work at a public library, you probably don't have to worry about having records with manuscript notated music, nor subunits of archival material. However, in an academic library you could have issues with the report, getting unwanted manuscript and archival records in your results.   Fortunately, these types of materials can be filtered out since these settings also show up in the fixed fields.

Just add the following to the base filters in the template:

 BR -> Fixed Field Entry -> BLvl [NOT IN LIST] d
 BR -> Fixed Field Entry -> Type [NOT IN LIST] d

If you are hoping for a list of records for manuscript notated music that ALSO have a deleted record status, then you really need an SQL report.

I have one last thing to add. You will notice that I had suggested entering the end bracket as the parameter for the subfield.  The end bracket is the truncation symbol used for Evergreen reports when entering your filtering parameters. (Very useful to remember!) I used the end bracket here because the leader does not have a subfield. In the Evergreen table, that cell is blank. Yet, when creating the report you cannot leave the parameter field blank; Evergreen refuses to run a report with any blank parameter fields. So an easy workaround is to just use the end bracket. Another option would be to create a brand new template without a filter for subfield, but why have two templates when you just need one? (Anything to help keep our templates organized, right?)

3 comments:

  1. Now, what about a report based on more than one Marc Field.

    ReplyDelete
  2. Yes, that would be the natural next question, wouldn't it? With back-end access, an SQL query would take care of this type of report easily. Unfortunately our options are quite limited when using the client-side interface. Nevertheless, I have an idea of what might work, but would like to test it first. Do you have a specific report that you need? Having something concrete to work with would help me out with testing.

    Cristina

    ReplyDelete
    Replies
    1. 590 with memorial and 690 of South Carolina.

      590 is where we had Memorial Funds designated, and a 690 of South carolina would mean the title was purchased for our South Carolina room. I need a list to correct the MARC and the items attached.

      Delete