Date Magic

It’s a common scenario.  You’ve got a reservation system of some kind or another and you have a limited number of resources available.  When you add a reservation (for a specific date), you want to be able to only select the resources that are available; in other words, they haven’t already been booked for another reservation on that same date.

This is not a terribly complicated scenario but you may find it a little difficult to get your head around; I certainly did. However, it is a really powerful, and useful, technique that has many different applications so it’s worth making the effort.

So, let’s give it a whirl.

The first thing you need to do is to create the tables that you need; there are three of them.


The Reservations table is for the actual reservations; the Res_Guides table is for the Guides assigned to each reservation and the Guides table lists all of the Guides (your resources).

We are then going to create our initial set of relationships.




You may notice that I’ve created a relationship called All_Guides.  (Technically I don’t need this relationship but I’m using it to illustrate the technique later on in this post so just go with it for now.  In any case, it is a simple relationship using a calculated field with a value of 1 on both sides of the relationship; yes, I know I could use a cartesian relationship but I prefer to use the constant).

The relationship between Reservations and Res_Guides



allows the user to create the related records using the Reservation ID (_pkid) and the date.  (Note:  If the Reservation does not have a date entered, you will not be able to create the related record(s).

I now need to create a calculated field to get the GuideIDs from the reservations but to do this, I first need to create a new relationship (to the Res_Guides table)





Based on this relationship we now create a calculated field to grab the GuideIDs for the date in question.


We’re using the List function to grab those IDs.  (Now you may notice that I’ve created a field to display the guide names (which are coming from the same relationship).

In order to minimize the number of table occurrences that I need, I’ve chosen to lookup the guide name when the guide id is entered.


and I’m going to use that field in my value list.  (There are several reasons why I’m doing this; one is that I don’t need to create extra relationships but, more importantly, I want to be able to sort the portals by a field that exists within that portal’s table.  The other reason is that it is easier for me to test and debug when I’m looking at values I can recognize which is why I created the second list field using the guide names).

Now that I’ve got the list of guides for a specific date, I need to add one more relationship to show me who is available and I’m going to use that list field in the relationship.

The new relationship is called Available_Guides and it is very simple in that the Guide IDs listed are not equal to the Guide’s ID in the Guides table.



So now that we’ve got all of our structural work done, we can now start building our layout.


These two portals that you see are not going to appear on the screen itself but we’re showing them here for illustrative purposes.

Now that we’ve added the portals, let’s take a look at the data we have.


As you can see we have a reservation for April 15, 2016 and we’ve assigned two guides for that reservation (the Guides for Reservation portal on the right of the screen).  The portal showing the available guides is missing the two names that have already been assigned.  Now we need to create a pop-up menu field showing those available guides.



Below is our working portal.


For now, we’re simply concerned with the GuideID field on the right so let’s take a look at the value list for that field,


The ‘All_Guides’ field


simply displays all of the different guides that are in the system.

Now, you may well be wondering why there are two copies of the same field, with different value lists, in the portal.  The reason is simple.  The Available_Guides relationship only works once a guide has been added to a reservation for the date, otherwise it will display this.


So what we need to do to make sure that the user can always select a guide is to add a condition to hide or show the field with the value list, depending whether or not a guide has been assigned

Hide_All_Guides Hide_Avail_Guides

In the examples above, we hide the Available Guides  field when no guides have been selected; in other words, there is no completed relationship.  The user then selects from all of the guides.  In the other example, once a guide has been assigned, we hide the All Guides field.

Let’s take one more look at our ‘selector’ portal.


In the right hand portal, we have stacked all three fields one above the other with the guide name on top.  When the user clicks into that portal, it displays the correct value list so that a guide can be selected.

Below, we are in a new record for a date where we have already assigned two guides and thus we only get to choose available guides



Here we have a new date where no guides have been assigned for that date and thus we select from all guides


Now that we have everything working correctly, we simply create a layout for the user to enter data in



And we’re done.

I hope you find this technique a useful addition to your FileMaker bag of tricks.  As always I welcome your comments and feedback.

Michael Rocharde – April 12, 2016

[contact-form][contact-field label=’Name’ type=’name’ required=’1’/][contact-field label=’Email’ type=’email’ required=’1’/][contact-field label=’Website’ type=’url’/][contact-field label=’Comment’ type=’textarea’ required=’1’/][/contact-form]

Leave a Reply

Subscribe To Our FileMaker Tips & Tricks

Join our mailing list to receive the latest FileMaker tips, tricks and video how tos from Michael Rocharde

You have Successfully Subscribed!

Website Apps