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

    Filter portal based on count of records in data range

    gabrielve

      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

      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.

       

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