I have a list of timetable line items with a start time and a duration. The classes can vary in length and the available time slots are 30mins. What I am trying to do is create a graphic view of this using conditional formatting. I have a separate table with records for each time slot. For my method to work the graphic table needs to relate to the timetable table and the problem is the timetable only has a record for the start of the class. I thought I could create a field in the timetable which lists the time slots booked via a recursive custom function. My attempts are failing and this is my code.

Let([

start=(Timetable check all::Start);  //start time

dur=Timetable check all::Dur; //Duration

SL=30*60]; //30min slot length

 

start & "¶" & Case(dur>0;session(start+SL;dur-SL)) )

 

Any help appreciated.

 

Cheers John

 

 

Been beavering away but still not getting it to work. - I have attached a file to assist. The Field with the custom function is "SlotList", 

SessionSlots(StartasNumber,DurationasNumber)

Let([ Slot=.5]; //Slot length

Case(DurationAsNumber>0;SessionSlots(StartAsNumber+Slot ;DurationAsNumber-slot);StartAsNumber) )

 

Test.fmp12

I'm not sure from your file and description if this is what you are after or not.

I apologize for the schema - I made this file in FileMaker v3 in 1994.

Hi Lee - that is sort of what I am after but the axis would be switched. Here is a screen shot of what I am after.

If I can get the custom function to work I think I can achieve my aim using a related table and conditional formatting. The table of times lots ( screenshot) would be referenced to the Custom Function in the actual timetable (previously attached file.) 

 

Also the timetabling is actually quite complex as more than one class can occupy the space at the same time, hence the two sectors in the 9:30 and 10:00 time slots. 

 

cheers John

Some progress - I have managed to get a list of occupied slots for a given duration of class - but there are a lot of extra numbers at the end of each line. (totally stumped). To get that far  I added an extra field into the DB (Slots)  and have reattached the file. Here is an example of the result, Any help is most appreciated.

 

RESULT

14.51212512130121251213512125121301212512

14.012125121301212512

13.51212512

13.012

12.5

12

 

This is the function: SessionSlots (StartasNumber ;Slots ; SlotDur )

Case ( Slots >1 ; SessionSlots (StartasNumber+Slotdur & "¶" & StartasNumber; Slots - 1 ; Slotdur) ;   StartasNumber )

 

Where;

StartasNumber = the Class start

SlotDur = 1/2 hour (the available timetable start points at 1/2 hour intervals)

Slots = Duration of the class/slotDur

 

Test.fmp12