Calculating Date Range

[Jalz]

Hi Guys,

Need help with a calculation please. Im using FMP8Advanced on a PC if that helps.

I have two date fields and a text field as listed below

d_startdate, d_enddate and d_subject.

If d_startdate contained 1/5/2006

d_enddate contained 5/5/2006

d_subject contained "This_is_the_subject_field"

the ouput i would like generated in my fourth field d_calc_date_subject would be

1/5/2006_This_is_the_subject

2/5/2006_This_is_the_subject

3/5/2006_This_is_the_subject

4/5/2006_This_is_the_subject

5/5/2006_This_is_the_subject

Thanks for any help out there, im I looking at a recursive function?

Edited May 11, 2006 by Guest

[Jalz]

Not to worry guys, Ive found a function on briandunnings website that enables me to do this.

[Ender]

Try this one:

//ExplodedRange ( startDate; endDate; key ) =

Let(

max = 365; //maximum number of days allowed

Case (startDate ≤ endDate and endDate - startDate < max;

GetAsText(startDate) & " " & key & ¶ &

ExplodedRange( startDate + 1; endDate; key)

)

)

[Jalz]

Thanks Ender,

Whats Key used for? is that where my subject would go? I may have a more difficult problem to solve.....

I've just added a couple of new fields d_start_time and d_end_time

I'm now looking at entering in these values

d_startdate = 1/5/2006

d_enddate = 5/5/2006

d_starttime = 0700

d_endtime = 1300

d_subject = This_is_the_subject

How could I change the function if I wanted my ouput to read

1/5/2006_0700_0000_This_is_the_subject

2/5/2006_0100_0000_This_is_the_subject

3/5/2006_0100_0000_This_is_the_subject

4/5/2006_0100_0000_This_is_the_subject

5/5/2006_0100_1300_This_is_the_subject

Thanks again for your help.

[Ender]

Can I ask what the purpose of this is? If this is for a relational key, there are better ways...

[Jalz]

Yes, Im trying to create a small scheduling application to be used internally. Ive got the application to work more or less with the output I have supplied, but I cant work out how to get the data formatted like I have sent with the fields I have listed.

I think version 2 will use the "new" relationship methods FileMaker 7/8 allows (I've kinda jumped from 6 - 8).

[Ender]

It's still not very clear how these calculated fields fit into your scheduling application.

If they are for relationship keys, then try searching the forums for some our past discussions and examples of how to construct them. Although, I'm a fan of multi-keys, based on the examples you've shown so far it would probably be easier to use FM7/8 range relationships instead.

You might check out the SmartRanges() CF and demo file I put together, that shows both a multi-key technique (using Mikhail Edoshin's Smart Ranges), and the range relationship technique:

http://fmforums.com/forum/showtopic.php?tid/124596/