wide view of dates and reservations

[Tarnking]

Hello all. I have a B&B reservations DB with relations to a houseDB and TransactionsDB. It works acceptably.

But I would like to create a visual interface in FM5.5 that would allow a reservation-taker to reply quickly, over the phone, for instance, which bungalow (of 12) is available for reservation on a given range of dates that interests a client or travel agency.

The friend for whom I'm attempting to put this together currently has an excel sheet with 365 rows by 12 columns. He types in the names of the guests as they reserve into the date/house grid, which actually yields an effective way of seeing at a glance what's available, but boy is it cumbersome. And it's excel, not FM.

At first I had imagined putting 12 repeating fields 365 (or more) days long in a portal with checkbox X's in the reserved days and maybe invisible buttons in each box which goes to related records (or else creates a new one in the case of empty dates), but people here, who know Filemaker much better than I do, talk about how bad repeating fields are. and my idea doesn't seem very elegant anyway.

So just wondering if there's a really good way to do this...

Thanking you all for any insights...

[keshalyi]

Oh, yes, you're working way too hard making big repeating fields... WAY too hard. Now I don't know the exact structure of your database, but Here goes one solution, if its structure the way I think it is.

I imagine the reservations database holds records that contain a start date and an end date for each reservation, and the bungalow that your guest is staying at. If so, here's an easy way to do what you want.

Make two global fields in your parent database, one Called GLOBALCheckAvailable_StartDate, and one called GLOBALCheckAvailable_EndDate. Now, make a calc field CALCCheckAvailable_DoesOverlap as follows:

ReservationStartDate < GLOBALCheckAvailable_EndDate and ReservationEndDate > GLOBALCheckAvailable_StartDate. This will return 1, if the reservation overlaps the range set by your global fields, and 0 if it does not.

Now, go back to Bungalows, and set up a relationship from Bungalow to BungalowReserved, called Reservations. This will give you every reservation in the Bungalow. Sort this relationship by CALCCheckAvailable_DoesOverlap, in descending order. This way, if any of the reservations overlap, they'll come to the top of the relationship. Now set up a calc CALCCheckAvailable_isavailable, as follows: if(Reservations:CALCCheckAvailable_DoesOverlap, 0, 1). This returns a 1 if the bungalow is available during the dates above.

Now, set up a global container field called GLOBALHighlight (we're still in bungalows, btw), and paste into it, simply, a filled box of a color that you want to highlight good bungalows with. Then, set a Calc field CALCCheckAvailable_HighlightifAvailable as if(CALCCheckAvailable_isavailable, GLOBALHighlight, "").

Almost done!

Now, go back to the reservations table, set up a universal relationship to Bungalows, and sort it by CALCCheckAvailable_isavailable in descending order. Now, you can build the interface to check which bungalows are available. Just but down our two global fields, for them to enter a start and end date to check, then make a portal to AllBungalows. Now, if you put in the background of each portal row the CALCCheckAvailable_HighlightifAvailable field, and then set your bungalow name or whatever on top of it, you will end up with a dynamic portal, that lists the available bungalows first, highlighted.

Make sense?

[Ugo DI LUCA]

Well, keeping almost all Keshavi's good ideas...and calcs.

Create a new file called Check Available (For quick checkings, involving globals, I always use these separate files) or use your current Main file.

Put in it the GLOBALCheckAvailable_StartDate and the GLOBALCheckAvailable_EndDate.

and add

g_Hook (global num = 1)

c_Hook = (indexed calc) = 1

In your Reservation File (that should be used as a line item with fields Bungalow#, StartDate and EndDate)

add

c_Hook = calculation indexed = 1

g_Hook = global number = 1

Set a relationship called "OnetoOne" in both file using g_Hook at left and c_Hook at right.

Set a selfjoin in Reservation using g_Hook at left and c_Hook at right

Still in your Reservation File, slightly modify Keshavi's calc to

CALCCheckAvailable_DoesOverlap = ReservationStartDate < ::GLOBALCheckAvailable_EndDate and ReservationEndDate > ::GLOBALCheckAvailable_StartDate (where "::" stands for your OnetoOne relation). This still will lead to 0,1 results.

and add

c_MinCheck* = Min(selfjoin::CALCCheckAvailable_DoesOverlap)---> This calc will return for all records in the reservation a 0 if there is at least one bungalow available, a 1 if not.

Back in your Check Available file,

add :

n_match* (lookup from c_MinCheck through the OnetoOne relation)

c_checkavailables* (calc text) = Case (n_match = 0,"Bungalow(s) available(s)","No bungalow available")

and

create a script "Update"

Relookup ( g_Hook) without dialog.

[color:"red"] Placing the 2 global date fields + the button for script + n_match (transparent) + the c_checkavailables in the layout would almost be sufficient to tell a client on the fly at the phone if there are bungalows available.

Another button would eventually lead to the portal, selecting the bungalow for the new reservation.

This portal could use the OnetoOne relationship (records still sorted by CALCCheckAvailable_DoesOverlap)

You can use the Highlight function for a cleaner interface (I now know Keshavi loves pink or lime green ) if you wish, but as records are sorted, all the first rows would almost be highlighted.

* If you want to have the portal, keep these fields as they would allow to refresh the portal with the relookup, but you will need to keep n_match on the layout (even transparent).

[Tarnking]

Thanks a bunch to both of you! I'm going to try this out and see what it looks like.