Display bookings with rooms and dates?

[padlet]

Hi, I am new to this forum and Filemaker (both are great i must say)

I am making a DB for a hotel. I have 3 tables called Guests, Rooms and Bookings. They are related, as shown on the first attachment.

Bookings are made on the Bookings layout with a Guest, a Room and the selections of Arrival and Departure Dates.

On a new layout, I would like to show that, with dates on the top row, and rooms' number on the side column, the availability of the rooms, on a weekly basis. Just like in attachment 2 (made from Excel)

My questions are:

1. Can a portal do it? Portals can only have defined columns, right?

2. If a portal can't do it, should I create lots of fields (100 Rooms x 7 Days = 700 Fields!) and use Validations or Find to check and display the availability?

3. What about next week or next months, do I have to create two buttons and let user change the dates to display associated info?

I guess if I can prevent duplicated bookings (i.e. room booked for two unique guests on the same day) if I can achieve that sort of data display, no?

Thanks in advance for any suggestions or ideas.

Edited February 7, 2009 by Guest

[Søren Dyhr]

Congratulations, you have doubts about this:

If a portal can't do it, should I create lots of fields (100 Rooms x 7 Days = 700 Fields!) and use Validations or Find to check and display the availability?

Display is one thing and the availability is another - each requiring their own relational structure. The display could follow this approach:

http://fmforums.com/forum/showpost.php?post/205225/

But the actual booking needs not follow any sheet'ish metaphor, this method deals with successive days booked, and shows availability:

http://www.nightwing.com.au/FileMaker/demos7/demo705.html

--sd

[padlet]

Thanks Søren Dyhr, I didn't have much luck with it, so I try a new approach:

On the table "Booking", there are fields called "RoomID" and "ArrivalDate"

There is a layout for "Booking", so user can view as well as adding new records for "ArrivalDate"

However, when I add a new record, in order to prevent doublebooking on the same room over the same period, I need to check back to older records against the one I'm adding.

[color:brown]Is it possible to Run a check on the record I am current browsing/adding, against old records, On the SAME Layout?

I guess a button with a script will be the best way to do it? What should i say in the script, someting like:

If "RoomID" = "RoomID" AND "ArrivalDate" = "ArrivalDate"

??

[Frank Weiss]

I think your display grid is just lovely and I would be quite happy if you could share with me how to make one. If you could attach a file, that would be great. Hard to believe that you are a beginner. if you don't want to post it, then I wouldn't mind an email, [email protected]

thank you,

Frank

[Søren Dyhr]

Alright the newest version fm10 comes with some very neat features, worth exploiting here.

Provided you're on an Intel-mac, could you try out a solution I just have created dealing with an medical operating treater, here are there 4 timeslots each day - you could drag and drop a patient to ... similar can you move a booked to another timeslot within the same day.

The solution is a runtime which have been trimmed off the universal code into intel only and irrelevant languages have been removed ... in order to make an as small download as possible. (here down to 15 Mbyte)

http://files.me.com/jamsessionware/sw5hj4

(30 days from now)

Now this is obviously not fully fledged for running a hotel where bookings can have duration beyond a single day ... but it should give you some idea of how to accomplish such a grid of hotel rooms. Since portals as such can be cut up, as shown in my original linking to JMO's template and the debate derived from it. If you study the thread carefully will you learn as well how to deal with recuring events.

In the above show image will you roughly get an idea how the relational structure goes, a detail here is that I exploit this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000128

...to create new records in a recursive structure, instead of prefab'ing a year or two as JMO did in his original suggestion.

--sd

[padlet]

Thanks for the detailed guide

Sorry Frank, being working all weekend, now replying to your question - in bed :

When i first encounter this problem, I did a google search, and NightWing's demo came on the first page of all google result, but I was afraid of its complexity and didn't look into it.

Thanks to Søren, I looked into it and understood it. if you analyst its table relationship, you will see they use a table called Availability to filter the dates from the existing bookings (Preventing Double Booking!! Smart)

and then the Availability table looks into the Rooms Table and only display the match ones.

I can send you my sample later, just let me iron out the bugs first.

http://www.nightwing.com.au/FileMaker/demos7/demo705.html

[Frank Weiss]

No problem padlet. I just noticed that the availability grid picture that I was so excited about was just an excel rendering. So like, I think we're both in search of the same thing. I saw the Nightwing example that everyone is pointing to. It's cool and all that but I'm not sure how it would work for our needs unless we make a ton of fields (as you suggested) as dates. This doesn't seem like the most efficient or elegant way of doing things. Another Nightwing example worth looking at is http://www.nightwing.com.au/FileMaker/demos8/demo814.html

perhaps if you could figure out a way to fuse the two together, we could get a nice availability grid figured out.

Anyway, whatever you can share with me, I'd appreciate. Thanks

[Frank Weiss]

padlet, any progress?

Soren, any chance you can post the above example for non-intel based macs?

[Søren Dyhr]

It's a little bit complicated since Xslimmer follows the operating system you're on, I have no access to a non Intel, so what I suggest you do with my download is to open it with a demo version of fm10 availiable from here:

http://www.filemakertrial.com/nskto/form/entry.aspx

It's the .usr you attempt to open with a regular version, but I have just made a copy for you:

--sd

BookPersoner.zip

[Frank Weiss]

Hey Soren,

Thanks a lot.

[chrisnta]

I have build visiual room plan in my latest FileMaker Solution. Its fairly easy althought it took me few days to figure this out.

[Søren Dyhr]

Not to neglect the efford you've put into it, but if thats what you're after, could you freely poke into this and get a tad wiser:

http://www.seedcode.com/cp-app/ste_cat/CCGantt7

Update ... I've just uploaded a template showing a technique:

http://fmforums.com/forum/showpost.php?post/322871/

--sd

Edited April 13, 2009 by Guest