How to plan your Access or FileMaker Pro database – Part 2

In part one of this series we said you should start planning your Access or FileMaker Pro database by using paper and pencils to draw the input forms and reports that your users will need.  Now we are going to talk about using those mockups to create a “Field List Worksheet.”

A “Field List Worksheet” can be done with paper and pencil, or you could do it in a spreadsheet program like Excel.  As the name implies, this worksheet contains a list of fields that will go into your database.  Each field will be on a separate row.  You should also have one column for each of the following field attributes.

• Table Name

Identifies the name of the table that will contain this field.

• Field Name

In an Access database it is best to use names without spaces.

• Caption

This property, in an Access database, controls how the field will appear in a form or report.  This is often the same as the field name except that it can have spaces.  If the field name and caption match, such as in a “notes” field, then you can leave the caption blank.

• Field Data Type

See the Access Data Types post for more details about the data types available in an Access database.  For a FileMaker Pro database your field data types could be Text, Number, Date, Continer, Time or TimeStamp.

• Field Size

For example if it is a number field you can specify “long” if the number is a long integer (a number that doesn’t allow decimal places), and a “double” if the number should allow decimal places.  For a text field you should list how many characters the field will allow.

• Description

This clarifies the purpose or use of the field.  In an Access database the field’s description will be shown in the status bar when the field’s value is being entered.  I often choose to phrase the description as a question.  For example for a quantity field you might I might enter “How many items were received?”

• Key Field

Enter a “P” in this column if the field is part of the primary key.  Enter a “F” in this column if the field is a foreign key.  See the post “Database Terms” for more information about Primary and Foreign keys.

• Default Value

Enter any default values you would like the computer to enter in “by default” when a new record is created.  For example if all your customers are in California you might enter “CA” as the default for a state field.

• Is Required

Put a “Yes” in this column if the field must be populated when creating a new record.

• Input Mask

This identifies a pattern for how the data is to be entered.  For example you might enter (999) 000-0000 for a phone number, or 00000-9999 for a zip code.  (The 9 means the digit is optional and the 0 means it is required).

• Format

Specifies how the field is formatted for reports and on-screen viewing.  For example you might enter “Standard 2” for number fields that should show commas and display two decimal places and “Percent 0” for numbers that will show a percent sign and zero decimal places.

• Comments / Notes

Enter any comments or notes you need to remember about this field.  For example, for a credit limit field you might write, “Validation Rule: >=0” to remind you to set up a validation rule to only allow positive numbers or zero.

Below is a sample field list worksheet for a products database:

Sample Field List Worksheet