More Date Magic

In my last post, I showed you how to build a value list of available resources for a single date using guides as an example.

In this one, we’re going to take it a stage further and work with a date range. For our example, we are using a property management system to manage vacation rentals.


When we have an enquiry, we enter the arrival and departure dates into global date fields and that then displays any properties that are available for those dates. Obviously we cannot allow double bookings.

One thing to remember is that the property is available on the departure date for previous guests (as they leave in the morning) and new guests arrive in the afternoon so we need to have a calculated field (LastNight) to give the date of the last night for each stay.

The first thing we need to do is to create a relationship to grab the IDs of the properties that are already reserved for the dates in question. We’re going to call this relationship Reserved.

Note that the Start Date is ≤ the last night of the stay and the End Date is ≥ to the arrival date.

This relationship allows us to grab a list of the IDs

But I also want to grab a list of the names of those properties since it is much easier to debug and test when looking at recognizable values.

We use the list of Reserved Property IDs in our relationship to show the ones that are still available.

Note that in the relationship above, we are using the list of booked properties IDs to not be equal to the property IDs (in the Properties table).  The second criteria is to make sure that we are only looking at active properties since there are many properties that are currently inactive.

So let’s take a quick look at what we have so far.

Firstly we’re going to look at reservations and availability for a two day period.

Now we’re going to look at reservations and availability for a five day period.

You should notice that in the above image (for a 5 day period), we have more bookings but the same property (Bijoux, for example) appears multiple times.

The list of available properties, however, still shows the ones that are available to book.  Just to clarify, the list of Available Properties does not include any properties that show up in the Booked Properties column.

Now, let’s look at the layout itself where we have two portals; one showing Available Properties, and one showing Active Properties

Now let’s look at it in Browse mode.

Notice that we are only going to display one of the two portals.  The reason we need the right hand portal is that if there are no bookings for the period in question, the left portal will display nothing but we still need to be able to make a booking so thus we show all properties.  As soon as a single booking has been made for the dates in question, the Available 2 portal will populate.

In closing, this is a very powerful technique and has many, many applications.  I hope you find it useful.

Happy FileMaking.

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!

Subscribe To Our
Video Posts

Join our mailing list to receive info on new videos as we post them

You have Successfully Subscribed!

Website Apps