Nancy Hensold
Tropical Collections Specialist
Department of Botany
The Field Museum
Roosevelt Rd. at Lakeshore Drive
Chicago, IL 606052596

Contact: hensold@fmnh.org

CONTENTS

Introduction

A. Database Design

A-1. General Considerations

A-2. The Field Museum’s Northern Peru Database - Explanation of Field Structure

B. Data Entry Protocol

C. Viewing dBase Files

D. Indexes and Information Retrieval

D-1. Creating an index from the menu

D-2. Using indexes

D-2.1. Setting a database to indexed order

D-2.2. Searching indexed files

D-2.3. dBase settings which affect the SEEK command

D-2.4. Notes on editing indexed files

D-3. Locating records without an index

D-3.1. From the Menu: “Search”

D-3.2. From the Command Line: “Locate”

E. Updating Database Files

E-1. Adding Determinations

E-2. Global editting and the REPLACE command

E-3. Proofreading

E-4. Database structure changes

E-4.1. Suggestions for splitting one field into two

F. Simple Statistics

F-1. COUNTing records which meet a condition

F-2. Using the command line for computations

F-3. The CALCULATE command

G. Creating Reports

G-1. The LIST command

G-2. The Report Generator (dBase IV)

G-2.1. Designing a Report

G-2.1.1. Basic procedure

G-2.1.2. Notes on arranging field templates

G-2.1.3. Using Group Bands
G-2.2. Printing from an existing Report Form

G-3. Exporting ASCII text files to WordPerfect

G-3.1. Exporting data to WordPerfect Merge files (WP 5.1)

G-3.2. Exporting data to WordPerfect Data files (WP 6.0)

G-3.2.1. Importing databases directly (custom installation required)

G-3.2.2. Importing ASCII delimited text files

G-3.3. Primary Merge (Form) files

G-3.3.1. Designing a Primary Merge file (WP 5.1)

G-3.3.2. Designing a Form file (WP 6.0)

H. Specialized Reports

H-1. Specimen Labels

H-1.1. A dBase Labels Program for Data conversion

H-1.2. WordPerfect Labels Format

H-1.2.1. How Labels Format works

H-1.2.2. Creating a Label Format file

H-2. Annotation Labels

H-3. Determination Lists

H-4. Project Checklists

Acknowledgments

Literature Cited

Appendix 1. Using dBase Commands
1a. Scope & Condition Statements
1b. Index of Selected dBase Commands
Appendix 2. A Data Conversion Program for Specimen Labels
2a. LABELS.PRG
2b. Labels Program Auxiliary File Structures

FIGURES:
Figure 1. DETBASE File structure and Sample Record

Figure 2. Primary Merge (Form) Files - Examples
Fig. 2a. Specimen Label
Fig. 2b. Annotation Label

These database structures and protocols evolved out of our own needs in handling incoming floristic project material. At no point was any attempt made to design an optimum herbarium computerization “system.” What this manual attempts to do is (1) to allow a beginner with limited resources to “hit the ground running,” realizing at the outset what options are available, and which design decisions are most critical; and (2) to reduce the amount of time spent crawling through software manuals. The best system, for any particular herbarium or research group depends on many local factors, including the volume of material being processed, the frequency with which various operations will be needed, and the skill level, turnover rate, and number of personnel involved with data entry and database maintenance.

In our case, I was the principal data entry person, and the limitation of our work to floristic project material limited the number of collector names, localities, etc., to be mastered. Therefore, we had little concern with spelling inconsistencies. In most cases and in the long term, the use of linked authority files for taxon, collector, and author names is desirable to reduce error and to save time in data entry. I have not yet tackled this problem in dBase but would be happy to hear from someone who has. I also appreciate any other helpful input on streamlining or refining any of the procedures described herein.

INTRODUCTION

