Time List

[FMDuck]

I'm trying to build a time list. It seems it would be best done with a CF but this is starting to hurt my head a bit.

For example, I would specify the following:

StartTime: 10:00AM

EndTime: 2:00PM

Interval: 5

This would produce a list of times between 10:00 and 2:00 in 5 minute intervals

Any help would be appreciated.

[Ender]

Hi Jeff,

Try this:

//TimeList ( Start; End; Interval )

Case(interval>0;

Let(

nextTime = Start+Interval;

GetAsText(Start) & ¶ & Case(nextTime>=End; GetAsText(End); TimeList(nextTime;End;Interval))

)

)

You may need to adjust it a bit to get the result times to have the desired time formatting.

[FMDuck]

Thanks Ender,

I made a slight modification (*60 for minutes)...

Case(interval>0;

Let(

nextTime = Start+ (Interval * 60);

GetAsText(Start) & ¶ & Case(nextTime>=End; GetAsText(End); TimeList(nextTime;End;Interval))

)

)

But here's my output...

10:00

10:05:00

10:10:00...

I'm trying to not show seconds. I tried to format the field as a number, set the display format, then change back to text, but it had no affect.

Any ideas on this one?

[Ender]

You can probably work something out based on this CF from Jonathan Mickelson:

http://www.briandunning.com/filemaker-custom-functions/detail.php?fn_id=144

Or just use that CF to format each time as you append it.

[Fenton]

You could add calculations to produce the time the way you wanted it. For example:

StartTxt = Hour (Start ) & ":" & Right ( "00" & Minute ( Start ); 2 ) & Case ( Start ≥ 43200; " PM"; " AM" )

The Right function is needed because Minute(Time) is a number result, otherwise "10:05" ends up "10:5".

You'd probably want to add this to the Let ([ ]; ) function:

//TimeListTxt ( Start; End; Interval )

Case(interval>0;

Let ( [

nextTime = Start+Interval;

StartTxt = Case ( Hour (Start ) ≤ 12; Hour (Start ); Hour (Start ) - 12 ) & ":" &

Right ( "00" & Minute ( Start ); 2 ) & Case ( Start ≥ 43200; " PM"; " AM" ) ;

EndTxt = Case ( Hour (End ) ≤ 12; Hour (End ); Hour (End ) - 12 ) & ":" &

Right ( "00" & Minute ( End ); 2 ) & Case ( End ≥ 43200; " PM"; " AM" ) ] ;

StartTxt & ¶ &

Case( nextTime>=End; EndTxt; TimeListTxt ( nextTime; End; Interval ) )

)

)

[Edited and fixed]

I'm assuming you need to display this result for some reason? Because otherwise I'd use the Time as a number, which I think is better and more reliable, say for a relationship test (between 2 ranges).

Which brings up a couple of questions I have, which I need to solve sometime; which I could test myself, but I wondered whether anyone has already.

1) I have heard that ">" and "<" operators in a compound relationship are somewhat slower than using a dedicated date-time range produced by a custom function. Has anyone tested on large files? I would be thinking of time ranges with say a 10 min interval (no smaller). Some entries could be the full 24 hours however.

If the answer to "1" is that Custom Functions are much faster, then:

2) You can add other fields to the range afterwards, say an ID field and one other, using Substitute ( the range; ¶, the fields & ¶ ). So there are 3 methods:

a. Substitute afterwards, in the regular calculation field, in Define Fields

b. Substitute within the Custom Function

c. Build into each line of the Custom Function during the recursion

I could think that "c" would be the fastest, from what I've heard.

Edited August 17, 2005 by Guest
Fixed the time display to be 12 hour

[comment]

Your formula returns 15:00 PM, which I believe should be 3:00 PM in 12-hour AM/PM format.

Try:

Hour ( theTime ) - 12 * ( theTime >= 13*3600 ) & ":" &

Right ( "00" & Minute ( theTime ) ; 2 ) &

Choose ( theTime >= 12*3600 ; " AM" ; " PM" )

I don't have answers to your questions, just a note. Don't forget the option to use a repeating field. It is often easier to manipulate than a CF and it retains the date/time/timestamp format.

[FMDuck]

I've never used a repeating field for a calculation and my brain is coming up short there too.

Thanks for the good ideas above, but does anyone have a good suggestion for how to write it? I can't seem to get the calculation to carry down the repetitions.

[comment]

Try something like:

Let ( [

start = Extend ( StartTime ) ;

end = Extend ( EndTime ) ;

add = Extend ( Interval ) * 60 ;

i = Get ( CalculationRepetitionNumber ) - 1 ;

t = start + i * add

] ;

Case ( t <= end ; t )

)

[Ender]

Hi Fenton,

Regarding your first question:

1) I have heard that ">" and "<" operators in a compound relationship are somewhat slower than using a dedicated date-time range produced by a custom function. Has anyone tested on large files?

Last fall I did some testing of the speed of range relationships using FM ranges vs. my Smart Ranges CF. With local files, FM ranges was definately more sluggish. But when the files were hosted, they both performed equally well. Unfortunately I haven't had the opportunity yet to test them with many users in the system, so my results are inconclusive.

My guess is that if there are lots of criteria to match in the relationship, a compound key will be faster than the multi-criteria relationships.