MissiNG iTEMs: auToMaTiNG THE rEPlaCEMENT worKFlow ProCEss | sMiTH ET al. 93 Tutorial Cheri Smith, Anastasia Guimaraes, Mandy Havert, and Tatiana H. Prokrym Missing Items: Automating the Replacement Workflow Process Academic libraries handle miss- ing items in a variety of ways. The Hesburgh Libraries of the University of Notre Dame recently revamped their system for replacing or with- drawing missing items. This article describes the new process that uses a customized database to facilitate efficient and effective communica- tion, tracking, and selector decision making for large numbers of missing items. T hough missing books are a ubiquitous problem affecting multiple aspects of library ser- vices and workflows, policies and procedures for handling them have not generated a great deal of buzz in library literature. For the purpose of this article, missing books (and other collection items), refers to items that were not returned from circula- tion or have otherwise gone missing from the collection and cannot be located. Significant staff time may be invested in the missing-book pro- cess by departments such as col- lection development, circulation, acquisitions, database management, systems, and public services. More importantly, user experiences can be negatively affected when missing books are not handled efficiently and effectively. While most libraries have procedures for replacing or suppress- ing catalog records for items that are missing from the stacks or have been checked out and never returned, few have made these procedures public. This article describes the procedure developed by the Hesburgh Libraries of the University of Notre Dame to replace missing items or to withdraw them from the catalog. Hesburgh Libraries’ procedure offers stream- lined, paperless routing of records for missing materials, accounts for “non- decisions” by subject librarians, and results in a shortened turnaround time for acquisitions and catalog- maintenance workflows. Hesburgh Libraries’ Experience In 2005, Hesburgh Libraries recog- nized its need to develop a stream- lined method of processing missing items. Because of personnel changes and competing demands on staff time, the routine handling of missing mate- rials had been suspended for roughly five years. During this period, cir- culation staff continued to perform searches. When staff declared an item officially missing, the item’s catalog record was updated to the item pro- cess status “missing” (MI) and paper records were routed to the Collection Development Department office, but no further action was taken. The mounting backlog of missing items in the catalog became a recur- ring source of frustration to patrons and public-services employees alike. Searches for books that were popular among undergraduates often led to items with a “missing” status. To compound the problem, budgetary constraints resulted in the suspension of spending from the fund earmarked for the replacement of missing items. Subject librarians were forced to use their own discipline-specific funds to replace items in their areas, but because there was no systematic means of notifying subject librarians of missing items, they replaced items very rarely and on a case-by-case basis—primarily when faculty or graduate students asked a selector to purchase a replacement for an item critical to their teaching or research. Also in 2005, a library-wide fund to replace materials was made avail- able. Unfortunately, by that time, the tremendous backlog of catalog records for missing items rendered the exist- ing paper-based system unworkable. As a result, a small task force was formed to manage the backlog and to develop a new method for handling future missing items. Hesburgh Libraries’ Solution The missing items task force was initially composed of eight members representing all departments affected by changes in the procedures for han- dling missing books. The task force was chaired by the subject librarian for psychology and education. Other members represented the Circulation, Collection Development, Cataloging, Catalog and Database Maintenance (CADM), Monograph Acquisitions, and Systems departments. During the initial meeting, each member described their portion of the work- flow and communicated their require- ments for effectively completing their parts of the process. Because most items with the sta- tus “missing” were ones that a patron or patrons had either recently used or requested and could therefore be con- sidered relatively high-use material, the task force quickly determined that the search time for missing books should be shortened from one year to six months. Task force members from Monograph Acquisitions were particularly interested in making this change because newer books are more easily replaced if requests were made Cheri smith (cheryl.s.smith.454@nd.edu) is coordinator for Instructional Services, anastasia Guimaraes (aguimara @nd.edu) is Supervisor of catalog and database Maintenance, Mandy Havert (mhavert@nd.edu) is head of the Monograph Acquisitions department, and Tatiana H. Prokrym (tprokrym@ nd.edu) is Senior Technical consultant at hesburgh libraries of the University of notre dame, notre dame, Indiana. 94 iNForMaTioN TECHNoloGY aND liBrariEs | JuNE 2009 sooner—many books, especially in the sciences, go out of print quickly and become difficult to replace. The Systems task force member supplied a spreadsheet containing the roughly three thousand miss- ing items. This initial spreadsheet included all fields that might be useful for staff in Monograph Acquisitions, Cataloging, CADM, and Collection Development. Various strategies for disseminating the spreadsheet to subject librarians were discussed, but all ideas for how the subject librarians might interact with the spreadsheet seemed laborious and inevitably required that someone sort through each item on the list to deter- mine whether the records needed to be sent to Monograph Acquisitions or CADM for further processing. The process seemed feasible for a one- time effort, but the task force did not see it as a suitable permanent solution. The task force then consid- ered the feasibility of developing a customized database to manage all of the information necessary for library employees—primarily subject librar- ians and Monograph Acquisitions and CADM staff—to participate in the processing of missing books. The Database Once the task force determined that a database would serve Hesburgh Libraries’ needs more efficiently than a spreadsheet- or paper-based sys- tem, the task force enlisted the help of an applications developer. Hesburgh Libraries had previously created a database for handling journal cancel- lations, and the task force decided to base the replacement application upon this model. The application is therefore written in PHP and uses a MySQL database. The first step in designing the database was to determine which bibliographic metadata (such as call number, ISBN, ISSN, imprint, etc.) would be required by subject librarians to specify replacement or withdrawal decisions, including whether the item was to be replaced with the same edition, any edition, or the newest available edition. Because replacement funds may not always be available, the task force wanted to enable the selector to identify other funds to use for the replacement purchase. Finally, the task force felt that, no matter how easy the system was to use, there would always be a few sub- ject librarians who choose not to use it. It was therefore important that the database could also account for “non- decisions” from subject librarians. Other general database requirements included that it be available through any Web browser and accessible to only those people who are part of the replacement-book process. With those requirements in mind, the task force created a list of meta- data elements to be included in the database (see table 1). On a quarterly basis, the applica- tion pulls the database fields—Title, Author, Call Number, Sub Library, Imprint, ISBN or ISSN, Barcode, Previous Fund, Local Cost, Description, Item Status, Update Date, Bib System Number, and System Number—from Hesburgh Libraries’ ILS (Aleph v18) and imports into the replacements database. For each item, biblio- graphic, circulation, and acquisitions information is retrieved from ALEPH and combined to generate the export data file. Procedurally, a list of all items with an item process status of “missing” is first retrieved into a temporary table from the item record (Z30) table. This temporary table con- sists of the system number, status field, sublibrary, collection, barcode, description, and the last date the item was modified (z30-update-date in ALEPH). A second temporary table is then created that includes the pur- chase price and fund code originally used to purchase the item. The two temporary tables are joined and their information merged, creating a sin- gle list of missing items and related acquisitions information. This list is then linked to the bibliographic tables to obtain key bibliographic informa- tion such as title, author, imprint, ISBN or ISSN, the ILS bibliographic number, and the barcode. These com- bined results are converted into an ASCII text file for import into the MySQL replacements database. Upon the import of the ASCII file, an e-mail is sent to the collection development e-mail list, informing subject librar- ians that data has been loaded and is ready for their review and input. Table 2 lists the purpose of each of the nine tables within the replace- ments database. Figure 1 illustrates the relationships and linking fields Table 1. Fields for the replacements database Database Field Data Type Title varchar(200) Author varchar(150) Call Number varchar(30) Sub Library varchar(12) Imprint varchar(150) ISBN or ISSN varchar(150) Barcode varchar(30) Previous Fund varchar(20) Local Cost decimal(10,2) Description varchar(50) Item Status char(2) Update Date Date Bib System Number int(9) unsigned zerofill System Number varchar(50) New Database Fields: Action to Take tinyint(1) New Fund Code int(10) Modified Date Date Modified By varchar(50) Notes Longtext System-Used Fields: Transfer Date Date Record ID int(10) (Auto) MissiNG iTEMs: auToMaTiNG THE rEPlaCEMENT worKFlow ProCEss | sMiTH ET al. 95 between the tables. The database provides two “pick lists” for subject librarians. The first pick list is the Action to Take field. Primary choices are “Any edition,” “Newest edition only,” “Micro format only,” and “Do not replace.” The second pick list is the New Fund field. The default choice for this field is Hesburgh Libraries’ replacement fund code, although any acquisitions funds may be selected. Both pick lists provide data integrity and assurance that all input from the subject librarians is standardized. Two internal fields, Record ID and Transfer Date facilitate programming and identification. These fields are very important for auditing and track- ing replacement records through the replacement process. Rollbacks are easily handled through the manipu- lation of these two fields. Programmatic Process For the initial implementation of this application, the task force decided that batch loads would be preformed on an as-needed basis. After the ini- tial phase of the project, the task force implemented a quarter-based schedule. For each data load, the exported records are written to a text file, which is then imported into the replacements database through an import script. The import script archives the previous group of pro- cessed records, appending them to a set of historical tables stored within the database. The import script fur- ther processes the ALEPH data by eliminating duplicate records and ensuring there is only one record per barcode and system number. The historical tables are checked to see if a missing item has already been loaded into the database and pro- cessed. If a record has already been processed, it is automatically deleted from the newly imported item list. After the successful completion of the data load, an e-mail is auto- matically generated notifying sub- ject librarians that the replacements database is ready for their review and input. The verified missing item records are then transferred to the main database table, “tblreplace- ments,” and are ready for updating. Included in the e-mail to subject librarians is a link that directs them to a search window allowing them to take action on the missing items (see figure 2). Once the subject librarians update the records, the application provides a mechanism to distribute missing book records to the appropriate departments for further processing. A Collection Development staff member runs a series of reports, each one cre- ating a Microsoft Excel spreadsheet. The first report lists missing-book records marked for replacement and is sent to Monograph Acquisitions for processing. Missing books that have been marked “do not replace” or have had no action taken on them after a certain time period are exported to a separate Excel spreadsheet that is sent to CADM for suppression or removal of cataloging records. For each report that is run, the application gener- ates an e-mail message, notifying all necessary departments that there is information to be processed. A list of processed records is available for viewing and distribution to CADM and Acquisitions as illustrated in fig- ure 3. The application also provides customized manipulation of the data records that are exported to each of the departments. This customization pulls together only the specific fields of interest to each department such that each export template is unique to each department’s needs. At the end of each replacement cycle, the application automatically creates backups and archives the missing book records. Table 2. Tables and their purposes within the database Table Description alephdump Stores imported ALEPH data before processing. tbltempreplacemetns Stores ALEPH data from the alephdump table. This data is processed and sent through verification and truncation programs. tblreplacments Post-processed ALEPH records. Primary table for all activities, actions, and fund codes selected by the subject librarians. tblactions A reference list of valid actions that can be taken by the subject librarians. tblfunds A reference list of valid fund codes; originally imported from ALEPH. tblacqrecords Temporary table that stores processed records that should be sent to Monographic Acquistions. tblcadmrecords Temporary table that stores processed records that should be sent to CADM. tblcadmnullrecord Temporary table that stores records where no action has been taken by a subject librarian. historytblreplacements An archiving table. 96 iNForMaTioN TECHNoloGY aND liBrariEs | JuNE 2009 Subject librarian workflow When subject librarians receive a message indicating a new replace- ment list is ready for review, their job is surprisingly simple. After entering their network ID and password to gain access to the database, they can select how they wish to view the list of missing books—by selected call number ranges, by the budget code with which the books were originally purchased, or by system number (the last two options are rarely used). Subject librarians can also view items that have already been processed, and they are able to sort this list by subject librarian, action taken, new budget code, or call number. Figure 1. relationship diagram for the nine database tables that were created for this application. The AlePh system number is used as the primary linking field for most of the tables. MissiNG iTEMs: auToMaTiNG THE rEPlaCEMENT worKFlow ProCEss | sMiTH ET al. 97 Initially, subject librarians encoun- ter a list of brief records for each item in the database. The brief records include system numbers, titles, authors, volume numbers (if appli- cable), call numbers, sublibraries, and ISBNs or ISSNs. If a record has already been reviewed by a subject librarian, the list will include actions taken and the names of the sub- ject librarians who took the action. To take action on an item, subject librarians select the system number, displaying the full record (see figure 4), and may then choose to replace the book with the same edition, any edition, the newest edition available, or a microform version. By using a drop-down menu, the selector can elect to pay for the replacement with replacement funds or with their own subject funds. Subject librarians who choose to replace books with their own funds are rewarded at the end of the quarter when their replacement requests appear at the top of the queue for processing by Monograph Acquisitions. Additional functionality includes the ability to directly link to and browse OPAC records for items in the database. Replacement funds cannot be used for second copies of books, so quick access to OPAC records is often useful. It also facilitates determining if the library owns other editions of the item before taking action. A notes field allows subject librarians to com- municate special instructions for Monograph Acquisitions or CADM, and records can be e-mailed to other librarians for additional input with just a few clicks. Subject librarians are able to return to the database at any time during a given quarter to continue making decisions on their missing books and make any adjust- ments to prior decisions as necessary. If a subject librarian takes no action on an item by the end of the quar- ter, it is assumed that it is not to be replaced, and these untouched items are sent to CADM for removal or suppression. Figure 2. replacements application search window Figure 3. Processed book records ready to be sent to Monograph Acquisitions and cAdM. notification and data transmission to these units are achieved through the Send buttons on this webpage. 98 iNForMaTioN TECHNoloGY aND liBrariEs | JuNE 2009 Monograph Acquisitions workflow Once the quarterly database pro- cessing completes, a comma-sepa- rated file is delivered to the shared Monograph Acquisitions e-mail address. Monograph Acquisitions staff format, sort, and begin search- ing the spreadsheet, giving priority to the orders designated for replace- ment by subject librarian funds over those funded from the library replacement fund. Staff members routinely search the library catalog for duplicate titles or review orders in process for the same title prior to searching with our library materials vendors. Staff members ensure that replacement funds are not used to purchase second copies. Material that is not available for purchase is referred by Monograph Acquisitions to the subject librarian for direction. Sometimes the mate- rials may be kept on order with a vendor to continue searching for out- of-print or aftermarket availability. Other times it is necessary for staff to cancel the order and remove the record from the system completely. Likewise, the missing edition may have been subsumed by a newer, revised edition. Subject librarians are contacted by search and order staff in the Monograph Acquisitions department regarding availability of different editions when they did not specify that any edition would be acceptable. When the Monograph Acquisitions department places a replacement-copy order, the search-and-order unit adds an ILS library note field code designat- ing the item is a replacement (RPLC), the bibliographic system number of the item being replaced, and any typi- cal order notes such as the initials of the staff member placing the order. The RPLC code alerts the receipt unit to route new items to the Cataloging supervisor, who then reviews and directs the items to either Cataloging or CADM for processing. Catalog and Database Maintenance (CADM) workflow CADM is usually the last unit to edit records in the missing books work- flow. The unit receives two reports from the database: a “do not replace” list and a “no action taken” list. Both reports get the same treatment: All catalog records for titles listed are removed from the catalog. Removal of catalog records is accomplished either by suppression/ deletion of the bibliographic records or complete deletion of all records (item, holdings, bibliographic, and administrative) from the server. For titles that have order or subscrip- tion records attached to bibliographic records, a suppression/deletion pro- cedure allows the record to be sup- pressed from patrons’ view while preserving the title’s order and pay- ment history for internal staff use. Records are completely deleted when no such information exists (e.g., a gift copy or an older record that has no such data attached). Because it takes a long time to review each newly loaded batch from the catalog into the database, some records that come to CADM for deletion no longer need to be deleted if missing books are found and returned to the shelves. It is very important for staff working on the cleanup of records to check the item process status and not delete any items that have been cleared of the “missing” status. Fortunately, Aleph allows staff to look up an item’s his- tory and view prior changes made to the record. This item history feature eliminates unnecessary shelf checks for items appearing on CADM reports that are no longer listed as “missing” in the catalog. Occasionally, CADM receives requests to delete records directly from Monograph Acquisitions and Cataloging staff because of a revised selector decision. This often occurs when a replacement item is only available in a different edition from Figure 4. Full record for a missing book in the replacement database MissiNG iTEMs: auToMaTiNG THE rEPlaCEMENT worKFlow ProCEss | sMiTH ET al. 99 the one originally sought, or when an item is ultimately unable to be replaced because it has gone out of print or a vendor backs out of a pur- chase agreement. When a different edition is received to replace a missing item, the replacement copy is sent by the receipt unit in Monograph Acquisitions to Cataloging for copy or original cataloging, and CADM is alerted by either Monograph Acquisitions or Cataloging staff if the record for the missing item needs to be deleted. Because Monograph Acquisitions often orders the replace- ment on its own record with appro- priate bibliographic information (we keep the original record just in case the missing piece is found while we wait for replacement), the record for the missing book does not come to CADM on either of the two reports. Perhaps in a library with a differ- ent makeup of technical services the process would be more streamlined, but because Hesburgh Libraries has separate cataloging and database maintenance units, we have created such partnerships to make sure noth- ing falls through the cracks. So far it has worked well, and every party in the process knows and carries out their responsibilities. Issues While the initial implementation suc- cessfully brought a large backlog of missing records into the database, subsequent loads included dupli- cate records of some items processed in earlier batches. This duplication occurred, for example, if an item was identified for replacement in a prior database review cycle, but a replacement request had not yet been processed by Monograph Acquisitions staff. Because such an item is still identified as “missing” in the catalog, it was again included in data loaded from the catalog into the missing-books database, creating confusion for selectors, CADM, and Monograph Acquisitions. To resolve this problem, the import process was revised to include a search for previ- ously loaded items, deleting them before records are viewed by collec- tion managers. A second issue involved the tim- ing of the data load from the catalog into the replacements database. For various reasons, the data load file was not fully generated for several of the scheduled processing dates. To remedy this problem, the appli- cation automatically generates an e-mail confirming a successful data load to the Collection Development Department staff. There is continued debate as to whether the missing- items file should be created on a daily basis, providing the capabil- ity for Collection Development to import new data at one time rather than periodically. Results Since implementing our new system, Hesburgh Libraries has processed records for 5,141 missing items. Since its creation, twenty-five librarians have consulted the database and twenty-three of thirty subject librari- ans have used the database to request replacements. Of the 5,141 records loaded into the database, 2,537 items (49 percent) have been selected for replacement, and 2,604 items (51 percent) have either been sup- pressed or deleted from our catalog. Replacement funds are renewed on an annual basis and have not yet run out. As a reflection of the collection strengths at Hesburgh Libraries, most of the missing books (21 percent) fell in the Theology/Religion call number range. Language and Literatures was the second most popular collection for missing items (17 percent). Other collections with significant numbers of missing books are History (15 percent), Social Sciences (17 percent), Science (12 percent), and Philosophy (10 percent). Conclusion Although the process could certainly be further developed and refined, the Hesburgh Libraries missing books application is an amazing improve- ment over the extremely outdated paper-based method of dealing with missing library materials. The pro- cess works; it is both efficient and effective, and employees who engage in the process have reported satisfac- tion with it. It has not only allowed Hesburgh Libraries to catch up on its backlog but, more importantly, to stay current and organized, keep- ing the catalog more accurate and patrons more satisfied. Furthermore, should the libraries opt to do a full inventory in the future, the cur- rent system will prove invaluable. The authors are pleased to have the opportunity to share our experiences with interested libraries. Feel free to contact any of the authors for further information. Reproduced with permission of the copyright owner. Further reproduction prohibited without permission.