hour list from start and duration

[Aussie John]

Hi I have been struggling with this for a while.

I have a start time and a duration and would like a field which lists the hours within those parameters.

For instance;

start

 9am

duration

3hr

Result field

9am

10am

11am

12pm

 

My recursive custom function skills are rather poor.

cheers John

[LaRetta]

Hi John,

Would you explain the purpose here?  Do you want a list only or do you need each time slot as a record?  What you plan to do with this list?

Sometimes it works best to create a custom function and then use it in a script to accomplish what you wish, sometimes it is best to handle it all via script, and sometimes by field calculation.  If you wish to filter records according to a slot then records for the times/dates(?) is probably the right choice.

If you truly want a custom function, I searched and didn't find one which specifically filled your request so I put one together which how I perceive it might be handled:

//  timeSlots ( startTime ; endTime ; secondDuration )

Let (
start = startTime
;
start &
Case (
start < endTime ; ¶ & timeSlots (  startTime  + secondDuration ; endTime ; secondDuration )
)
)

If you can go this route, you can enter 3600 for hour intervals or 900 for 15 minute intervals or even 1 second intervals so it allows a lot of flexibility.  I also wonder if it wouldn't be smart to use timestamps instead so as not to limit your options to 24 hours but rather provide a tool for multiple date/times to 'whatever you wish to do with it.'

Again, without knowing the purpose, we are a bit blind. :-)

edited ... sorry about the spacing ... the forums need to be tweaked to remove one of the two returns when only one is entered.  Also I forgot and had changed to secondDuration.  That can be changed easily to minutes.  And really it should be secondInterval instead of 'duration' - I just followed your term but clarity in naming is important so I suggest that be changed anyway. 

 

Edited April 24, 2015 by LaRetta

[Aussie John]

Hi La Retta

 

EDIT: I should add that everything is in hours to make it easier

The result i need, i believe is a list in a field. It is a little complicated but I have a timetable booking table which comes from a third party and i want to cross reference this with a separate potential booking table which lists all of the available slots. The issue is these available slots are in hourly spaces where as the booking shows hour starts and duration.

Basically I want to tell if the slot is booked or not and propose a conditional highlight when the slot has a booking. (using the hours to the list filed as the relationship key).

cheers and thanks

my attempt at the CF is failing to loop

Let([

start=Hour(start);
duration=Hour(duration);
finish=start+duration];


start&Case(finish≠start; "¶" & period(start+1;duration))

)

John

Edited April 24, 2015 by Aussie John

[comment]

Wouldn't it be simpler to calculate the EndTime and use this, along with the StartTime, as the matchfield to your other table? I am assuming that the other table uses a Time field to indicate the hour. If it doesn't, then this may be more complex than it seems: if you just increment the hours, you will eventually get to 13 - which will not match the text of "1pm".

Edited April 24, 2015 by comment

[Aussie John]

Wouldn't it be simpler to calculate the EndTime and use this, along with the StartTime, as the matchfield to your other table? I am assuming that the other table uses a Time field to indicate the hour. If it doesn't, then this may be more complex than it seems: if you just increment the hours, you will eventually get to 13 - which will not match the text of "1pm".

Actually this a good idea to use the finish time as part of the match. Thanks.

That said I would like to know why my recursive CF doesn't work. 

[comment]

That said I would like to know why my recursive CF doesn't work. 

There are several reasons: first, you begin by converting both start and duration to hours, and you send the converted values as the parameters for the next iteration. So if you started with a duration of 3:00:00, the next iteration would receive the number 3 as the duration - and then Hour (3) = 0. Same thing with the start parameter.

The second problem is that you are increasing the start, but not decreasing the duration. In this process, finish will never become equal to start, and you will enter an endless loop (which Filemaker will mercifully cut off after 10,000 rounds).

[Aussie John]

Thanks much appreciated