Hello,
I have a reservationsystem in Filemaker.
When a reservation i done, a script loops through a repeating field
with 366 repetitions that represents the days that is blocked out.
Then when I search for a available cabin, a search goes to the
repetitions on the cabins and omits all cabins with blocked out
repetitions for that timespan.

Any idea to solve this smoother?

I would love to discuss this with others.

Regards Nicolay
(this is my web with cabins from a live filemaker database: www.bookingservice.no
)

On Mar 26, 8:02*am, Nicolay <nicolay.flaa... (AT) gmail (DOT) com> wrote:
Nic,

You've dug yourself a hole by using repeating fields for this. The
best thing to do is get out and fill it in. Drop the repeating fields
and use a separate table to hold reservation records. Each record in
that table would have fields for Date and Cabin Number and Is
Reserved.

DJ

On Mar 28, 4:39*pm, David Jondreau <da... (AT) wingforward (DOT) net> wrote:
Hello DJ,
I agree with you that repeating fields is not a ideal solution, and I
only have this as a search tool, that I agree is not optimal.

I already have the reservation records table in the booking line
items, because this contain date and product ID and is Reserved
status.
But how can I search out Products or show them in a portal when I
enter arrival date and departure date in a search and I will get the
products that have no reservations in that period? I think this have
to be done in a join table between reservation_lineitems withe the
dates and the product table. I would like to find a way to find join
tables that have no reservations for a specific date period, but how?
Do you recommend another approach? I got a tip that maybe relations
could be the fastest way.
Nicolay

On Mar 28, 5:17*pm, Nicolay <nicolay.flaa... (AT) gmail (DOT) com> wrote:
It seems to me you can do this with a simple find.

You have Cabins and you have Reservations, no? And Reservations are
related to Cabins by Cabin ID or some such. From the Cabins layout
find on the related Reservations table. It's unclear how your
Reservations are set up, but a regular find or an "Omit" find should
work.

DJ

"David Jondreau" <david (AT) wingforward (DOT) net> wrote

The original person isn't search for existing data, but for a gap between
existing booked dates big enough to fit a new reservation. Unfortunately
that's not so easy and can really only be done with a looping Script -
whether you use awful Repeating Fields or a separate Table.

Technically you could probably set-up a Field to calculate how many days
between each record's booking and the next record's booking, but it's
probably not worth the all the effort ... you wouldn't gain much in terms of
overall speed since the database would have to keep re-calculating those
values.

Helpful Harry )

Harry, I don't agree on the scripting part.

I thought he could create a calender part (something like CalendarFree from
J Sindelar). This table would contain only 1 record. Containing the date and
a couple of related calcs. Within the selected date you create timeslots
that cabins are booked or not. The actual booking would be stored in a
related table. Then present the selected day. The timeslots recalculate to
booked or non-booked. Presenting the user with a visual display (like
darkblue if booked, light-blue when free). I have done something a bit
similar before, I have never had any complaints. (Which might be a very bad
sign in itself

Hou je goed / keep well,

Ursus


"Your Name" schreef in bericht news:in12us$sj9$1 (AT) lust (DOT) ihug.co.nz...


"David Jondreau" <david (AT) wingforward (DOT) net> wrote

The original person isn't search for existing data, but for a gap between
existing booked dates big enough to fit a new reservation. Unfortunately
that's not so easy and can really only be done with a looping Script -
whether you use awful Repeating Fields or a separate Table.

Technically you could probably set-up a Field to calculate how many days
between each record's booking and the next record's booking, but it's
probably not worth the all the effort ... you wouldn't gain much in terms of
overall speed since the database would have to keep re-calculating those
values.

Helpful Harry )

In article <in12us$sj9$1 (AT) lust (DOT) ihug.co.nz>,
"Your Name" <your.name (AT) isp (DOT) com> wrote:

You could do it with a relationship, using global date fields on the
"left" side, and reservations on the right side. The global date fields
would be in the Cabin table.

Call the global fields
gDateStart
gDateEnd

Relationship:

Cabin::gDateStart <= Reservation:ateEnd
AND
Cabin::gDateEnd >= Reservation:ateStart
AND
Cabin::__kpCabinID = Reservation::_kfCabinID

