Filter portal based on count of records in data range
I am building a system for booking of rooms.
My database layout (simplified for this problem) is:
id_room - unique, auto generated id_type - type of room, linked to room_types [ other_data] - number, floor, etc
id_type - unique, auto generated beds - how many beds are in the room [other_data] - misc info
id_reservation - unique, auto generated id_room - links to rooms::id_room id_person - links to people::id_person (table not related to current issue) date_from - when does the person checks in; sets global field globals::date_from date_to - when does the person checks out sets global field globals::date_to
What I am trying to achive, is to have a portal (I think it is a best solution here) on "create reservation" layout where, after selecting reservations::date_from and reservations::date_to the user would get a list of rooms that have at least one free bed in given time range.
The filter would be something along the lines of
count reservations::id_reservation > room_type::beds
globals::date_from =< reservations::date_from =< globals::date_to
globals::date_from =< reservations::date_to =< globals::date_to
) return false
Meaning, find any reservations already made, see if the one I am creating starts or ends within the existing one. If there are more matching reservation that there are beds, do not show this room on list.
In theory, all is peachy. I just cannot get the filtering script to pass the syntax checks.
I am using global fields, since the system will be deployed using instant web publish, and I forsee having to do the reservation proces in two steps, since there will be no dynamic portal filtering on web.
Once again, I would be gratefull for any help or hints anyone could provide. I am using FM pro adv 11.