Join my Table? – Part Three

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 for an Enrollment

Deletion of Enrollments

Prevention of “over-enrolling” a course.

 

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:

Disable the relationship creation option.

Place an “Add Enrollment” button to the layout

Create a script “Add Enrollment” to handle the creation of enrollments.

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

Determine if the maximum number of enrollments reached

If yes, then do not create a new enrollment.

If no, then create an enrollment.

Here is the script might look like:

The first thing this script does is two error checks:

If the max number of enrollments field has not been set, then do not proceed, exit.

If the number of course enrollments matches the limit, then do not proceed, exit.

If these error checks pass, then create the enrollment, and set the course ID into it.

Finally, go back to the Courses layout, and place the user on the new enrollment.

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.