where gDateStart and gDateEnd are the start and end dates of the period
of inquiry, where you are trying to find open cabins within that
interval. The reservations that overlap the period of inquiry would
satisfy the relationship.

Cabin::__kpCabinID is the primary key field for the Cabin table.
Reservation::_kfCabinID is the foreign key field in the Reservation
table to hold the assigned CabinID.


Do a Find:Omit for cabins where the relationship is not satisfied, and
you thereby find the cabins that do not have a reservation for the
period of inquiry.

The relationship is a multi-predicate relationship, where all three
conditions must be satisfied, and it uses inequalities in two of the
relationships. This capability is available in recent versions of
FileMaker, but not in early versions. I think this capability became
available in FileMaker 7, though I am not absolutely sure about that. I
know it is available fro FileMaker 8 on. This capability is definitely
not available in versions before FileMaker 7.

--
Bill Collins

On 3/26/2011 7:02 AM, Nicolay wrote:

I would probably use a multi-key relationship that included a list of
all desired dates (as numbers) on the parent side of the relationship
and a list of all booked dates (as numbers) on the child side, plus of
course the cabin number on each side. That way, if there's a match for
that cabin/dates combination, then the cabin is unavailable for at least
part of the desired string of dates.

For the multi-line key, you could utilize a custom function such as
http://www.briandunning.com/cf/892 or http://www.briandunning.com/cf/8
or one of the other similar functions on that website.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg
FM Professional Solutions, Inc. Los Angeles

FileMaker Certified Developer
Associate Member, FileMaker Business Alliance

On Mar 31, 12:22*am, "Your Name" <your.n... (AT) isp (DOT) com> wrote:
Harry,

It's not clear why you think a Find will not work.

Nic likely has one of two set ups. Either the Reservations records
have a Start and End Date or there's a Reservation record for each
date. Either way he needs a layout based on a Cabin's TO, which is
related to the Reservation's TO.

In the first case, Nic needs to write a Find that using three *Omit*
requests with data in the *related* Reservations table:
1) the Arrival Date is greater than a Reservation's Start date and
less than its End date.
2 ) the Departure Date is greater than a Reservation's Start date and
less than its End date.
3) The Arrival Date is less than a Reservations Start Date, and the
Departure Date is greater than the Reservation's End Date.

Any Cabins that meet those criteria are omitted and you're left with a
found set of available Cabins.

In the second case, he needs to write a Find that will Omit any cabins
that
1) the New Start Date is less than the Reservations Date and the New
End Date and greater than the Reservations Date.

When using "greater than" or "lesser than" here, substitute greater
"or equal to" as your business dictates.

DJ
www.wingforward.net

On Mar 31, 6:43Â*pm, David Jondreau <da... (AT) wingforward (DOT) net> wrote:

I found another thread that was relevant to this, and I have now a
search that actually is working:
http://forums.filemaker.com/posts/001f658f58#137549

Enter Find Mode []

Set Variable [$gStart; Value:Belegg::Global_sok_fra]

Set Variable [$gEnd; Value:Belegg::Global_sok_til]

#Check to see if gStartDate falls with in start and end dates

Set Field [Belegg_Ordrelinjer:ato_fra[]; " ≤ " & $gStart]

Set Field [Belegg_Ordrelinjer:ato_til[]; " >" & $gStart]

New Record/Request

#Check to see if gEndDate falls with in start and end dates

Set Field [Belegg_Ordrelinjer:ato_fra[]; "<" & $gEnd]

Set Field [Belegg_Ordrelinjer:ato_til[]; " >" & $gend]

New Record/Request

#Check to see if gStartDate...gEndDate encloses a reservation record's
date range.

Set Field [Belegg_Ordrelinjer:ato_fra[]; " ≥ " & $gStart]

Set Field [Belegg_Ordrelinjer:ato_til[]; "<" & $gEnd]

New Record/Request

Set Error Capture [On]

Perform Find []

Show Omitted Only

I have tried to modify the search as described by DJ, but I did not
get it to work, maybe I mixed up some of the searches. But it would be
nice if I could find the free rooms without using the Show Omitted
only step.
At the moment I use a jointable called Belegg between the Product
table and the Orderline table (the reservations). This way I can have
a jointable for only those Products that shall be bookable by date.

I really think I have come a big step in the right direction.
Thanks to all of you!
Nicolay