Specimen databases are an invaluable tool for taxonomists, which have gained wide acceptance and accessibility in recent years. Databases not only facilitate research and information-sharing, but can greatly increase the efficiency of standard herbarium operations such as the generation of specimen labels, annotation labels, and determination lists. At the Field Museum, we have been databasing our incoming Peruvian material for several years using an IBM PC, and dBase® (Version IV for DOS or V for WINDOWS) database software in tandem with WordPerfect® (Version 5.1 for DOS or 6 for WINDOWS). This database, called “DETBASE,” serves the following functions:

1. Quick querying of northern Peruvian specimen data.
2. Generation of accession lists.
3. Generation of herbarium specimen labels (in conjunction with WordPerfect).
4. Generation of annotation labels.
5. Efficient generation of determination lists to send to the collectors, and (unlike the chit method) an easy way of sending multiple determination updates.
6. Tracking of duplicates.
7. Tracking special project collections.
8. Generating species checklists, for special project areas, political regions, or elevational ranges.
9. Generating exsiccatae lists and indexes to exsiccatae, for taxonomic treatments.
10. Calculating simple statistics.

This database is a simple “flat file” with a field structure comparable to that of other large mainframe databases. Field structure compatibility allows data-sharing with other institutions via “delimited” ASCII text files, regardless of the software in use.

A PC-based system offers great flexibility and accessibility to the individual investigator. However, it does require familiarity with software capabilities and command language, which can be tedious for a layman to acquire from documentation geared to commercial use. Therefore a manual of practical suggestions and protocols for using dBase in herbaria is presented here, with examples from our database, DETBASE.

These structures and protocols evolved out of our own needs in handling floristic project material, and not through an attempt to design an optimum herbarium computerization “system.” What this manual attempts to do is to allow a computer amateur to begin working with a database program, realizing at the outset what options are available, and which design decisions are critical.

This manual assumes a minimal familiarity with dBase, and the use of the command line, or “dot prompt”. Readers familiar with only the “Menu System” may find Appendix 1 helpful. Keystroke shortcuts referred to in the text apply to dBase IV for DOS.

A. DATABASE DESIGN

A-1. General considerations

The ideal specimen database is designed to allow: 1) ease of sorting specimen records into desirable categories or sequences; 2) efficient search, retrieval, and calculation of desired information; 3) efficient proofreading, editting, and updating; 4) data sharing with other institutions and individuals; and 5) efficient use of disk space.

As a general rule, it is best to parse the data into the smallest practical units. It is easier to combine data fields later than to separate them. The more data fields are recognized, the more ways it is possible to sort and index the specimens, and the easier it is to perform searches and queries. It is also easier to proofread and edit data for consistency when the fields are small.

Data entered in fields which are likely to be used for sorting or indexing the database should be monitored and held to a uniform format and spelling. For example, since you may frequently want to order database records or create reports which are sorted by the first collector’s name and number, it is important that the spelling of first collectors’ names be standardized. If the spelling is consistent, it can easily be modified later, if necessary, with a global replace command.

It is also best, when disk space is limited, to use standard abbreviations where practical, and to avoid repetitive words or phrases, such as “Depto.” or “Elevacion.” Simple protocols can be used to convert abbreviations or add repetitive verbiage when desired, either to the database directly or to a printed report. (For examples see Sections E-2 and H-1.)

In most database programs, the amount of space used is a direct function of the number of records times the sum of the maximum field lengths. Fields which are filled with information use as much space as empty fields. Therefore it is important not to make fields longer than necessary, and it is usually practical not to use fields which will only rarely contain information, such as a field for “Chromosome number.” Such information can be kept in a miscellaneous “notes” field if necessary.

The dBase software recognizes several field “types,” including Numeric, Character, Logical, and Date fields. A field is best designated as numeric when: a) only numbers will be entered in the field, and b) it is desirable to sort, index or perform calculations on the field. Numeric fields automatically right-justify their contents, whereas character fields left-justify and therefore do not sort numbers in true numeric order.

