FileMaker Availability Date Option

From Dwayne Wright PMP
Certified FileMaker Developer

TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here)

I am trying to find a solution for the following problem. In the included starter solutions in FileMaker 12 I am trying to add a field in the Personnel Records  file to show the availability of a person. I am trying to make a calculation that will return in text "Available" or not "Available" if todays date (or another date) falls between the repeating start and end dates in the "Time off" layout. Can you help?

I've enclosed a variation of the FileMaker template file (click here). The route that I took is just one of the many possible options. What I did was create a calculation field that builds a listing of the dates between the time off start and time off end dates.

I did a little layout tweaking to accommodate the UI of our new functionality. Reduced the number of portal repetitions for the time off records so that I could fit our "Availability" section. Added some test records with various related time off entries. It would be very easy to add a bunch of gold plating here to account for holidays, weekends and the like. However, I resisted that temptation but it could certainly be added as a later elaboration.

Next I added a couple custom function found from the excellent custom function library at To be able to leverage this custom function library, you will need to be working with FileMaker Advanced to add custom functions, I hope that isn't a problem for you.

DateRange ( StartDate ; EndDate ; RangeLimit )
Andrew Persons, Excelisys
Creates a multi-key field containing a range of dates.

Supertrim ( text )
Debi Fuchs, Aptworks Consulting
Speedily (and with no recursion) remove leading and trailing white space (including spacing, tabs and returns) from a text string.

Next I created a couple fields in the time off table and a couple fields in the Personnel Records table. The heavily lifting is accomplished by the offDates field that is used to create the multiple line key field for non-available dates. The offDatesString is more of a vanity field that I used to actually see the dates in a string from the portal in Personnel Records.

The Available field in Personnel Records is used to calculate the "Availability" message and the availablityDateCheck is our globally stored parent key.

Then created a relationship using a new Availability Start Date field as the parent key field and that listing of dates as the child side of the relationship.

© 2012 - Dwayne Wright -

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.