Printer Friendly

Coding a Custom E-Mail Alert Service for New Books.

We must constantly look for new ways to serve our patrons. While traditional "brick-and-mortar" services provided within the library will continue to change and grow due to technology, the opportunities abound in the area of designing new electronic services to meet patron demands. The relative in-expense of e-mail as a means of communication makes it one of the ideal mediums for sharing information with patrons.

Carmel Clay Public Library (CCPL, is a medium-sized public library in Carmel, Indiana, that serves a community of about 55,000. We have always strived to provide new, original, and sometimes cutting-edge services for our patrons. CCPL has had a large Web presence as far back as 1996. One of our greatest challenges is to provide unique and varied content and services.

Our new arrivals section has always been one of the more popular areas of our Web site. We update the list manually on a regular basis, adding titles as they arrive. However, this information is somewhat incomplete--titles on the list are not classified or sorted in any way, and not all titles can be included. (The list would be too large!) We decided that we needed to offer a new Web-based service, one that patrons could use to create their own lists of new arrivals. The logical method was to create a searchable database of new titles, which could be sorted or manipulated according to the patron's preference. While this in itself seemed like a great idea, we decided to take the process one step further and actually deliver customized lists to the patron, via e-mail.

Enter the Book Alert Service by E-Mail

Once we had an idea in mind, the specifics began to come together quickly. Book Alert would be a hybrid Web- and e-mail-based system that patrons could use to notify themselves of new arrivals. Patrons would sign up via the CCPL Web site, choosing subjects they are interested in. Each month, the patrons would receive a list of new arrivals meeting their criteria. They would be able to log in and change their preferences, should they choose to do so. They could also generate their own lists with the new preferences, based on the current data.

We already had most of the infrastructure in place, since we develop custom Web applications using Allaire ColdFusion Web development software and Microsoft Access and SQL Server database software on a regular basis. The first, and most important, challenge would be to retrieve the data from the library automation database and manipulate it into a usable format.

Retrieving the Data from the Database

CCPL runs its Web services under Windows NT, while its automation system is UNIX-based. The machines run separately and do not share data. We determined that since all items that were new in the catalog were flagged with the word "New Arrival" in our Notel database field, we could write a custom query to find titles that were flagged with that word. The data we needed to alert the patron of a new title would require the title, author, and Dewey call number (more on that later). So, we wrote a simple query to select the appropriate data:

select author, title, call_number

from items

where Notel matches 'N[[Ee].sup.*]';

This query would unload the data to a flat file, which we then could FTP to our Web server and manipulate. Once the data was in a useable format, it would be stored in a Microsoft Access database. We then had a large table of new arrivals, but this list could contain items added over 6 months ago! We needed a method of trimming the list down to items added only in the last month.

Creating a Picklist of New Arrivals

Each month, we have a large list of new arrivals. Items hat are marked with "New Arrival" aren't cleared after every month, so it's important to compare the current list to the previous month's list to eliminate duplicates. We accomplish this ask by comparing two Microsoft Access tables, and creating a third table out of the difference between the two. Then we store this third table in what we called the "Picklist" table (the table from which Book Alert picks). We do this by using ColdFusion's CFLOOP function:

[less than]CFLOOP query="getbooks"[greater than]

[less than]CFQUERY name="finddupes" datasource="bookalert,[greater than]

SELECT id,callnum from masterlist

where callnum = '#callnum#'

[less than]/CFQUERY[greater than]

[less than]CFIF #finddupes.recordcount# LTE 0[greater than]

[less than]CFQUERY name="addbook" datasource="bookalert"[greater than]

INSERT into picklist (author,title,callnum)

values ('#author#','#title#','#callnum#')

[less than]/CFQUERY[greater than]

[less than]/CFIF[greater than]

[less than]/CFLOOP[greater than]

We loop through each item, checking to see if there is an item with an identical call number in the master list table. If there isn't a duplicate, then that item's author, title, and call number are written into the picklist table.

After these steps are completed, we have a table full of new arrivals from the last month. However, all of these items still have Dewey call numbers attached, which aren't of any use (yet). Each item will need its Dewey number converted to a subject before we can match items to patron preferences.

Converting Dewey Numbers to Subjects

Before we could write a custom program to sort the data, we needed to decide how many subject categories there would be and what they would encompass. We ended up with 45 categories, spanning our collection from architecture to writing and literature. Once we had determined what the subject categories would be, we needed to create a subject for each item in the picklist table.

The easiest way to sort the new arrivals into subject areas was to compare each item's Dewey number to a known subject, and then set the subject appropriately. We wrote a special program in ColdFusion that queried each item in the picklist table for its Dewey number, then assigned it a subject based on that number. The ColdFusion program loops through the picklist with a collection of "if-then" statements, such as the following:

[less than]CFIF Left("#callnum#",3) EQ 'SCI'[greater than]

[less than]CFQUERY name="updatescifi"[greater than]

update picklist

set subject = 'Scifi'

where id = #id#

[less than]/CFQUERY[greater than]

[less than]/CFIF[greater than]

This statement examines the first three characters of the variable call number and matches it to a subject. For example, if the first three characters are equal to SCI, then the item is categorized as Scifi, or Science Fiction.

Inevitably, there are a few items that end up with no classification at all. We scroll through the picklist table to ensure that each item has been given a classification. Once we had confirmed that it was possible to extract and convert the data into a useable format, it was time to write the patron interface for the data.

Building the Signup Module for Patrons

When we first envisioned Book Alert, we had no idea how much use or demand there would be for such a service. We also envisioned a point at which managing the service might become a headache, so we decided to write an interface that patrons could use to update and modify their own accounts, without any staff involvement. There are two critical pieces to Book Alert's patron module: the patron signup module and the list generation module.

The signup module is a simple HTML form page that collects certain information from the patron: name, phone number, library card barcode, and of course e-mail address. The phone number is collected to use for patron login--if a patron needs to access Book Alert to change or modify his account, the patron's phone number and library barcode are used. We determined that using the phone number would be easier than using a password, which is easier to forget. The HTML form then posts the data into a Microsoft Access users table.

Once the patron has entered his vital information, he is given a choice of 45 categories from which to select. Patrons can select up to 10 subject categories they want to be notified about. If they choose to select more than 10 subject categories, they need to sign up for the service twice. As with the other patron information, each selection is stored in the patron users table, under that patron's unique ID. Once the patron has entered his preferences, the task of the signup module is finished.

Generating the Lists to Send Out

The next important patron module is the list-generation module. This module is the piece of ColdFusion code that allows the patron to generate a list of new titles. Once the patron has signed up, he is asked if he would like to generate a list of new arrivals to get started. By clicking yes, the patron can verify that his account is set up and working properly. The list-generation module is also invoked monthly to generate lists for all Book Alert members.

The list-generation module is another ColdFusion script that pulls data from the Microsoft Access tables, analyzes it, and develops a set of results. These results are then e-mailed to the address that the patron specified.

As with the conversion of Dewey numbers to subjects, the CFLOOP tag is used for this script. The users table is queried for a set of users, and the results set is then looped through another script. The second script analyzes the user preferences, comparing them to the items in the picklist, while writing matches into a results array. The following piece of code writes matches between the picklist and user preferences to the results array:

[less than]CFLOOP query="getbooks"[greater than]

[less than]CFIF #subject# EQ #pref1#[greater than]

[less than]CFSET choicearray[loopcount] = #title#[greater than]

[less than]CFSET callnumarray[loopcount] = #callnum#[greater than]

[less than]CFSET subjectarray[loopcount] = #subject#[greater than]

[less than]CFSET #loopcount# = #loopcount# + 1[greater than]

[less than]/CFIF[greater than]

[less than]/CFLOOP[greater than]

Each item's subject (#subject#) is compared to the user preference (#pref1#). If they are equal (a match!), then the title, call number, and subject are written into individual arrays. The loop counter is then increased by one, to account for the addition of an item to the array. Each item must be compared to all 10 user preferences for a match.

Once all the items have been examined for matches to user preferences, the array of user picks is complete. In order to format this data to make it presentable in e-mail format, the data must be converted from the array into a list format. This task is accomplished by using the ColdFusion list functions. We wanted each item in the e-mail to have the following format:

Watercolor basics. Painting snow and water / Jack Reid. (Arts)


In order to accomplish this, we needed to create a list format with the title, subject, and call number, with spaces, carriage returns, and parentheses at various points.

The following ColdFusion code dumps the arrays into a list format, which we can then modify to achieve our goals:

[less than]CFSET ContentList = ListAppend(ContentList, choicearray[counter],"$")[greater than]

[less than]CFSET ContentList = ListAppend(ContentList, "#chr{32)#","#chr(32)#")[greater than]

[less than]CFSET ContentList = ListAppend(ContentList, subjectarraylcounter],"(")[greater than]

