Join My Table Pt 3 - Putting on the Finishing Touches & Extra Features
By Daniel Wood, 15 December 2010
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 (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.
Example File:
Please find attached an example file. This file was used for all the screenshots in this article, and the other two parts to the "Join My Table" series of articles. It is fully commented to help you fully understand what is going on in the file.
Something to say? Post a comment...
Comments
Mike 02/10/2012 2:16am (12 years ago)
Wondering if you could help me. I have my database set up like yours with players::playerID as a portal on my team layout, and displaying only values from a second field players:fullname, resort by English.
What I can't figure out is how to get it to sort based upon the players::fullname rather than the players::playerID... in other words, even though it's displaying only values from players::fullname, it continues to sort by players:playerID
Thanks.
Mike
Mike Antoniello 25/09/2012 8:55am (12 years ago)
Duh. The concatenated field FullName was set as number. Once I changed it to text, it worked fine.
Mike 25/09/2012 8:21am (12 years ago)
I've discovered more in troubleshooting. My second display field of Coaches::FullName is a concatenated field, and the value list doesn't like it. If I set the second field display as LastName (which is part of the FullName concatenated field), it works fine. Oddly, the PlayersID with second field of Players::FullName is also conatenated and works just fine.
If I can't find a solution, I can simply use last names since I don't have many coaches, and nearly all have different last names. At this point, though, I'm mainly intrigued.
Mike 25/09/2012 2:56am (12 years ago)
This is great, thanks! I set up a database for a soccer league with tables for Players, PlayerAssign, Teams, coaches, and coachesAssign. Using PlayerAssign and the above advice, I can add players to a team using the dropdown menu (which displays the names but then enters the playerID, but I have a field next to it so I can see the name).
My question is that when I do the identical thing for the coachesAssign using a value list for Coaches::CoachesID, also display second field Coaches::FullName, show all values, show only from second field .... my options in the dropdown box are limited to one name. I'm pretty sure that one name is the first record that I entered. If I delete that record, it displays only the second record I created. Any advice?
Peter Pacey 25/02/2012 6:01pm (13 years ago)
Famous last words, just discovered that if you hit the tab key in layout mode, then we're back to the same of problem. Should have been obvious!
Peter Pacey 25/02/2012 5:52pm (13 years ago)
Dear Daniel
Found a solution to this problem, or at least one that will work depending upon what you want your DB to do. It was actually really simple ... too simple as I was looking in all the complex places.
I'm not using this in a portal, so haven't tried it there but should work the same ... I followed your suggestion and placed the calculation field over the top of the other field. I then formatted the calculation field as a button: go to layout -> original layout, and also unchecked "Change to hand cursor over button". Now if a user clicks in the field then it appears as if nothing happens but when they begin the create new record process because the underlying field is 1 in the tab order they are immediately presented with the drop down list. Of course the downside of this is if you want users to be able to select another value from the drop down list in Browse mode - they can't!
For my DB I do want the button on the calculation field to be functional so it is formatted as go to related record on another layout and on the other layout formatted as go to related record on the original layout. So I'm very happy with that and would never have got there without your suggestion of the calculation field .... Thanks again : ).
One other thing I discovered along the way is to do with highlight colours. I thought if I could set the field fill colour to "don't fill" and the text colour the same as the highlight colour then users wouldn't see the ID when they clicked in the field. However FMP seems to do a calculation in the background and changes the highlight colour based upon what you choose as the text colour and this is independent of the highlight colour set in the preferences pane for the OS. Nevertheless I discovered, for some colours I tried, haven't tried them all, that if you set the text colour one shade lighter than the body colour of your layout then FMP doesn't come up with such a great highlight. You can still see the ID in the field but it is rather dull. If you also left justify the text in the calculation field and right justify the underlying field, it helps just a little bit more as users attention is "left directed". If we knew more about the calculation that FMP performs I wonder if using the colours could become a better solution, although I suspect probably not.
Peter Pacey 25/02/2012 1:03am (13 years ago)
Thanks Daniel
This was driving me nuts, I thought there must be a work around and I was trying everything to find it without success. At least I can now relax knowing that there is no perfect solution. I did put a bit of eye candy *up & down arrow" at the right side of the field so users will know it's a click-able list but of course the field's No 1 in the tab order and as soon as you begin the new record process the up/down arrows disappear.
I will try your solution and see if I can work out any way to make it a little more user friendly.
Thanks again Daniel : )
Daniel Wood 24/02/2012 5:21pm (13 years ago)
Hi Peter,
The popup menu field style can only be tabbed to and activated if from a windows machine, not on a mac unfortunately. If you wanted to use a drop-down menu then there are some possible workarounds you can employ to lessen the effect. The drop-down will let you tab to it so it can be a good option for this. The problem obviously is that if using a drop-down, you don't see the secondary display field from the value list as you would with a popup. Instead, you see the actual field value which is the record ID (primary key).
This can be remedied to a degree by placing a calculation field over top of the Enrollment::Student ID field. The calculation field is setup to display the associated students name. So you would setup a relationship from Student ID to the students table, and your calculation would reference the related students name. make the calculation non-enterable and place it on top of the ID field in the portal. Now, when you choose a student ID from the drop down, as you tab to the next field, the calculation should be showing the students name, and you should not see the record ID.
The downside to this however is that once you click back into the student ID field, you are going to see the ID displayed temporarily until the user exits the field. I wish there was some way to prevent this happening but it's just one of those things that doesn't have an easy fix in FileMaker. What we really need is either for popup-menus to be able to be keyboard activiated, or for drop-downs to have an option to only ever display the 2nd field (ala popup menu) - no matter how much whining to FM that is done in regards to this issue it hasn't been implemented yet, but hopefully one day :)
Peter Pacey 24/02/2012 5:09pm (13 years ago)
This example uses:
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.
I am doing a similar thing but not in a portal and am using a foreign key. So e.g. when a user clicks on the field Enrollments::Student ID they would see a pop up menu of all the Students. But it would be better if they saw this when they immediately begin to create a new record, Filemaker won't do this. I have the tab order set so that the foreign key (in this example Student ID) is the first field, I have also tried a goto field script step, but regardless Filemaker will only show the list when the user clicks in the field, it won't show when the field is tabbed into ... it will show if I make it a drop down list but of course the display when browsing records is the foreign key number. Any way around this other than buying a plugin?
Daniel Wood 01/07/2011 1:58pm (13 years ago)
Cheers Rod, glad the article helped out. You can always put that bike on layway or rent-to-own or something ;)
Rod Davies 01/07/2011 1:52pm (13 years ago)
Ahh... sweet mystery of life at last I've found you.
Daniel your insightful, blow by blow descriptions addressing solutions is a godsend.
Can't thank you enough. I'd buy you a mountain bike-but I'm a bit skint.
No one has commented on this page yet.
RSS feed for comments on this page | RSS feed for all comments