5 Replies Latest reply on Nov 28, 2011 11:20 AM by fitch

    Filter portal based on count of records in data range


      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.