NEVER BEEN LOADED CHECKLIST

1. Create and prep load spreadsheet.

2. Start Load Report document.

3. Create resource record if needed.

4. Create list of bib records related to the resource. (Search: checkin [c]>resource id [p]>has[h]>xxxx [resource id]  OR  bib[b]>MARC[!]856>has[h]>xxxx [part of a base url])

5. Update good bibs list.

6. Update ermbib template. (Admin>>Settings>>Record Template>>ermbib)

7. Check ERM Parameters NOT to create brief bibs. (Admin>>Parameters>>ERM>>Electronic Holdings Administration)

8. 1^st Load – load against bib records related to resource only.

9. Save titles not in file report as titlesnotinfile.txt.

10. Delete titles not in file (there should only be titles not in file if the database had a previous Coverage Load).

11. Save catalog load report as load1.txt.

12. Resolve errors with alt lookup and create check-ins on resolved titles to aid matching.

13. Check ERM Parameters to create brief bibs. (Admin>>Parameters>>ERM>>Electronic Holdings Administration)

14. Run load 2 against all good bibs to create brief bibs.

15. Resolve errors.

16. Run lists and send to Cataloging for global updates.

1. PREPARE FOR CATALOG LOAD

If this is the first time we have done a catalog load for this resource, or if the resource has changed:

• Make sure that a resource record exists in III and that it matches the Serials Solutions info.
  No resource record
• Make a folder on S:\ERM\coverage loads and save all documentation related to the load in this file, for example csv files, reports, etc.
• Create a "coverage notes" document to list the steps you have taken. This will aid in troubleshooting later on.

2. DOWNLOAD COVERAGE FROM SERIALS SOLUTIONS

Follow the procedures for Downloading Coverage.

3. LOAD FIRST COVERAGE

