Real Life Adventures with askSam

by Christine McGuire Gregg

Reprinted with Permission

_______

     I work for a county library and was given the task of making a video catalog of some 6,000 videos. The information for each video was to include genre (drama, comedy, etc.), a brief plot summary, major stars, run time, and other descriptive  features. The films were to be sorted by genre and then alphabetically by title within a given category. In addition to the printed catalog, we also wished to be able to produce various lists such as all John Wayne movies, or all movies which had public performance rights. All these are rather simple tasks for any good  database program. However, there was a major problem. There was no pre-existing database of these films and I fervently wished to avoid having to retype all this material into a database.  

     I began by taking an inventory of what video lists were available in either electronic or printed form. The results were not pretty. There was a yearly list for each of five years done on a MAC®, numerous discs for another 5 years done with MS-DOS® WordStar®, about three years of printed lists, and a master index of just film titles and film numbers also done on aMAC®. Needless to say, the formats of all these lists, i.e. the order and appearance of theinformation about the videos was also was not identical from list to list. Further, many of the films on the lists had since been discarded over the years and their numbers reassigned. A final complication was that I use Windows 95® and the video librarian who would be using and updating the database once it was created had a MAC®. This meant that once the database was finished, it would have to be exported in some format which could be used by a MAC® database program.  

     I chose askSam for this daunting task because I knew of no other database which could import different kinds of text as a structured or unstructured database. Another vital attribute was AskSam’s ability to export database information in various other formats. I needed the latter capability for two reasons. I intended to use either Microsoft Publisher® or Word for Windows® 6.0 to produce the camera ready copy for the catalog. AskSam can import and export Word for Windows® 6.0 documents. Word for Windows® 6.0, in turn, can import and export to Microsoft Publisher®. Second, AskSam’s ability to export comma separated values would make it easy to get the final database into a MAC® database such as Claris’ FileMaker Pro®.

     I began by using DataViz’s Conversions Plus® to convert the MAC® Microsoft Works 2.0® documents to Microsoft Word for Windows® format. Next I imported the MS-DOS® WordStar® documents in Word for Windows®. The last step was to scan the printed lists into a Word for Windows® document. Why all the documents were put in Word® format will shortly become clear.

     Because the information was arranged differently in each of these three major types of lists and because the final database I would export to the MAC® would have to be structured, I decided to add fields before importing the data into askSam. Once all the available data were imported into Word for Windows® its powerful  “search and replace” function could be used to find certain consistent format characteristics in each entry and then insert the desired fields. AskSam’s word processor was not suitable for this task because it can’t search for spaces and control characters which was a critical capability.  

     Below is a sample entry for one video. The others were just variations on a theme with the same information presented in different formats.  AIR BUD / Walt Disney 98 minutes, color, PG, 1997 E:V01767 Josh Framm is a 12-year-old boy who is too shy to try out for the school basketball team until he meets Buddy, a runaway golden retriever who surprises him with his ability to score baskets. Starring Michael Jeter and Kevin Zegers.

     The task was to insert the following field names: TITLE[, STUDIO[, RUNTIME[, SPECS[, FILM#[, PLOT[, STARS[, and NOTES[ in the appropriate places. Each differently formatted list required dreaming up new search algorithms to accomplish the field insertions. For instance, in the example above there were always two carriage returns (^p) before each new title. Thus, the TITLE[ field was added by searching for “^p^p” and replacing it with

“^p^pTITLE[ ”. Selecting “replace all” makes the replacement for all entries. Searching

for “/” and replacing it with  “]^pSTUDIO[ “ adds the ending bracket to the title field, starts a new line, and  inserts the STUDIO field. To add the ending bracket to the studio field and insert the run time field, I searched for ”^p^#^# minutes” and replaced it with “]^p RUNTIME[  ^&” (where ^& is the expression searched for) and so on.  Although this took a lot of time and thought, the exercise was well worth doing because it made the import virtually trouble free.

     Once each of the lists had all the fields inserted, each file was imported into

askSam and appended until all had been joined into one large file. The whole process worked like a charm! The next task was to remove all films which had been purged from the collection over the years and check that the film numbers were accurate. To do this I sorted the master list and produced two printouts: one of all the film titles and another sorted in numerical order. These the video librarian checked against her master lists of current  film titles and film numbers. With her list of corrections I was able to search for discarded films in the database and delete them. New lists were produced and checked until the database was completely updated.  

     The next step was to sort the file and format an askSam report which contained all the information desired in the printed catalog complete with formatting (title in bold italics, etc.). The report writer made this very easy to do. Once the report was perfectly formatted, the entire formatted file of 6000 videos was exported to Word for Windows. The transfer worked flawlessly. The Word file was then imported into Microsoft Publisher where the final formatting was completed. Page size was adjusted for the newsprint format we would use,  columns, headers, and various graphic ornamentations were added to produce the camera ready copy.

      The last step of the project was to get the database into a format our MAC® loving librarian could maintain. AskSam made this easy too. The entire database was exported as a comma separated values (*.CSV) file. DataViz’s Conversions Plus® program was used to convert the CSV file to MAC CSV format. The latter was then imported into Claris’FileMaker Pro®. The whole process worked beautifully and the whole project was a grand success — thanks to askSam.