It is possible to reduce space usage by using several linked databases. For example, a list of unique taxon names can be stored in one database, and linked to specimen records kept in another database, through a unique reference number. This is a common practice for large databases, and it is possible to do so using dBase. However, it may not be necessary in a smaller database, which can be converted from a “flat file” to a relational design later, if necessary.

A-2. The Field Museum’s Northern Peru Database

Our Northern Peruvian vascular plant collections are entered as they arrive in a dBase IV (or V) file called DETBASE. The file structure of DETBASE, and a sample record is displayed in Figure 1. An explanation of the field contents follows.

1. CLASE - This field allows sorting of the major groups of taxa (PTEridophytes, GYMnosperms, MONocotyledons, DICotyledons) for floristic checklists. We currently use three-letter abbreviations. However, it may be more practical to use abbreviations (or numbers) which will sort the major groups in the order in which they normally appear in checklists.

2. FAMILIA - Use 3-letter family abbreviations as in Weber (1982).

3. GENERO - Genus name

4., 7. ESPECIES, SUBESPECIE - Specific and infraspecific epithets, respectively.

5., 8. AUTOR, AUTOR2 - Author(s) (including basionym author in parentheses) of species, and subspecific taxon, respectively. If possible, use standard abbreviations, as in Brummitt & Powell, 1992.

6. VAR_SSP - Rank of subspecific taxon (f., ssp., or var.)

9. AFF_CF - This field is for abbreviated comments, or “qualifiers” on the determination. It is usually not important that these follow a rigid spelling or format, but likely entries to the field include the following:
cf. [”comparable to”]
aff. [affinis, “related to”]
vel aff. [or a close relative]
s.s. [sensu stricto, in the narrow sense]
s.l. [sensu lato, in the broad sense]
hybrid
ex (or e) descr [identification based on a published description only; other specimens were not available for comparison.]
* - We use an asterisk in this field to indicate that more extensive comments by a specialist on the determination, such as subgenus name, or most closely related taxon, etc. can be found in the NOTAS field.

10. DEPTO [1st order political division; most users will want a COUNTRY field also, I presume]
Peruvian departmental abbreviations used in Cat. Fl. Peru (Brako & Zarucchi, 1993).

11. SITIO - This field may be used for project code numbers or letters, to be determined by the individual user. It allows the collection data associated with any particular project to be quickly accessed for use in reports, counts, checklists, and statistics.

12. PROV - Province name [or 2nd order political division]

13. LOCAL - All other locality information.

14. HABITAT - Habitat information, associated species.

15. OBSERV - Information pertaining to individual plant habit, ecological information, etc.

16. COLECTOR - First collector's name.

17. COL_AD - Names of all other additional collectors, separated by commas.

18. NUMERO - Collection number. This field accepts numeric data only.

19. SUFFIX - This field used for suffixes, such as "a" and "b", or “-93" on the collection number. (You may find that a “prefix” field is also necessary; simply add it to the database structure as needed.)

20. HERBARIO - Acronym for herbarium where specimen resides. May also include herbaria in which duplicates are known to reside, separated by commas.

21. DUPL - This field can be used: a) to sort specimen labels for printing, so that labels requiring the same number of copies are printed on the same sheet, and paper is saved; b) to indicate how many unmounted duplicates are available for distribution; and c) to sort annotation labels for printing, if desired.
As yet, we have no program for automatically printing the desired number of duplicates.

22-24. DIA, MES, ANO - Date information. NOTE: Many databases have special date fields. We have not adopted this here, since it makes printing labels more complicated. We abbreviate the month with letters and not numbers to avoid confusion between the month and the date fields.

25-26. ELEVACION, ELEV2 - These fields will accept 4 digits of numeric data, and assume measurement in meters. If only one elevation is given, enter it in the ELEVACION field. If an elevational range is cited, as 2800-3200 m, enter the lower number in ELEVACION and the higher in ELEV2.

