(Unofficial) Planet FileMaker

In this new series of articles, I am going to explain all the methods I use to help maintain a tidy and manageable database. As the size of a database grows, it can be very easy to fall into complacency, and become slack in areas such as naming conventions. I will give some tips on how to avoid this and keep your database spick and span

Why a Tidy Database?

Keeping your database tidy has huge benefits over the entire life cycle of the system. If you are building a database for the first time, or for a personal project, then you might think this is not important. Indeed, while you are in the development phase, you are “in the moment” – you know where everything goes, how everything works, and why everything is the way it is. Only later on down the track will it become apparent to you why you should have started keeping things tidy from the beginning. You might think you’ll never forget how your database works, but I guarantee that if you leave it for 6 months, it will feel like someone else built it when you return to do more development! The problem is people are continually learning, continually coming up with new ways to do things in FileMaker. The way you build a database 6 months from now may be completely different from the way you build a database now.

For this reason, it is important that you devise some simple conventions you can follow in the development of your database. If you maintain this consistency through your databases, then it becomes much easier to return to a database after a period of time, and be able to understand how it works.

For a developer, this is even more important. If you have multiple developers working on a project, then ensuring everyone is on the same wavelength when it comes to conventions is one of the most important things you should make sure of. Failing to do so usually results in everybody bringing their own ideas to the database, and the whole thing ends up in a mixture of conventions.

Most importantly, maintaining a tidy database is a great habit to get into. If you start early, and force yourself to stick to conventions, then you are well on your way to becoming a great developer!

What is a “Tidy” Database?

One of the biggest things that a tidy database contains is consistency. The term consistency encompasses a broad range of areas of the database, some examples are:

Now, being consistent does not necessarily mean being tidy. You can still have a database with consistently bad naming conventions! The goal is to find good naming conventions, and standards that you can understand and feel are easy to understand, and then stick with them! It’s no good switching your conventions halfway through development of a database unless you are prepared to change the conventions everywhere within the database.

What’s to come?

Within the coming weeks and months I will be writing articles (in order) on the following topics for this series:

You may be asking yourself what Anchor-Buoy is. Well in a nutshell it is a design methodology for how to structure your relationship graph efficiently. Most beginners to FileMaker start with a few tables, and some table occurrences on their graph to match the tables. They will create some basic relationships between the table occurrences, but generally won’t go beyond a certain level. If the database ever begins to grow in size and complexity, they might become overwhelmed with the number of relationships and table occurrences on the graph, and more often than not, the graph evolves into a classic “spiderweb” structure, with everything related to everything else, one big mess!

Anchor-Buoy breaks the cycle of the spiderweb graph. Hopefully I can do it justice and explain it sufficiently, because I believe it to be the single most significant thing you can use to help keep your database tidy and manageable going forward.

With Anchor-Buoy explained, I will move onto discuss the naming conventions I use for table and field names, and also the naming of table occurrences on the graph. How my table occurrences get their names is specific to having an anchor-buoy structure, which is why I must explain that first.

Next, I will go over some of the ways in which I keep scripts well organized. You might not think it necessary, but alot of larger FileMaker solutions contain over 500 scripts, so it pays to know how to locate them. I will also talk about some things you can do within your scripts to keep them tidy and more readable.

Then I will talk about some other conventions that apply to more specific areas, such as value lists and custom functions. While perhaps not quite as important as other naming conventions, that is no excuse not to slack off when naming them!

Finally, I’ll explain and demonstrate the importance of consistency when it comes to designing and building your layouts.

Stay tuned for more soon!

In Part Two, I demonstrated how to implement the basics of the join table solution in FileMaker. While the solution works fine as an illustration for join tables, it lacked many of the basic interface features that would make it a usable solution. In part three, I will show how to implement some of these features, namely the following…

Better Selection of a Student…

In Part two, selection of a student for a new enrollment consisted of knowing the students ID number and entering it into the field. There was no feedback of whom the student was at any point – not very useful for anybody.

A much better way would be to build a value list of students, and allow the user to pick from a pre-defined list. This will give them a visual on the student names. For starters, we will just create a basic value list for students.

