Introduction
Working at The Washingtonian presents many interesting challenges. Some of our articles, such as Best & Worst and Top Doctors, use surveys. I've been writing software so we can enter the survey data inhouse. When the most recent survey was produced, I decided to write a generic survey database that would not require any reprogramming to set it up for use with a new survey.
Washingtonian Survey Requirements
The requirements for survey software came down to the following:
The solution was to create a relational database. This database was written in 4th Dimension v6. A relational database was used because the number of questions per survey varies.
Setting Up the Survey Database
The first step a user takes in setting up the database for a new survey is to make a copy of the survey database structure file, rename it, and place it in its own folder. When this file is opened for the first time, it will create a new data file. 4th Dimension, unlike FileMaker Pro, uses a single structure file and data file for its databases. This makes it very easy to create a copy of a database program without its data.
Figure 1 -- Opening the Questions Form
After opening the file, the user enters the survey questions. Select Go To Area from the File menu and then double-click on "Questions" in the Go To Area dialog (see Figure 1). An question browser will appear. The user presses command-N to enter a new question. A number may be up to 5 characters and made up of both numbers and letters. A question may be up to 32,000 characters. If a predefined list is used, it is also entered. The user then saves the question and continues entering new ones.
Figure 2 -- Questions Browser Showing Survey Questions
If any predefined lists are used in the survey, they should be entered now. Entry is similar to question entry. Each record holds the name of the list and the individual entry. See Figure 3. In the Top Psychotherapist survey, there is only one list. Lists are used for any question that has a limited number of answers.
Figure 3 -- List Records
The database is now set up for the entry of completed surveys. The steps above took much less time than programming a new database with individual fields for each questions. No technical prowess needed. Future version of the generic survey database will provide tools to ensure that only defined lists are entered in the questions area and there will be room for more information about the survey, such as survey name and who requested the survey.
Entering a Completed Survey Form
After opening the database, the data entry operator selects Add New Records from the Selection menu or presses command-N. A new survey record is created with a row for each question. See Figure 4. The user can select a field in the table for data entry with either the keyboard or the mouse.
Figure 4 -- New Completed Survey Data Entry Form
Because the first question, "Your profession" has a list attached to it, when the cursor enters its response field, a list of options will pop up. See Figure 5. The user can now select from the list using either the keyboard keys or the mouse.
Figure 5 -- The Profession List
If a survey contains more than one answer to a question, the user
can either click on the Add Row button 
or press command-tab. This will create a blank row directly below the
current row. The user now enters a question’s number. If he or
she needs to review the questions he or she can enter an invalid
number for a list of all questions. See Figure 6. The question can be
selected with the keyboard or the mouse. The user can change the
question for any row by replacing the question’s number.
Figure 6 -- Pick List of Questions
The user can sort the table by any of its three fields by clicking on the table header. In the Top Psychotherapist, survey, most of the answers are the names of healthcare professionals, and many people enter the same name for multiple questions. The data entry person can click on the Response heading after entering a single survey to see if a person's name was entered in a consistent manner. When a survey response has been completed, the user either presses on the Accept button or presses the Enter key. The data is saved and a new data entry form appears on the screen. When a data entry session is completed, the user presses on the Cancel button or presses command-period keys.
When a survey record is saved, only rows with responses are saved to disk. When a survey is reopened, only answered questions are shown. This approach saves time and disk space.
Figure 7 -- Completed Survey Response
Under the Hood
Figure
8 -- Database Tables
For every completed survey, we create a single record in the Survey table and a record in the Responses table for every question. The Survey table holds a minimal amount of data: an ID field and a Date_Entered field. The ID field is the table's key field. It is an integer generated by the database. It is unique; no two Survey records will have the same ID number. The ID number is written on the paper survey so we can refer to it if necessary, such as to check data entry. The date the record was entered is automatically recorded by the database. Other fields could have been added to this table, such as the name of the data entry person. All fields for the Survey table should be standard ones used on every survey.
The Responses table has a many-to-one relationship with the Survey table. For every survey record, there can be an unlimited number of responses, and every response record is linked to a single Survey record. The ID field is the link to the corresponding Survey record. The Number field is a 5-character string that holds the survey question number. The Response field holds the answer to the question. The response may take up to 80 characters. The reason for an 80-character limit is that our surveys usually ask for short answers, such as a person's name.
The Questions table holds the survey questions. It is related to the Responses table through a many-to-one relationship: There are many Response records related to each Questions record. The tables are related through their respective Number fields. The Questions tables holds each question in a text field. If a question is supposed to be answered from a list of options, the name of a list is entered in the List field.
The Lists table holds the options for each list. In the Top Psychotherapists survey, one question has a list associated with it. The purpose of a list is to speed up data entry and minimize errors.
The database is written in 4th Dimension v6, a relational database management system. Unlike FileMaker Pro, the most popular Macintosh database, 4th Dimension is intended for database developers and not end users. This survey database is simple but requires programming. 4th Dimension uses a Pascal-like programming language.
It was important to use a programmable database to make the survey database as generic as possible.
AreaList Pro v6.1, a 4th Dimension plug-in, is used on the survey data entry form. This plug-in provides an improved interface for data entry. Various features, such as easy user sorting and automatic actions upon leaving and entering a cell, are controlled via AreaList Pro.
While we are using uncompiled versions of the database, I do run the code through 4D Compiler as a method of detecting syntax errors. A compiled database runs faster than an uncompiled one, but requires either a full copy of 4th Dimension or 4D Server, or it must be compiled with 4D Engine to work with 4D Runtime. The last option is too expensive for our office and is intended for developers who want to distribute their databases as applications. Users are running the database with 4D Runtime, which can be freely distributed but does not support compiled databases.
All that is required to turn the survey database into a multiuser database is the purchase of 4D Server, which is a 4th Dimension database server. It has the additional advantage of allowing us to use a compiled version of the database.
Further Development
A number of enhancements are planned for the survey database. The most important is report generation. Currently, the user has access to 4th Dimension's built-in report writer. One export routine has been written to export the data as a tagged text file for QuarkXPress.
There is a need for a table for general survey information, such as survey name, when the survey was mailed, and who requested the survey. These will be easy to add. I will add an import routine so surveys filled out on a web-based form can be imported into the database.
While the survey database assumes that all data is text, it is possible to expand the database so a user can specify that a question requires a text or a date or a numeric answer. The Questions table can have a field that specifies the type of date -- text, date, or number -- and programming can mandate that a question not violate the data type during data entry. Custom reports will have to be programmed to tabulate numeric data.
Summary
Writing a generic survey database was surprisingly simple. It took less time than creating a database dedicated for a specific survey and solved the problem of the programmer not knowing how many answers any particular question might have on a single survey. While this database was written in 4th Dimension, the principles behind it should be transferable to other Macintosh databases such as Panorama, FileMaker Pro, and Omnis.
Paul Chernoff spends his days writing databases at The Washingtonian when not busy helping people with QuarkXPress, fixing network problems, working on the Internet, or taking care of the servers. He is lucky to be doing most of his work on a Mac. At home, he balances time between Mac and family. He can be reached at paul.chernoff@tcs.wap.org.
Revised Saturday, August 12, 1998 Lawrence I.
Charters
Washington Apple Pi
URL: http://www.wap.org/journal/