AnsweredAssumed Answered

Filter portal based on count of records in data range

Question asked by gabrielve on Nov 21, 2011
Latest reply on Nov 28, 2011 by fitch

We are building a system for booking of rooms.

My database layout (simplified for this problem) is:


Table rooms

id_room - unique, auto generated

id_type - type of room, linked to room_types

[ other_data] - number, floor, etc


Table room_types

id_type - unique, auto generated

beds - how many beds are in the room

[other_data] - misc info


Table reservations

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


if (

count reservations::id_reservation > room_type::beds where


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.


I would be gratefull for any help or hints anyone could provide. I am using FM pro adv 11.