We have used the existing Students table occurrence to build the value list, which has also been setup to show all students. This value list uses the option to display a second value. Rather than deal with the students ID number, we have chosen to display the students full name as the displayed field. Underneath, the students ID is still being chosen and inserted into our Enrollments::Student ID field.

Now, attach the value list to the Enrollments::Student ID field in the Enrollments portal. You will see that the students full name is instantly displayed if you are using the pop-up menu option.

Now students can be selected for an enrollment via the pop-up menu. Now, this implementation is still not perfect. For example, there is no restriction on enrolling the same student twice. Also, if you have a large number of students, a pop-up menu is not the best option. A better option would be to use a drop-down menu, which brings with it some advantages and disadvantages. For now, we will stick with the popup-menu to demonstrate the technique, but may come back to these issues in a future article.

Deletion of an Enrollment…

This is a simple one, but a necessary one. We need some way to delete an enrollment. By far the easiest way is to insert a button into the enrollments portal, and attach to it the single script action Delete Portal Row. If you disable the dialog within this script step, the enrollment will be deleted instantly.

Sometimes however, this might be a dangerous action. Hitting the delete button by accident will cause the enrollment to be deleted right away, so you might want to ask the user for confirmation before deleting. Whether a confirmation is ever required is a topic that could be debated endlessly, but the technique would be as follows. First, it is going to require a new script, called “Delete Enrollment”. The script itself would look something like:

A custom dialog is presented to the user, asking them if they really wish to delete the enrollment. The users action is then captured via the Get ( LastMessageChoice ) function. 1 corresponds to the default button action, typically “OK” (though you can specify any action for the default button). If the user has clicked OK, we delete the portal row, otherwise we don’t.

Prevention of Over Enrolling a Course…

For this feature, we are going to need to record exactly how many enrollments are permitted for a given course. This is going to be done by introducing a new field into the Courses table called Max Enrollments, type number.

We create enrollments currently by using the relationship option Allow creation of records in this table via this relationship. This has been fine up until this point. But now that we wish to control how many enrollments are created, it is going to make more sense to disable this feature, and script our enrollment creation instead. By scripting the creation process, we can check whether the maximum number of enrollments has been reached, and prevent the user from adding more gracefully. (also, not to mention that having a blank portal row in the enrollments portal as a means to create enrollments is not exactly intuitive for users).

For this, we will need to do three things:

The script is going to need to do the following things:

Here is the script might look like:

The first thing this script does is two error checks:

Here you can see the new layout design. The blank portal row for creation of enrollments is gone. In its place is an “Add Enrollment” button. The new field “Max Enrollments” is also present, and has been set to 3. This means that we should only be allowed to create three enrollments.

It may be a little difficult from this picture to tell what has happened, but I have reached my limit of three enrollments. Now, clicking the “Add Enrollment” button does nothing.

Can this be made any more intuitive to the user? Well, we could easily place a custom dialog into our “Add Enrollment” script when the user tries to click the button when the maximum number of enrollments is reached. OR, we can use conditional formatting to convey to the user visually that this is the case, saving them the attempt at clicking the button.

First, we can conditionally format the button to appear inactive when the maximum number of enrollments is reached:

The same type of conditional formatting can be applied to the “Max Enrollments” field also if you wish.

Every solution presents its own problems. As you may have realised, there is nothing stopping a user from increasing the max enrollments number, creating enrollments, and then reducing the max enrollments number. This is a way for a user to bypass the restriction imposed on the “Add Enrollment” button. I won’t go into detail as to how this can be resolved, but some options would be:

Field Validation on the “Max Enrollments” field, so it can never be less than the number of enrollments that exist
Script the setting/changing of the Max Enrollments Field
Script Triggers to prevent a user from modifying it to a number less than the number of enrollments that exist.

All would be viable solutions, and perhaps might be discussed in a future article.

This concludes the series on Join Tables for now. Hopefully you have learned something out of these articles, and perhaps picked up a few tips and techniques along the way which will help you turn your basic join table, into something more powerful and user friendly.

You can download the example file used in this article here.

Recently on the Cafe Forum over at FileMaker Today, someone asked if it was possible on a sub-summary report, to display the percentage of records contained within that sub summary. Having never needed this functionality before, I decided to investigate how this might be done. What I came up with is a fairly simple and straightforward way to implement this.