[less than]CFSET ContentList = ListAppend(ContentList, "#chr(32)#",")")[greater than]

[less than]CFSET ContentList = ListAppend(ContentList, callnumarray[counter],"+")[greater than]

After this snippet of code, the items look like this:

$Watercolar basics. Painting snow and water / Jack Reid. (Arts)+


The $ and + symbols are used to denote where single or double carriage returns are needed. The $ is replaced with two carriage returns, and the + symbols are replaced with a single carriage return followed by a tab using ColdFusion's ListChangeDelims feature. The list of items is now properly formatted and needs to be e-mailed to the patron. The patron is e-mailed his picks using the following ColdFusion code:

[less than]CFMAIL to="#user_email#"

from="CCPL Book Alert"

subject="CCPL Book Alert Picks for #user_first# #user_last#"

server="mailserver"[greater than]

Dear #user_first#,

Here are your Book Alert picks for this month!


[less than]/CFMAIL[greater than]

Managing the Data Flow Automatically

In order to streamline the monthly process of sending Book Alert picks to users, we also wrote a management module. The management module consists of a Web page with links to various scripts, which perform the operations needed to send out the data. While some tasks, such as FTP-ing data and importing into Microsoft Access have not been automated, others such as clearing old picklist tables, merging new data, and generating lists have been. The entire process of generating picks for users now takes around 15 minutes to complete. It would be fairly easy for us to streamline the process even more, automating tasks like FTP and importing records into the database.

