I am trying to create a rental database calendar that shows dates properties are rented. I have created a calendar and fields that calculate the date as a number once the year and month are selected for each day of the month. I have a related database called Lease which is linked through the property ID number.

Then I have tried to create a calculation field that says if the calendar date is greater than or equal to the lease start date or less than or equal to the lease end date of a related lease record than the condition is set to "yes" otherwise it is "no"

However, it works for the first record but not for the second or any subsequent records. How can I make the field consider all start and end dates in leases related to it. Any help would be greatly appreciated.

Have a look at the "Max" and "Min" functions.

You need to be considering all the RELATED data for your calc.

Sounds like your calculation field is on the calendar database. Put it on the lease database instead.

Thanks so much for considering my problem. I wasn't sure how to make FileMaker consider all related records in my calculation. Do the min and max functions work like that?

I am hoping within in the properties database to have the calendar that I have set up to have a calculation field show up underneath it with different colors for rented days vs. available. I will then be able to look at one monthly calendar for a property that shows the availability at a glance. I have seen it done but don't know how to replicate it.