First, a little background on sub-summary reports. In FileMaker, reports are run on a given set of records, called the found set. They come from a single table, however if the records contain information in other tables via a relationship, then that information can be displayed if needed. Within the found set of records, there may be certain records that can be grouped together. A classic example would be a Customers table that contained among other things, a field to record the country a customer is from. Now, lets say the user wishes to produce a report of their customers. The easiest way would be to just create a layout, based on a customers table occurrence, and place some common customer fields on the layout, such as the customers name, country, and contact details.

Now, let’s say the user wishes to group customers by their countries on the report. This would make sense from a sales perspective. It would be nice to see all customers for a given country grouped together. It would also be nice to see how many customers there are for each country, and finally it would be handy to see what percentage of the overall customer base each country produces.

All of this can be accomplished very easily by using a special layout “part” called a “sub summary”. Think of a sub summary part is a header, but for a sub set of records (in our case, country will be the header for our sub-sets of customer records).

There are two important things to know when using a sub-summary part for a report, these are:

• In the sub-summary part setup box, you must specify a “break field”. This is the field that “breaks” up the records into various groups (in our case, the Country field)
• In order to correctly display the sub summary report, your records must be sorted by this break field.

In versions of FileMaker prior to 10, there is an added requirement in that you must be in Preview mode. FileMaker 10 has a new feature in that sub-summary parts will display correctly whilst in browse mode, though you must still ensure your records are sorted by the break field.

Back to the issue at hand. Normally in answering a question for a user, the best way to convey the solution is to build a small example file to demonstrate the technique, and this is exactly what I have done.

You can download the file Here.
(sorry for the bad file hosting link, I am trying to get some better hosting sorted)

Enjoy!

This implementation assumes you have a basic concept of relational database theory, in particular the use of primary and foreign keys in tables.

As a brief catchup, a primary key is a field in a table which has a unique value for each record. A perfect example would be a serial number field, which has a different number for each record. As good database practice, every table in your solution should have a primary key field, regardless of whether it is used or not.

A foreign key on the other hand, is a field in a table, which contains the primary key value of a record from another table. This is how two records in different tables are linked/related to one another, and indeed is how we are going to link an ENROLLMENT to both a STUDENT and a COURSE.

Table Setup

This solution is going to require the three tables mentioned above. It is very straightforward to create the tables. Each table will require a primary key, plus whatever fields you wish to capture information for, as a very basic example:

STUDENTS:

Students

COURSES

Courses

ENROLLMENTS

Enrollments

You can see that ENROLLMENTS has two foreign key fields, because we wish to relate an enrollment record to both a STUDENT record and a COURSE record.

Relationship Setup

How you setup your relationship graph is dependent upon personal preference. In fact, you can successfully implement this solution without having any relationships between the tables at all. However if you want to be able to see enrollments for either a student or course perspective on your layouts, you will need relationships.

In this example I will setup the most simplest of relationships. I have related ENROLLMENTS to both STUDENTS and COURSES via the key field associations as mentioned in the above section.

TO Setup

Building a Course Layout

This solution is going to require layouts for data entry. It will most likely have a Students layout, a Courses layout, and an Enrollments layout.

For this section I will focus on the Course layout only. Adding course-specific fields to this layout is easy. The layout is based on the Courses table occurrence, and so you can place course fields straight onto it.

A useful thing to have on this layout would be a list of all the enrollments for the current course. This can be achieved by placing a portal on the layout. The portal will be based on the Enrollments table occurrence. Be sure to select the related Enrollments table occurrence – the one that has the relationship between itself and the Courses table occurrence.

The fields you will want to put in the portal are basically all the fields from the Enrollments with the exception of a couple. First, it makes little sense to put Enrollment ID in the portal unless you are using this to identify your enrollments. Second, putting Course ID into the portal is redundant, since we are guaranteed that every record that displays in the portal will have the same Course ID as the parent course record, the relationship defines this condition.

So a basic layout might look like this:

Basic Layout

I have pre-populated all tables with a few records to illustrate how this will look. You can see here that the course has three enrollments.

