AnsweredAssumed Answered

Filter portal based on count of records in data range

Question asked by NorbertKowalski on Nov 19, 2011
Latest reply on Nov 28, 2011 by philmodjunk

Title

Filter portal based on count of records in data range

Post

I am 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
  (
    globals::date_from =< reservations::date_from =< globals::date_to
    or
    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.

Outcomes