26-27. CORDLAT, CORDLON - These fields will only accept 4 digits of numeric data. Our database assumes latitude S, and longitude W. If necessary, add single-character fields after CORDLAT and CORDLON to differentiate North-South and East-West.

28. NOTAS - Any additional notes of any kind.

29. DETERMIN - Name of the determiner followed by a comma and the date.

30. ENVIOS - Indicates whether the most recent determination of the record has been sent to the collector, or to whomever determination lists are being sent. See “Determination Lists”. We use “NO” and “YES.” Normally, “NO” will be entered in the ENVIOS field any time a new determination is entered.

31. ANNOT - Indicates whether annotation labels are ready to print, or have already been printed. We use “A” and “D,” respectively, for “annotated” and “done.” Normally, “A” is entered in the ANNOT field when a determination is entered. Exceptions might be when the determination is uncertain or incomplete, or when there are no unmounted duplicates which require annotation.

B. DATA ENTRY PROTOCOL

1. In general, it is best to enter incoming specimen data into a new file which can be proofread and printed, and later appended to the main data file. To create an empty file with an identical structure to the main data file (e.g., “DETBASE”), do the following:

.use detbase
.copy structure to <newfile>
.use <newfile>
.append

2. Choose one of the following commands to allow automatic copying of repetitive data. Note that when field values change, you need only type over or delete any undesired data which has been carried over.

SET CARRY ON/OFF This command will cause the entire contents of the last record to be automatically entered in the new appended record below it, which can then be editted.

SET CARRY TO <fieldlist> This command copies the contents of specified fields only.

Example (recommended for specimen data entry):
.set carry to depto,prov,local,habitat,colector,col_ad,herbario,dia,mes,ano,elevacion

Shift-F8 Pressing these 2 keys together will cause data entered in the field above the cursor location to be duplicated in the field below. It will only copy one field at a time. If the active field already contains data, it will be erased and overwritten. This key is also handy for making quick corrections when proofreading. (NOT available in dBase V for Windows).

3. WARNING: a. Do NOT use double quotation marks (“) in the database (as when quoting common names). Double quotes are used as field delimiters in ASCII files, and can cause unpredictable field breakage if you attempt to transfer your file to a wordprocessor. You may substitute an apostrophe (‘) instead. This is particularly critical because (“)’s can NOT be removed by a search and replace routine once in place. They must be deleted manually. b. Do NOT enter commas between numerical characters, as in “3,000" or “1,6". This can also “break” fields. Commas used within normal character text do not seem to cause a problem. [These problems may have been solved by WP 6.0; I haven’t tested it out. Even in WP 5.1 they occur unpredictably so exercise caution.]

4. After data entry of a group of specimens is complete, you may want to proofread, edit, or sort the file, print a hard copy of selected fields (see Creating Reports), or make other changes. When this is complete, you may append the temporary file to the main database, using the following commands.

.use detbase
.append from <newfile>

Then, you may:

.erase <newfile.dbf> which will completely erase the temporary file
OR
.use <newfile> which will remove all records from the file, but will preserve the .zap structure for future data entry.

C. VIEWING dBASE FILES

D. INDEXES AND INFORMATION RETRIEVAL

The great utility of databases is due to their capacity to be sorted quickly many different ways, and editted, printed, copied, or exported in any order.

The principal tool for sorting a database is the index. The index is, like the index of a book, an auxiliary file associated with the database which contains information for rapidly sorting the file in a particular order determined by the user. With an index in use, the database file may be editted, printed (as a list or report), copied or exported, and all these operations will be performed following the current index order. Multiple indexes may be defined for any given database, and index specifications are named with a “tag” by which they can be called at any time. Indexes automatically update themselves as the file is editted, and are saved when the program is exitted. Searching for particular records in a large database, or on a slow computer, can be time-consuming, but in an indexed database, searches (using the SEEK command) on the indexed field(s) are very fast.

Creating indexes can be done either through the menu or from the dot prompt (command line).