This layout is fine for viewing course enrollments, but more often than not users wish to directly add enrollments from this layout. The easiest way to allow this is to turn on the Allow creation of records in this table via this relationship option on the relationship dialog. You will want to turn it on for the Enrollments side of the relationship, since it is these records you want to create.

Enabling Creation

Now, enrollment records can be created directly from the Courses layout by entering information into the last portal row.

While this is a fully workable solution so far, it lacks many nice features which make it usable for the average user. Current limitations include:

User must know a Students ID to add an enrollment
No visible way to delete enrollments
No restriction on number of enrollments created

This solution can be taken much further with a few simple techniques, some of which will be discussed in part three to this article coming soon!

Perhaps one of the most asked questions that I see on the Cafe Forum over at FileMaker Today, is “How do I do join tables?”. While some users have an understanding of what they are, they may lack the FileMaker skills to implement them. Others are unfamiliar with the concept altogether, but will explain their real word example. Usually it is a prime candidate for a join table. This article provides a real world example that will give you an understanding of why join tables are important, and in what circumstances they should be used. The example will then be implemented in a simple FileMaker solution.

Let’s get started.

Ponder this real world scenario…

“Joe want’s to build a database to manage the computer courses he offers to senior citizens. Being a one-man business, he only offers five courses. He wishes to capture information on his students, his courses, and who has enrolled in which courses.”

This scenario is very easy to understand when reading in plain english. Now lets deconstruct this scenario further.

When building a database from a users specification, one of the first things you should do is identify the potential tables:

With the main tables identified, can you begin to spot what might be the join table? Perhaps one last step will make it more apparent. Lets define in more specific terms which each table will capture within a single record:

How about now? Can you spot the join table? I think it is time to let the cat out of the bag. A join table is a tool by which you link two other tables together, or to put it in more defined terms:

A join table is used to establish a “many to many” relationship between two tables.

What does “many to many” mean? Well, in terms of the scenario it means this:

By using a join table, you are giving yourself much more freedom to capture as much information as you want, without compromising the integrity of your other tables. The STUDENTS table should only capture information about the student, such as their contact details. Likewise, the COURSES table should only capture information specific to a course, such as it’s name, location, and start time.

An ENROLLMENT is a thing, it’s a tangible, legitimate thing that you will want to capture information about, so it should have its own table.. It will capture who the student is, and which course they are enrolling in. In addition to that, it could capture information such as:

This information only makes sense if it is pertaining to a person for a specific course – a grade is meaningless to a course if you don’t know whose grade it is. Alternatively a grade is meaningless to a student if you don’t know for which course it was obtained.

Please Do Not Try This At Home…

So why not capture this enrollment information on the students table, and save us the extra table?

Doing this is bad. It is so bad in fact, that words cannot describe that badness it brings, on a badosity meter it would be off the charts…. If someone were to try it, let’s see how it would go.

Right, so we want to capture enrollment information on the Students table. Well, let’s start by capturing information about the first enrollment a student takes in a course. We will probably need some fields to capture this information:

So far so good. Oh look, Peter Jones just enrolled “Join Tables for Dummies” which has a Course ID of 5. So I will now go to Peter Jones’ record, and fill in my information:

Great. I have successfully captured all the enrollment information I need for Peter Jones enrolling in this course. When the course ends, I can fill in his Grade and all will be good… But wait.. Peter also wants to enroll in “Bad Database Design”. Where am I going to record THAT enrollment information? Well lets see, I could just create more fields to capture it, so I’ll do that.

Phew, glad that is over. I’ve still managed to capture information on his two enrollments……

* 6 Months Later…… *

WHAT?? Peter wants to enroll AGAIN in MORE COURSES??? What am I going to do?? I know… MORE AND MORE FIELDS !!!!!

Wrong.

Already this method has gone down the toilet. I am making the problem much worse by adding a new set of fields to the table for every enrollment. Every time a student enrolls in a course, the database schema must be modified. In short, avoid this like the plague. You don’t want to know what kind of problems you will get into when you want to start running reports! Some people who have tried this method have also gone the way of repeating fields. Rather than 1 field per enrollment, they create 1 field, with many repetitions – one repetition per enrollment. This is just as bad.

Stay tuned for Part Two coming very soon…