Hi all,

I work in an academic music department in a university. Last year I created a bespoke room bookings database with some great help from the kind folks here.

The problem is this: I have booked in all of our scheduled classes, and now I am able to offer time in our rooms to part-time music tutors who often don't sort out the rooms they need until the last minute. I would like to be able to produce a full list of times when are rooms are NOT already booked.

As Filemaker cannot show records that don't exist I figured that I could create a script that would loop through all our rooms every teaching day between 9am and 6pm and create a new record for every hour which doesn't show an existing conflict (via globall fields). The first day required is 10 October and the last day is 16 June (UK format in script below). The hols.calc field specifies whether the inputted gDate is during our holiday (vacation) period.

This is what I've got so far, the most it does is book times in the first room on the first date specified, but I'm not sure how to get it to move on to the other rooms and/or the following dates:

Go to Layout [ “New booking” (BOOKING) ] 

Set Field [ BOOKING::gDate; "10/10/2005" ] 

Loop 

Loop 

Set Field [ BOOKING::gTimeStart; Time ( 9 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeFinish; Time ( 10 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gActivity; "Instrumental Tuition" ] 

Set Field [ BOOKING::gClient; 2 ] 

Set Field [ BOOKING::gRoom; "RM9" ] 

Loop 

If [ IsEmpty ( BOOKING::conflict ) ] 

Perform Script [ “new global booking - don't clear2” ] 

End If 

Loop 

If [ BOOKING::gTimeStart = Time ( 9 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeStart; Time ( 10 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeFinish; Time ( 11 ; 0 ; 0 ) ] 

Else If [ BOOKING::gTimeStart = Time ( 10 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeStart; Time ( 11 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeFinish; Time ( 12 ; 0 ; 0 ) ] 

Else If [ BOOKING::gTimeStart = Time ( 11 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeStart; Time ( 12 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeFinish; Time ( 13 ; 0 ; 0 ) ] 

Else If [ BOOKING::gTimeStart = Time ( 12 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeStart; Time ( 13 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeFinish; Time ( 14 ; 0 ; 0 ) ] 

Else If [ BOOKING::gTimeStart = Time ( 13 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeStart; Time ( 14 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeFinish; Time ( 15 ; 0 ; 0 ) ] 

Else If [ BOOKING::gTimeStart = Time ( 14 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeStart; Time ( 15 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeFinish; Time ( 16 ; 0 ; 0 ) ] 

Else If [ BOOKING::gTimeStart = Time ( 15 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeStart; Time ( 16 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeFinish; Time ( 17 ; 0 ; 0 ) ] 

Else If [ BOOKING::gTimeStart = Time ( 16 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeStart; Time ( 17 ; 0 ; 0 ) ] 

Set Field [ BOOKING::gTimeFinish; Time ( 18 ; 0 ; 0 ) ] 

End If 

End Loop 

Loop 

If [ BOOKING::gRoom = "rm9" ] 

Set Field [ BOOKING::gRoom; "rm11" ] 

Else If [ BOOKING::gRoom = "rm11" ] 

Set Field [ BOOKING::gRoom; "rm12" ] 

Else If [ BOOKING::gRoom = "rm12" ] 

Set Field [ BOOKING::gRoom; "rm13" ] 

Else If [ BOOKING::gRoom = "rm13" ] 

Set Field [ BOOKING::gRoom; "rm14" ] 

Else If [ BOOKING::gRoom = "rm14" ] 

Set Field [ BOOKING::gRoom; "rm15" ] 

Else If [ BOOKING::gRoom = "rm15" ] 

Set Field [ BOOKING::gRoom; "rm16" ] 

Else If [ BOOKING::gRoom = "rm16" ] 

Set Field [ BOOKING::gRoom; "rm17" ] 

End If 

End Loop 

End Loop 

End Loop 

Set Field [ BOOKING::gDate; BOOKING::gDate + 1 ] 

Loop 

If [ BOOKING::hols.calc = 1 or  BOOKING::gRoom  =  1 or 6 ] 

Set Field [ BOOKING::gDate; BOOKING::gDate + 1 ] 

End If 

End Loop 

Exit Loop If [ BOOKING::gDate = "17/6/2006" ]

End Loop 

Where am I going wrong?

Thanks for your help,

Penny

You don't appear to be advancing through the records.

Your first loop needs a Go to record [Next, Exit after last) at the start of the loop. Also, make sure you Go to record [First] immediately before loop commences.

The same techique may also apply in the other nested loops.

Watch out !

It's very easy to forget that you might be creating records that are being added to the end of the found set, and also that you have moved to that record. Also when this happens the Go to record [Next] will be affected.

I've done quite a bit of stuff like this, and it's very tough to get it right. Do it by hand first and write down the steps you take.

Good luck

Hi Mark,

Thanks for replying. I'm not dealing with records in the usual way here, so I don't think that the go to record step will have any affect on these global fields that I'm checking against before making these bookings.

I want this script to input the required room name, book 9-10am if available, then book 10-11am if available, and so on until 6pm, then move on to the next room and book each available hour as before. Then I want this to repeat each teaching day for the rest of the year.

Thanks