Creating a database fast and easy with FileMaker Pro

1 Introduction

This post aims to demonstrate the relative ease and speed of working with Claris FileMaker Pro to create a database application. It contains a step-by-step guide with plenty of screen shots to guide the reader.

Because of this aim, the post will not be an example of an ideal design method or process. Even the so-called agile development method is more structured than what is presented here. Instead, the ‘let’s just start and see where it goes’ method will be used.

The database application that will be built is a simple data management tool that stores descriptions of research variables.

Normally, extensive interaction with different kinds of users, the commissioners, contact persons and other actors involved, is part of a design process. Such users are not available, so instead this post will take its input from a data management guide of the British Ecological Society, and from this author’s imagination.

The next chapter will briefly introduce what the database application will be about.

2 A simple data management tool

Even though the British Ecological Society’s data management guide is from 2014, it reads as if it was written yesterday, that is in 2023.

The guide covers many aspects, some of which could be systematized into a tool to help writing a data management plan. However, this post will focus on a small part which is about the description of the variables. Here is a quote:

“Data level
• names, labels and descriptions for variables
• detailed explanation of codes used
• definitions of acronyms or specialist terminology
• reasons for missing values
• …”

“A Guide to Data Management in Ecology and Evolution.” British Ecological Society, 2014. https://www.britishecologicalsociety.org/wp-content/uploads/Publ_Data-Management-Booklet.pdf. p. 27-28

The bullet list is almost exactly a list of possible fields for the example database. How ‘labels’ are different from ‘names’ is not so clear, so ‘labels’ will be dropped. The original list contains one more bullet which has been left out for brevity’s sake.

A question that needs addressing is, how are the fields going to be grouped in tables? This is not a trivial question, but in this case, the answer is ‘one table’ which could be called ‘variables’. As a side note : naming fields, tables and other elements is not trivial either. Learn more about this here.

Besides a specification of the data, one also needs to know about the required functionality: what which users need to be able to do with the data. Again, for brevity’s sake, it will be assumed that the database has only one user, the researcher, who would like to enter data about their data, search for data and export it, so it can be incorporated in a report.

Users may also already have detailed ideas about the interface design : what should it look like, which buttons there should be, what should they do, and so on. This will not be explicitly included, but just show up.

Now, there is a specification of the required data fields and the functionality, so it is time to ‘just start and see where it goes’.

3 Just start and build

This post will use screen shots from FileMaker v. 19, which is the most current one at the time of writing. In older versions, the basics are the same, but details and some features may differ. Also, the screenshots presented below are from Mac OS. On Windows, they will differ in style.

Please note that not every step and click will be illustrated, and progressively less so. For additional help and information, use the ‘Help’ menu.

* Open FileMaker ( hereafter 'FM' ) and click 'Create'. If FM is already open, select 'Create New...' in the File menu
* Double-click 'Blank', or click 'Blank' and then the 'Create' button

* Choose a name ( 'data management' ) and a location and click 'Save'

FM will create a table with the same name as the file and 5 default fields.

The use of the primary_key field will become clear later on. The other four are not necessary, but could be kept anyways.

The data management fields can now be added

* Make sure to put the cursor in the 'Field Name:' field and enter 'name'

* Make sure that the 'Type:' is set to 'Text' and click 'Create'
* Repeat the previous steps to make text fields called 'description', 'codes_used', 'acronyms_and_terminology', and 'reasons_for_missing_values'

Notice that here a different writing convention ( snake_case ) is used than in the pre-set fields ( which use CamelCase ). For consistency, one should stick to one convention and not mix them. This author prefers snake case, but feel free to choose another convention. Both have the advantage that they will work between many different systems, softwares or platforms. CamelCase may produce problems in places that are not case sensitive. FM is case sensitive ( but its SQL functions are not ) and allows the use of spaces and certain other punctuation, but one better not take chances here and stick to 26 character alphabet, 10 digits and underscores. Sooner or later FM needs to interact with other systems, and then it saves endless hassle not to take chances on this point.

* Rename either the preset fields or the added text fields to unify the writing convention. 

Notice that in the screen shot above, the fields are also re-organized. One can simply drag them one by one or as a group.

The primary_key field is set up such that it automatically receives a unique text code. For users, this code is difficult to read and it may be necessary for them to read it. So, before entering any data, the primary_key field needs to be redefined.

Note that one can debate the pro’s and con’s of the text-based and number-based versions of a primary key, that the choice for either is not straightforward, and that at the end of the day it is perhaps mostly a matter of custom.

* Click the field to select it, set the Type to 'Number' and click the 'Change' button

* Click the 'Options...' button. The dialogue shown below will pop up.
* Deselect 'Calculated value' and select 'Serial number'

* Select the 'Validation' tab. Shown below.
* Select 'Strict data type:', and make sure it is set to 'Numeric only'. Then click the 'OK' button to return to the 'Manage database dialogue'.

* Click the 'OK' button to save the changes and close the dialogue. FM shows the main window, which should look like the image below.

FM automatically added the 5 text fields that were added to the ‘variables’ table. This is the result of a preference that is on by default but can be switched off.

Notice that the layout name still is ‘data management’, i.e. the initial file name and table name.

Notice that some of the labels partly fall behind the fields and that all fields are too small for their purpose of entering and re-reading the data.

* Click the 'Edit Layout' button. ( Alternatively select 'Layout Mode' from the 'View' menu in the application top menu bar ( not visible in the screen shot above ). Alternatively press command-L on Mac, or CTRL-L on Windows )

* Drag and resize the labels and the fields so that the result looks like the screenshot below. It does not have to be perfect, since the layout will be changed soon after.

* Optionally, find out how to switch on the scrollbars in the multi-line text fields. There is a clue in the screenshot.
* When done, click on the 'Exit Layout' button. Alternatively press command-B on Mac, or ctrl-B on Windows.

Done!

FM’s main window is back in the ‘browse mode’ in which one can add records and enter data.

* Click the 'New record' button and enter data about a variable in the five fields.

* Repeat for a couple of variables.

* Familiarize oneself with the browse interface

* Try out how to find data and experiment with the export possibilities

Notice that there is no ‘save’ button. Changes to records are saved automatically, for example when one creates a new record or browses to another record. This is the result of a setting that is attached to the layout.

Also notice that possibilities to ‘undo’ actions are limited. Once one moves the cursor out of a field, there is no ‘undo typing’ option for the field’s data anymore.

4 Rounding up

This post provides a step-by-step guide to create a simple data management tool with five text fields, a primary key field and a couple of support fields. All ready for use.

The point of this post was to show the speed and ease of working with Claris FileMaker Pro.

Hopefully, it has succeeded doing that, and one feels tempted to explore more and start creating!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Powered by WordPress.com.

Up ↑

%d bloggers like this: