How to create an array of dates for x weeks

[anotherfmnewbie]

I am trying to create an array of dates that are a week apart and list for x weeks. So if a starting date is 4/25/2010, and x = 4, I would end up with:

4/25/2010

5/2/2010

5/9/2010

5/16/2010

Struggling with how to do this.. any insights would be most welcome.. TIA

[LaRetta]

Hi there! Welcome to FM Forums!

You can use a global field (call it gStartDate) to input the start date. You would use another global field (call it gNumWeeks) to input the number of records (weeks). Then script could be:

Loop

Exit Loop If [ $num > gNumWeeks ]

New Record/Request

Set Field [ yourDateField ; gStartDate + $num * 7 ]

Set Variable [ $num ; $num + 1 ]

End Loop

... as always, back up before trying something new in your file.

[comment]

A repeating calculation field could be handy here - depending on what you need this for.

[anotherfmnewbie]

I'm using this for the left side of a relationship. So, while I could script the process, that seems to carry a lot of overhead. A calculation seems more in line with what I want to do. Any thoughts on how to structure a calculation to do this?

[LaRetta]

Then if you don't need new records, it doesn't have to be scripted.

Let (

i = Get ( CalculationRepetitionNumber ) ;

Extend ( gStartDate ) + 7 * i

)

[comment]

Try:

Let ( [

i = Get ( CalculationRepetitionNumber )

] ;

Case ( i ≤ Extend ( x ) ; Extend ( StartDate ) + 7 * ( i - 1 ) )

)

Give the field enough repetitions to cover the worst case scenario.

[LaRetta]

I was just going to modify so it stopped according to a specified number of weeks. And I was also going to add that I got this calculation originally from you. In fact, it's on my wall because it's so pretty. :wink2:

[comment]

That's nice - usually they tell me I'm off the wall, not on it.

[anotherfmnewbie]

Can you explain how I should implement this a little more. I created a calculation field with 16 repetitions which references a startdate field, but it does not give me the result I need. I also noticed that where you have:

i ≤ Extend ( x )

If I try to use a number rather than x, FileMaker tells me that I need to reference a field rather than use an expression.

[LaRetta]

"but it does not give me the result I need. "

What result does it give you? Did you go to field setup and change so the calc displays the repetitions? If the calc produces a series of numbers (similar to 73387) then the result of the calculation must be changed to date.

You should replace the x with a global field which holds the number of weekly dates you specify (such as 4). It could also be an unstored number calculation (with only the number 4 in it) but you would have to amend the calc every time you wanted to change the number.

Edited April 25, 2010 by Guest

[comment]

If x is a constant rather than a variable, then you can shorten the formula to =

Extend ( StartDate ) + 7 * ( Get ( CalculationRepetitionNumber ) - 1 )

and make the number of repetitions equal to x.