D-1. Creating an index from the menu
1. From EDIT or BROWSE mode, select ORGANIZE menu.
2. Select CREATE NEW INDEX.
3. On each line of the submenu that appears, you must press ENTER first, before entering your selection.
4. INDEX NAME (or “tag”): This is the name used to call the index from the command line, so make it easy to remember. For a simple index, you may give it the same name as the index field.
5. After all selections are made, press CTRL-END.
6. The database will appear in the new indexed order, until you close it or change the index order.

Examples:
NOTE: Unless specified otherwise below, assume default values for: FOR clause => [none], the Order of index => [Ascending]; Display first duplicate key only => [NO].

Records sorted by collection number:
Index name NUMERO (or any desired name)
Index expression NUMERO

Records sorted by collector and number.
NOTE: In this example, you must use STR() to convert NUMERO to Character data; if you don’t you’ll get the error message “Data type mismatch.”
Index name COLECCION
Index expression COLECTOR+STR(NUMERO)

Sagástegui collections only, sorted by number
Index name SAGAST
Index expression NUMERO
FOR clause COLECTOR=”Sagástegui”

Records sorted by family, genus, and species.
Index name TAXON
Index expression FAMILIA+GENERO+ESPECIES

A UNIQUE list of all the species found in the database, sorted by family and species, with each species listed only once.
Index name UNITAXON
Index expression FAMILIA+GENERO+ESPECIES
Display first duplicate key only YES

D-2. Using indexes
Once created, all indexes for a given database <FILENAME>.DBF are automatically stored in a single file called <FILENAME>.MDX. This file is automatically updated each time database records are changed or added.

D-2.1. Setting a database to indexed order.
From the MENU:
1. In BROWSE or EDIT mode, go to the ORGANIZE menu, and select Order records by index. NOTE: Once an index is created, reordering the database by the index is extremely fast.
2. Select the index name from the popup menu. Selecting Natural Order will return the database to the original, nonindexed order.

From the dot prompt:
.use <filename> order <name of index>
e.g., .use detbase order taxon

OR, if file is already in use:
.set order to taxon
To return database to nonindexed order:
.set order to

D-2.2. Searching indexed files
Indexed files can be used to perform very rapid searches on an indexed field. To perform a search on an indexed field, use the seek command from the dot prompt. The seek command only searches in indexed fields, in the order in which they appear in the index expression.

Examples:
To locate the first record with collection number 8347 in DETBASE
.use detbase order numero
.seek 8347 [NOTE: Data in numeric fields must not be enclosed in quotes]
.edit

To locate the first record of Lamiaceae in the TAXON index of DETBASE
.use detbase order taxon
.seek “LAM” [NOTE: Data in character fields must be enclosed in quotes]
.edit

To locate the first record of Salvia in the TAXON index
.use detbase order taxon
.seek “LAMSalvia”
.edit

D-2.3. dBase settings which affect the SEEK command

SET EXACT ON/OFF This setting determines how dBase executes searches and processes “condition” statements. With EXACT ON, the contents of the field must exactly match the string given in the search or condition statement. With EXACT OFF, additional characters may appear in the field beyond the character string matched. For example, with EXACT ON, a search for “Leiva” in the collector field, will not find any records of “Leiva, S.” With EXACT OFF, a search for “Leiva” will turn up records of “Leiva”, “Leiva, S.” and “Leiva, P.” without discrimination.

SET NEAR ON/OFF This setting determines how dBase responds if it does not locate any records matching the specified string. With NEAR ON, the record pointer will be set to the record with the value closest to value specified. With NEAR OFF, the record pointer will go to the end of the file, and the message “Value not found” will appear.