Launching a New Service: Promote It!

After we tested the code, we unveiled the new Book Alert service to the public ( in May 2000. We started by e-mailing the members of our e-newsletter, and explaining the new service to them. Our first signup came from that group, only a few minutes after the notice went out. The first 30 days were reserved for new signups, and at the end of that time we generated our first lists to patrons with no problems.

Any new service needs to be promoted. We put information about Book Alert in several key places on our Web site, and we have created bookmarks and fliers that patrons can pick up at our service desks. After an initial flurry of signups, the pace has slowed some, but the service continues to grow weekly. We currently have over 200 Book Alert members.

Goals for the Future, What We've Learned

We envisioned our current Book Alert service to be only the beginning of a new wave of e-services. In the near future, we hope to add audiovisual items to the service. Audiovisual items are in high demand, and our new arrivals lists for that area are some of the most-often-viewed pages on our Web site. Another goal is to streamline the process so that patrons can view an item's status, and put an item on hold if it is already checked out. We hope to be able to embed links directly into the e-mail notifications, so that patrons are only one click away from viewing an item's status.

Patrons have suggested adding other areas of interest, such as romance titles. Because CCPL catalogs all romance titles under general fiction, there is no easy way to find these in our new arrivals query. The general fiction category in Book Alert comprises all fiction at CCPL (except Mysteries, Science Fiction, and Westerns), so this can be a large and daunting category for patrons to choose. A future goal might be to find a way to break the fiction category up into a more manageable size. Patrons have also suggested that it might be nice to be able to add a Dewey catalog number or range of numbers to be notified about, which would allow them to create a much more specific profile than is currently available with our preset categories.

The Book Alert project has been an interesting learning experience. We've learned that you can develop new, unique e-services for patrons with software tools that are readily available. It's easy to do, and it's a great way to better serve our patrons.

Peter Konshak is the computer technology services manager for the Carmel clay Public Library in Carmel, Indiana. He manages the library's automation system and networks, and designs custom intranet and Internet applications. He holds a B.S. from the University of Wisconsin--Madison and an M.L.I.S. from the University of Texas--Austin.
COPYRIGHT 2001 Information Today, Inc.
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2001 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:Technology Information
Author:Konshak, Peter
Publication:Computers in Libraries
Date:Feb 1, 2001
Previous Article:SilverPlatter Beefs Up Database Collection.
Next Article:Unpacking the Trunk Customization and MyLibrary@NCState.

Terms of use | Privacy policy | Copyright © 2020 Farlex, Inc. | Feedback | For webmasters