Self-updating date-based value list

[David Jondreau]

I want to create a value list of dates that will be chosen to view timesheets. I want to start with a date, have the date 14 days later, and so on, but not dates in the future.

Today, it should look like:

2/19/2007

3/5/2007

3/19/2007

4/2/2007

But starting on 4/16/2007, it should look like:

2/19/2007

3/5/2007

3/19/2007

4/2/2007

4/16/2007

And I want this value list accessible from different layouts based on different tables.

I'm having trouble because I can't base a value list on an unindexed field (ie a calc using Get(CurrentDate)) or a global (ie setting a global to my list of dates on opening).

Any ideas?

[Raybaudi]

Hi

you have to have 2 fields:

1) date ( the date to start ) with AutoEnter option of a calc:

Let(

$$date = Get ( CurrentDate );

date

)

2) dates - calculation repeating field with 10 or more rep and calc:

Let(

date = Extend ( date ) + 14 * ( Get ( CalculationRepetitionNumber ) - 1 );

Case(

date ≤ $$date; date;

""

)

)

Now define a Value List with the values of the second field.

Also you'll need an opener script that do only:

Set field [ date ; date ]

Commit Records/Requests

Dates.zip

[Mikhail Edoshin]

If you have a two-field value list, one of fields can be unstored. FileMaker loves to complaint about this, but it will still display the field. So in your case you could do something like that:

Make a table with 14 records. Add the Offset field and fill it in with numbers from 0 to 13. Add another field Date, = Get( CurrentDate ) + Offset. Make a value list with Offset and Date, sort by Offset. It will display the Date and the date will update every day. You can hide the Offset field.

Of course, with such a value list you'll be entering the offset, not the date. You can either rework your fields and add a table for the offset or try to make an auto-enter formula that takes the offset and makes a valid date out of it. I'm not sure though if it will allow you to enter a number in a data field. (I remember I did something like that, but don't remember if the result was positive or not. I'm already old : )

[David Jondreau]

I couldn't figure out how to determine which field to Set...then I realized it goes into a 1 record table. Is that right?

I prefer not to use $ or $$ variables in calculations, so I took your technique and tried it another way (the result is a little different than what my originally posted goal: I want only the dates when the end of the cycle has passed, not just the beginning):

//CycleStartDate and CurrentDate are set by script

Let([

startDate = Extend(CycleStartDate)-14 ;

currentDate = Extend(CurrentDate);

listDate = 14*Get(CalculationRepetitionNumber)+startDate;

thisRepDate = Case(listDate< CurrentDate;listDate;"")

];

thisRepDate)

I've never used repeating fields before so this was fun.

I realize now I could also do this by creating the list of dates with a custom function and Set Fielding that into a Text field (of a one record table) instead of the repeating field method.

Do you think there's an advantage of a using a repeating field?

Thanks

G