Week Ending value list - auto

[johnselekta]

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!

[CobaltSky]

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 &

[-Queue-]

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.

[elizabethb]

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

[-Queue-]

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

WeekEnding.zip

[elizabethb]

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

[-Queue-]

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.