Hi - schoolboy stuff this for you brainiacs but I'm new to this, especially scripting and calculations!

I need to make a value list that automatically determines the week ending dates for the year. Currently you just 'edit...' the list and input it manually, but I want FM to calculate it so I dont have to worry about getting it wrong! How on this earth do I do that?

i.e.

28-05-04

04-06-04

11-06-04

etc

If you can help it would be appreciated, I'm not built for FM, give me audio or design any day!

Hello John,

There are any number of different approaches you might use to achieve automated updates to the value list you have described. Here is one.

Define a new table called 'Resources' with only a single record, and with a number field called 'RefYear', a global date field called 'gDateTemp' and a text field called 'FridayDatesList'. Then set up a script called 'ReferenceDates' and define it along the lines of:

Freeze Window

Go to Layout [ Resources ]

If [ Resources:: RefYear Year( Get ( CurrentDate ) ]

Set Field [ Resources: RefYear; Year( Get ( CurrentDate ) ) ]

Set Field [ Resources::gDateTemp; Let(X = Date(1; 1; Resources::RefYear); X + 6 - DayOfWeek(X)) ]

Set Field [ Resources:: FridayDatesList; Substitute(GetAsText(Resources::gDateTemp); "/"; "-") ]

Loop

Set Field [ Resources::gDateTemp; Resources::gDateTemp + 7 ]

Exit Loop If [ Year(Resources::gDateTemp) Resources:: RefYear ]

Set Field [ Resources::FridayDatesList; Resources:: FridayDatesList &

If and when FileMaker fixes the problem with related repetitions, this will be much easier to implement and not require a script, as it doesn't in 5.5. For now it's your best option, unfortunately.

Hello,

Sorry to pull this one out of the past, but I am trying to do exactly this is FMP 6 & I'm stuck. Can anyone tell me if it's possible & point me in the direction of how to do it?

Thanks so much,

Elizabeth

Here's a sample file. Let me know if you have questions about it.

WeekEnding.zip

Thanks! That is perfect. I saw your other post about this but couldn't figure out how to change the index to increment by a week. I learn so much on this forum.

Again, thank you.

Elizabeth

Note that it is record dependent. If you want to get a list for a different year, you have to create a new record with your computer's date settings changed to a different year. Since the calculation must be stored for a value list, it will not update automatically when the date changes.