D-2.4. Notes on editing indexed files
Virtually any operation can be performed on files while in an index order, but a few precautionary notes are in order.
1. If data are changed in the indexed field, the record may move to a new position as soon as you leave it, and the screen will follow the record to its new position. If you do not want the record pointer to follow the editted record, go to the Records menu, and toggle Follow record to new position to no.
2. Global file operations, i.e., where dBase has to search through the whole file for certain records, run much more quickly on files in natural order, than on files where an index is in use. Before performing such operations, return the database to natural order. Examples of such operations are: SEARCHes on non-indexed fields, SORTs, COUNTs, and the REPLACE command (if global).
3. Delete index tags you do not need. This can be done through the Organize Menu. Indexes require space, and also time, since they are constantly updated as the file is changed. Also every time you change file structure, or “pack” the database, the indexes are automatically regenerated from nothing, and this requires time.
4. Do not attempt to modify the database structure while an index is in use.
5. Very rarely, you may have problems with “losing” the bottom half of the database as you browse through it. The records show in the status line but not on the screen. If this happens, go to the Organize Menu and give the command to Reindex. This usually solves the problem.
6. If you copy an indexed database file to another disk, but do not copy the index (MDX) file, you will get an error message the first time you use the copied file. Production.MDX file not found. You may select Proceed which is followed by the message Production index file reference deleted.

D-3. Locating records without an index
The quickest way to locate a particular record is through the use of an Index, as described above. However you may also need to search for a record on a field which is not indexed. SEARCHing in a non-indexed field is much slower than SEEKing in an indexed field. The advantage is that searches can locate character strings located anywhere within a field, whereas SEEK can only find character strings at the beginning of fields.
You may perform searches on non-indexed fields from the Menu or from the command line (dot prompt). Note that the command line allows more complex searches.
D-3.1. From the Menu: “Search”
Place the cursor in the field you want to search. Use the Search option on the Go To Menu. If the character string you are looking for is NOT expected to be at the beginning of the field, use the wildcard character * on either side of the character string.
For example, to locate records with the word “Cachil” embedded in the LOCAL field, search for *Cachil*.
D-3.2. From the Command Line: “Locate”
The command LOCATE FOR <condition> will start a search at the beginning of the file and locate the first record to meet the condition. The search can be continued with the CONTINUE command. As with the SEEK command, the LOCATE command is affected by the “EXACT” and “NEAR” settings.

Examples:
To find a record with “Wurdack” as the first word in the DETERMIN field
.locate for determin=”Wurdack”

To find a record with “Cachil” anywhere in the LOCAL field.
.locate for “Cachil”$local

To find a record with collector Sanchez, from an elevation less than 3500 m, and the word “jalca” contained in the habitat field.
.locate for colector=”Sanchez”.and.elevacion<3500.and.”jalca”$habitat

To continue the search through the file for the character string specified by the last LOCATE command:
.continue (or cont)

E. UPDATING DATABASE FILES

F. SIMPLE STATISTICS

G. CREATING REPORTS

H. SPECIALIZED REPORTS

Although we have not yet done so ourselves, you may want to select CLASE abbreviations which will sort the major taxonomic groups in the order they normally appear in checklists.

In addition the standardized 3-letter family abbreviations do not sort families alphabetically according to their actual spelling. To remedy this, the desired records and fields can be copied to an intermediate file, where the abbreviations can be translated (See Section H-1.1, “Lines 15-16"), and the file subsequently indexed.

Acknowledgments

FIELD~13 FIELD~15FIELD~16
FIELD~14

PAGEOFF

Figure 2b
ANNOTATION LABELS
Primary Merge (Form) File - Sample
NOTE: Field numbers 8-10 represent COLECTOR,NUMERO,SUFFIX

[Font 12 cpi][Font small][Center]Herbario Antenor Orrego (HAO)
FIELD 8 FIELD 9 FIELD 10[Font small off]

FIELD 1 FIELD 2 FIELD 3 FIELD 4 FIELD 5 FIELD 6 FIELD 7

det. por FIELD 11
{PAGE OFF}

---

[Environments] [Plant Families] [Publications]
[J. Francis Macbride & Flora of Peru Series] [Project Staff & Collaborators]