Saturday, October 06, 2007

FileMaker Pro 9 Advanced: Managing Databases

I know I said in my last review entry that I would create a new database. I decided to save that for next time. Instead, I thought I would do a quick run through of the screens used to manage databases. That is, the screens used to create tables, create and edit fields, set relationships, etc.

As I have come to expect from FileMaker, the tools used to manage databases are very visual. So far, the visual aspect of FileMaker has been its strongest feature, for me anyway.

Manage Database & Value Lists

In the photo above, you can see where I have arrows to two buttons on the tool bar. The button to the left is the "Manage Database" button. The button to the right is the "Manage Value Lists" button. I will explain both of those below.

View Tables When you press the "Manage Database" button, you are taken to the Tables tab. On this tab, you will see all table definitions contained within this file.

Remember the last entry when I created the Recipe database, I saved a single file. In FileMaker, you may have a database file with 1 or more tables. The recipe data is simple enough that only a single table was required. A future application in the series will create a database with multiple tables.

On this screen, in addition to seeing what tables are contained within the file, you can see how many records are stored in the table and how many fields comprise the table.

View Fields The next tab is the fields tab. The fields tab is where you would add, drop or edit a table column. You give a column a name and a data type. You can optionally give it a comment.

The Options button allows you to set additional processing information about the field. Some of the options are Defaults, Calculated Values, Lookup Values, Validation, Editability, Storage, Indexing and even a Japanese translation. I will also cover this field in greater detail in a future review entry.

View Table Relationships The final tab is the relationships tab. There's not much to say about this database because there is only a single table. In a more complex structure, you may link fields in one table to fields in another table. This is called a foreign key and/or referential integrity.

In FileMaker, you may link many tables in a single file, or you may link tables across files. As a matter of a fact, you can create a relationship to a table using an ODBC connection which means you can link to external databases from other vendors. That is a topic I will definitely be covering in the future.

Manage Value Lists The other button I highlighted above, is the "Manage Value Lists" button. The recipe database has a field called category that is pre-populated with a list of values. You use the "Manage Value Lists" to edit the values in that list.

From this window, you can select the value list of interest and press the Edit button. Of course, you can also create and delete lists here.

Edit Value List For your value list, you can either use values stored in a field in this database, in another database or with hard coded values.

Very frequently you will want to use a table as a lookup value. In a system, you will want to share that data across multiple databases. In my next entry, I will create a new database and store the Category lookup value as records in a table. I will then come back and modify the Recipes database to use that database and table.