• There are two strategies for creating a review file to load coverage against:
  • If the records for the resource already been loaded use the resource id to pull them together easily, create a list of bib records based on the resource id. (Search: checkin [c] > resource id [p] > has [h] > xxxx [resource id]
    You can easily add individual titles to the list later on in the second load after your initial review.
  • If there are no checkins with resource ids, you should create a fresh list of "good bibs."
    The search strategy can be found at Create Lists > Saved Searches mej good bibs erm and you will need one of the 150,000 max records lists.

NOTE: If the set is one that we catalog fully, add an argument that bcode3 is not equal to f. That will exclude "stubby" ERM bibs from being matched upon.

• In Sierra, Choose the ERM workflow and "Coverage Load" function.
   
• Check your Sierra settings
  •  Look at the top menu under Admin>>Parameters>>ERM>>Electronic Holdings administration
  • Make sure the holdings record template is set to use the ermMULN template:
  • For the first load, you will not be creating brief bibs. Make sure that the "create bib record" checkbox is unchecked.
  • Save your settings. These settings will persist from session to session but it is a good idea to check each time.
• Choose the file to be loaded by the Select File button. Browse to the csv file you have saved and choose it.
• If loading catalog, make sure that the Load catalog button is checked and that you have selected the review file you wish to load against.
• Generally, you will also be loading coverage at the same time so make sure that button is also checked.
• Review the settings and press Submit.
• Sierra will attach holding based on the following:

4. ANALYZE RESULTS

• Select “view results” tab to see the results of the load.
• Save the titles not in file report by selecting the icon for "Save Rpt." at the top right corner. Save a copy in the ERM coverage load folder. This report is only generated when the database has been previously coverage loaded. Select all the titles not in file and delete them from coverage.
• Download the load report by selecting the icon for “Save Rpt.” at the top right corner. Save a copy in the ERM coverage load folder.
• Review the report in Excel.
• Tally how many titles fall into each of the following categories and preform error resolution as needed:
  • Multiple Titles Found: Identify the correct bib and put an alt look up in the coverage edit. The alt lookup is the OCLC number, and can be added to the title in the coverage edit. Create a checkin in the correct bib and make sure it links properly. If there should be an ERM link in both titles, do not add an alt lookup.Then manually create a checkin in each and make sure it links properly.
  • Multiple ISSN Found: Identify the correct bib and put an alt look up in the coverage edit. The alt lookup is the OCLC number, and can be added to the title in the coverage edit. Create a checkin in the correct bib and make sure it links properly. If there should be an ERM link in both records, do not add an alt lookup.Then manually create a checkin in each and make sure it links properly.
  • Found as linked: In load 1, no found as linked should appear, as the check-in records have never been created before. If there are found as linked in a load 1, then it is probably either a title change or a gap in coverage. Check and make sure the link is in the correct title, and/or all the holdings/links appear. In load 2, found as linked is not an error.
  • No matches: These are usually not errors. It just means that these titles were found in Sierra, but not in the review file. When you load just against a series name, then these might be attached during the second load when you run against all good bibs. If this error is present when you run against all good bibs, it means we might have an inappropriate record (such as vendor) in Sierra, but it won't match because it was not in all good bibs. This is a good thing! This error will not happen in load 2, because brief bibs will be created instead.
  • No search results: These titles/issns can not be found anywhere in Sierra. Brief bibs will be created in the load 2. If you find that the title is really in Sierra (but with slightly different title), then add an alt look up and manual ERM record. A brief bib will still be created on subsequent loads, so add the title to the load report, and delete the brief bib after the final load.
  • Check-ins created: Bibs found with no errors and ERM checkins were created.
  • Brief bibs and checkins created: Load 2 only. Bibs were not found, so brief bibs were created and ERM checkins were created and attached.

5. SECOND LOAD WITH BRIEF BIBS

• Review the brief bib template for Serials Solutions coverage loads. Most often you will use the ermbib brief bib for erm loads template. Many times, no changes will need to be made, but make sure that the Genre 655 field correctly identifies the genre being loaded.
  • 655  7  Electronic journals. |2AFU
  • or 655  7 Electronic newspapers. |2AFU
    • If the package has been designated open access, use the ermbibOA brief bib for Open Access.
• Go to Coverage Load and select your csv file.
• This time, check the box in the Admin>>Parameters>>ERM>>Electronic Holdings administration for “Create bib record if no match upon load.” and instruct the system to use the correct brief bib record template.  
• Submit for catalog load again.
• Add results to the load report. If the numbers look unexpected when compared to the previous loads numbers, then investigate further. Examples could be if more brief bibs then expected were created or if the found as matched number is smaller in the second load.

6. CLEAN-UP REMAINING BIBLIOGRAPHIC RECORDS

• Now you want to add the 655 to the full records that were matched upon. To do that, create a review file of all 1^st and 2^nd round matched full bibliographic record titles. Use the search strategies box below to add in the correct marc tags.

Have the serials librarian perform a Global Update to add the correct 655 and/or 690 to these records.

• If brief bibs were created, have the serials librarian add a 222 field with Global Update.
   
• If the package is one we wish to fully catalog (not an aggregator like Ebsco or ProQuest), create a list of the brief bibs you created in the second load and ask Serials Cataloging to overlay those records with the appropriate bibs by adding the brief bibs to createlist 72 SERIALS CAT - ERM bibs that need full cataloging.
   
• If an entirely new database from SS, notify the Web Librarian so that Find it! can be adjusted.

EXAMPLE OF LOAD REPORT

Wiley online journals Load 06/10/15

 

RESOURCE ID     Wiley-Blackwell AGU American Geophysical Union

ERM LINK            SLKe1000208x

RESOURCE ID     Wiley-Blackwell AGU Digital Library

ERM LINK            SLKe10002091

RESOURCE ID     Wiley-Blackwell Full Collection

ERM LINK            SLKe10002078   

RESOURCE ID     Wiley-Blackwell Journals (Frontfile Content)

ERM LINK            SLKe10002108

Authority             Wiley online journals

 

1. Ran load 1 against series=Wiley online journals

multiple issn	18
no matches	10
no search results	9
found as linked	8
checkin created	470
total	515

 

2. Fixed multiple issn errors and found as linked errors by adding alt look ups and checkin records
3. Ran load 2 against all good bibs to create brief bibs.

Multiple issn	15
Found as linked	479
Brief bib created	9
Checkin created	12
totals	515

 

4. Double checked multiple issn errors. All still ok from load 1.
5. Ran lists for 506,655,222.

CONVERTING FILES TO .CSV WITH GOOGLE

Converting files to csv using Google Docs to ensure proper rendering of diacritical marks.

---

Databases are loaded in the .csv file format; however, if you save a file directly from the monthly holdings report as .csv the diacritical marks (and some foreign languages) will not display correctly.

Example of improper loading: 

To display correctly, the file needs to be opened in google sheets. Then re-saved as a csv.

• The file you want to work with should be saved initially as an excel workbook (xlsx).
• In google sheets, Import the file. (Note: Do not use the Open function).
• Then download the file as a .csv.

This file should now be able to be opened correctly in Sierra.

EXAMPLE OF PREPPING CAT/COVERAGE LOAD FILE

Reformatting database monthly holdings in Excel to .csv to correctly render diacritics for CAT/Coverage reloads:

1. Locate the most recent monthly holdings Excel spreadsheet that was reformatted from Serials Solutions’ report. [Serials drive (\\alexandria)-->ERM -->monthlyholdingsreports-->(most recent folder date; ex.201606=June 2016)].

2. Create a new file for saving the .csv file when completed. [Serials drive (\\alexandria)-->ERM-->coverage loads-->Open file of the database you are working on (or create new folder with name of database if not already in the list)-->Create new folder and open].

3. Start a new Excel spreadsheet. Copy (ctrl “c”) and paste (ctrl “v”) column labels from the Monthly Holdings Report into row 1 of the new spreadsheet. Confirm that the header row has column names in the format:

• Provider
• Title
• ISSN
• eISSN
• StartDate
• EndDate
• Embargo
• URL
• **Public_Note  

**Special Instructions: When prepping databases "Business Insights: Essentials" and "Medline Complete" add an additional column at the end of the spreadsheet and label it "Public_Note". Add the note: "Business Insights: Essentials [Selected Articles]" or "Medline Complete [Selected Articles]" for each title in its respective database.

The headings can be in any order but the column names are case-sensitive and must match.

4. Find (ctrl “f”) the database you want to convert into .csv format in the Monthly Holdings Report. (Using the filter feature is often quicker if prepping multiple databases in one sheet.) 

5. Highlight all the titles in that particular database and copy/paste the titles into the new Excel spreadsheet starting on row two. 

6. If several days have passed since the monthly holdings report was created, verify in Serials Solutions that the number of titles in the database is roughly the same as the number copied from the Monthly Holdings Report. If not, inform the person doing the reload. The Monthly Holdings Report may need to be updated to complete the reload for that database.

7. In the new spreadsheet make sure the columns are wide enough so that fields in the date columns do not read “########.” This will copy over as text when you save to a text file.

8. Confirm that your data omits any blank rows or columns by highlighting and deleting empty rows.

9. Check that start and end dates are correctly formatted.

10. Do a custom sort. First sort by “ISSN”; add a second layer and sort by “eISSN”. If there are titles that do not have ISSNs but do have eISSNs, cut and paste the eISSNs into the ISSNs column. This aids matching during the reload process. 

11. Do another custom sort by provider, then title. Save the new Excel spreadsheet onto your desktop as an Excel Workbook.

12. Start a blank Google Sheet. Click File-->Import-->Upload-->Select a file from your computer. Select the file you just saved to your Desktop.

13. Click Open-->Make sure the Import action is “Replace spreadsheet”-->click “Import”.

14. Click on “Untitled spreadsheet” at the top and rename the spreadsheet the name of the database.

15. Click File-->Download as-->Comma-separated values (.csv, current sheet). Click on the bottom right corner link “Show all downloads…”. The Downloads folder will open. Drag and drop the newly formatted spreadsheet into the coverage load Reload file that was created in step two.

16. Share the Google Sheet file with the person doing reloads by clicking the “Share” button in the bottom right corner of the spreadsheet and entering the person’s email.

1. In Sierra, choose Coverage Edit from the Function menu.
2. Find the database you are planning on loading coverage for and right click on the database name in the left-hand column.
3. Select Edit Field Protection. Check to make sure that the “alt_lookup” field has a checkmark beside it. If it does not, check the box and select Save.
4. In Sierra, choose Coverage Load from the Function menu.
5. Uncheck Load Catalog unless you are planning to load coverage into the catalog as well as into the WebBridge knowledge base.
6. Choose Select File and browse to find the csv file you just saved and click Continue.
7. Sierra will pre-process your file to a text (txt) format to check for errors in formatting. If the system encounters any errors in formatting or values it does not understand, it will give you an error and place your cursor at the line number of the file with the error. You can edit this and fix any errors as long as the csv file is not already open on your computer.
8. Once the file preprocessing is complete, check the fields to make sure that the values have come through correctly.
9. Limit to Provider and choose the provider that you are loading coverage for.
10. Click Submit.
11. To see your load results, click the View Results tab.
12. If this is a new or revised resource, make the appropriate changes in WebBridge and test at least one article for linking. Delete any old resources from the coverage database.

 

1. Create list of bib records related to the resource. (Search: check in[c]>resource id[p]>has[h]>xxxx)

2. Update good bibs list.

3. Update the appropriated brief bib template. (Admin>>Settings>>Record Template)

4. Check ERM Parameters NOT to create brief bibs. (Admin>>Parameters>>ERM>>Electronic Holdings Administration)

5. 1^st Load – load against bib records related to resource only.

6. Reports: Save catalog load report as load1.txt BUT ALSO Titles not in file report as titlesnotinfile.txt.

7. Titles not in file report: Delete titles from coverage by selecting 'Delete Selected'. This will create empty checkin records AND generate the “Delete Candidates” system-generated review file which will be copied into a list in the next step.

8. If there were titles not in file, go to create lists, find an empty list and click Copy. Look for a list at the very end: “delete candidates from coverage load.” (See search strategy below)

9. Copy the list and say YES to emptying original file.

10. Create a second list of all the empty checkins. *Review against the delete candidate review file. (See search strategy below)

11. Review the results and delete the checkin records that no longer have holdings. The Serials Supervisor can globally delete these holdings.

12. Create a third list of brief bibs based on the review file (resource ids): Bibs without links to item, checkin, or order records AND bcode3=f. (See search strategy below)

13. Review the resulting list and have Web Librarian delete those bibs.

14. Continue with the standard load procedure: resolved errors from load 1 and proceed to load 2 against all good bibs.

15. Run lists and send to Cataloging for global updates.

MONTHLY QUALITY CONTROL

RUN ALL LISTS AGAINST ALL GOOD BIBS (unless stated otherwise)

1. Empty Check-Ins (Must ask Sierra to run the coverage database utility first)
2. Bibs that need to be reviewed for deletion
3. Needs 655 Or Needs 655 Removed
4. Placeholder checkins need deleted
5. Internet item records need deleted
6. Tickler Review

   ---

Empty Check-Ins (Must ask Sierra to run the coverage database utility first):

• Store Record Type:Checkin[c]
• checkin[c]>scode3[118]>equal[=]>serials solutions[s]
• and checkin[c]>int. note[z]>has[h]>holdings from erm coverage load
• and checkin[c]>MARC856[!]>equal[=]>blank

Note: You must have "Include coverage database information" checked in Sierra to get accurate results. Sierra>>Admin>>Settings>>Create List>>Include database information box (checked).

---

Bibs that need to be reviewed for deletion:

     Brief bibs (ERM holdings)

• bib[b]>bcode3[31]>equal[=]>f
• and bib[b]>linkrec[^]>not exist to[n]>order[o]
• and bib[b]>linkrec[^]>not exist to[n]>item[i]
• and bib[b]>linkrec[^]>not exist to[n]>checkin[c]

     Others: (Note: while many law school records have been eliminated, double check to make sure the record is not law before beginning maintenance).

• bib[b]>bcode3[31]>does not equal [!=]>delete [d]
• and bib[b]>Bib LVL[b002]>equal to[=]>Journal/Serial[s]
• and bib[b]>bcode31>does not equal[!=]>Law Converted [L]
• and bib[b]>bcode31>does not equal[!=]>Law-Brief Bib[u]
• and bib[b]>linkrec[^]not exist to [n]>order[o]
• and bib[b]>linkrec[^]>not exist to[n]>item[i]
• and bib[b]>linkrec[^]>not exist to[n]>checkin[c]
• and bib[b]>MARC856[!]>equal to[=]>blank
• and bib[b]>MARC860[!]>equal to[=]>blank
• and bib[b]>MARC655[!]>has[h]>electronic

---

Needs 655:

• checkin[c]>int. note[z]>has[h]>Holdings from ERM Coverage Load
• and bib[b]>MARC655[!]>all fields do not have[a]>Electronic

Needs 655 removed:

• bib[b]>MARC655[!]>has[h]>electronic journals
• bib[b]>MARC856|u[!]>equal to[=]>blank
• {{{and bib[b]>linked rec[^]>not exist to[n]>checkin[c]
• or checkin[c]>int note[z]>all fields don't have[a]>Holdings from erm}}}<- group together

Until the broken URL project is completed, it may be beneficial to add a line bib[b]>Local Note[v]>all fields don't have[a]>ser proc broken

Also run: 

• bib[b]>MARC655[!]>has[h]>electronic newspapers
• bib[b]>MARC856|u[!]>equal to[=]>blank
• {{{and bib[b]>linked rec[^]>not exist to[n]>checkin[c]
• or checkin[c]>int note[z]>all fields don't have[a]>Holdings from erm}}}<- group together

---

Placeholder checkins need deleted:

• checkin[c]>int. note[z]>has[h]>Holdings from ERM Coverage Load
• and checkin[c]>int. note[z]>has[h]>Placeholder for ERM coverage and internet location
• Use this list as a review list to run 'Store Record Type:Item' list for only checkins with Placeholder int. note.

---

Internet item records need deleted:

• checkin[c]>scode3[118]>equal to[=]>serial solution[s]
• {{{and item[i]>location[79]>equal to[=]>internet resource[inter]
• and item[i]>itype[61]>equal to[=]>internet resource[43]
• and item[i]>status[88]>equal to[=]>on internet[j]}}}<- group together
• Use this list as a review list to run 'Store Record Type:Item' list for deletion.

---

Tickler Review

      Review ticklers in check in records:

           Store record type: checkin C

• checkin[c]>location[40]>equal[=]>internet resource[inter]
• and checkin[c]>Ticklerlog[u]>not equal top[!=]>blank
• After creating list, if necessary export the list into an excel to review for correctly entered tickler information, paying particular attention to date entry. For information on how to export the ticklers into an excel file see the tickler Research Guide page.

and then review ticklers in resource records: 

            Store record type: Resource e

• Resource[e]>Tickler log[c]>not equal to[!=]>blank
• and Resource[e]>Rights Type[172]>not equal to[!=]>Licensed Law[L]

NEVER BEEN LOADED

List should be run with the following search strategies:

Bibs that need 655:

• checkin[c]>resource id[p]>has[h]>xxxx

• and bib[b]>MARC655[!]>all fields do not equal[a]>Electronic journals.

Brief bibs that need 222:

• checkin[c]>resource id[p]>has[h]>xxxx [resource id]

• and bib[b]>bcode3[31]>equals[=]>ERM[f]

• and bib[b]>MARC222[!]>equals[=]>[leave blank]

Bib needs appropriate 856 removed:

• Use review list from Load 1.

Bibs that need 690 (For Open Access Packages Only):

• checkin[c]>resource id[p]>has[h]>xxxx

• and bib[b]>MARC690[!]>all fields do not equal[a]>Open Access journal

RELOADS (MAINTENANCE)

List should be run with the following search strategies:

1. Delete Candidate List
2. Empty Checkins 
3. Bibs to Delete
4. Bibs that need 655
5. Bibs that need 222
6. Bibs that need 690 (For Open Access packages only)
7. Choosing which Databases to Reload

---

Delete Candidate List:

• Select empty list and select copy

• Choose Delete Candidate list

Empty Checkins:

• Checkin list

• Review against "Delete Candidates"

• checkin[c]>resourceid[p]>has[h]>xxxx

Bibs to Delete:

• Review against "Original 830 List"

• bib[b]>bcode3[31]>equal[=]>f

• and bib[b]>linkrec[^]>not exist to[n]>order[o]

• and bib[b]>linkrec[^]>not exist to[n]>item[i]

• and bib[b]>linkrec[^]>not exist to[n]>checkin[c]

Bibs that need 655:

• checkin[c]>resource id[p]>has[h]>xxxx

• and bib[b]>MARC655[!]>all fields do not equal[a]>Electronic journals.

Brief bibs that need 222:

• checkin[c]>resource id[p]>has[h]>xxxx [resource id]

• and bib[b]>bcode3[31]>equals[=]>ERM[f]

• and bib[b]>MARC222[!]>equals[=]>[leave blank]

Bibs that need 690 (For Open Access Packages Only):

• checkin[c]>resource id[p]>has[h]>xxxx

• and bib[b]>MARC690[!]>all fields do not equal[a]>Open Access

---

Choosing which databases to reload: These searches have been superseded by the new frequency schedule which can be found on the next tab.

Brief Bibs Cat Loads:

• Store record type: Resource [e]
• Resource[e]>Load Type[177]>equal to[=]>Brief Cat Load[b]
• and Resource[e]>Resource Status[178]>equal to[=]>Active[-]
• and Resource[e]>Load Date[195]>less than or equal to[<=]>Choose a date 
• and Resource[e]>Rights Type[172]>not equal to[!=]>open access [o]

Full Bibs Cat Loads: 

• Store record type: Resource [e]
• Resource[e]>Load Type[177]>equal to[=]>Full Cat loads[f]
• and Resource[e]>Resource Status[178]>not equal to[!=]>Inactive[i]
• and Resource[e]>Load Date[195]>less than or equal to[<=]>Choose a date 
• and Resource[e]>Rights Type[172]>not equal to[!=]>open access [o]

Open Access Databases with Brief Bibs:

• Store record type: Resource [e]
• Resource[e]>Rights Type[172]>equal to[=]>Open access[o]
• and Resource[e]>Resource Status[178]>not equal to[!=]>Inactive[i]
• and Resource[e]>Load Type[177]>equal to[=]>Brief Cat Load[b]
• and Resource[e]>Load Date[195]>less than or equal to[<=]>Choose a date 

Open Access Databases with Fully Cataloged Bibs:

• Store record type: Resource [e]
• Resource[e]>Rights Type[172]>equal to[=]>Open access[o]
• and Resource[e]>Resource Status[178]>not equal to[!=]>Inactive[i]
• andResource[e]>Load Type[177]>equal to[=]>Full cat load[f]
• and Resource[e]>Load Date[195]>less than or equal to[<=]>Choose a date 

Note: Open access databases need the 690 Open access added or removed depending on the situation.

RELOAD (WHAT TO RELOAD)

A reload frequency note has been added to Resource records to denote when a group of resources should be reloaded.

The list contains the following reload schedules:

• Reload Frequency: Monthly
• Reload Frequency: Every six months
• Reload Frequency: Yearly
• Reload Frequency: Every three months

Ticklers have been set up in selected resources to send an email to the eSerials box to remind the eSerials staff to run one of the lists below and reload the selected resources.

---

CreateList Search Strategies

For monthly:

• Store record type Resource e
• Resource [e]>>Resource Status[178]>>=>>-
• and Resource [e]>>Staff Note[n]>>has>>Reload Frequency: Monthly

For every six months

• Store record type Resource e
• Resource [e]>>Resource Status[178]>>=>>-
• and Resource [e]>>Staff Note[n]>>has>>eload Frequency: Every six months

For every three months

• Store record type Resource e
• Resource [e]>>Resource Status[178]>>=>>-
• and Resource [e]>>Staff Note[n]>>has>>Reload Frequency: Every three months

For yearly

• Store record type Resource e
• Resource [e]>>Resource Status[178]>>=>>-
• and Resource [e]>>Staff Note[n]>>has>>Reload Frequency: Yearly

USING CREATELIST

For information regarding how to use the createlist module see:

Sierra Create Lists

OVERVIEW

The following videos provide a detailed walk-through of reloading one of our more labor intensive databases: the Directory of Open Access Journals.