Hi All - Does anyone have an example or idea how I can create a record for each calendar week in a table, using Sunday as the start of the week.
EDIT: Would be worth knowing how to make records on a monthly basis too! thank you
Since Sundays are always 7 days apart, the only real trick is making sure you find the first Sunday in the year and the last Sunday of the year.
For 2018, these dates are:
First Sunday of the Year: 2018-01-07
Last Sunday of Year: 2018-12-30
To get these dates, I would use a modern date API where finding values like this are simple and built right in. You can call that API directly from FMP (see micro-service links below).
Once you have these dates, you could loop in a script and create records for each Sunday, seven days apart. Alternatively, you could even have the micro-service return the entire list of Sundays to you in a RETURN-DELIMITED list you could then loop over that list to insert the records.
EDIT: making the dates monthly instead or also is a single line of code change. Simple.
The beauty of a micro-service is that it works anywhere, with any operating system, on any computer, at any location, with virtually unlimited users. All for free. FileMaker or no FileMaker....a micro-service is totally independent so you can use it with any HTTP-enabled application (an application that can issue HTTP verbs like GET, POST, etc. Terminal, Browser? No problem.). What's not to love?! You Control The Code! (not some third party).
Check out my two micro-service tutorials in the App Innovations Area:
Create Micro-Services Using Java and the Spark Java Framework
The Simplest Micro-Service! (Python and Flask)
I would write Create Weekly Records for the year X script but first on a layout I would have the Start Date field setup with a calendar on the right side. A text would indicate the user to pick the first Sunday of the year he wishes to create weekly record for, now a button or a script trigger to launch the script.
# define a custom dialog with button 1 = Cancel and button = Proceed.Doing so will avoid problem if user clicks too quickly as button 1 is default.
Show Custom Dialog ["Weekly Records"; "Please confirm First Date is " & StartDate field]
If [Get ( LastMessageChoice ) = 2]
# User Hit Proceed
Set Variable [$first_date; Value: StartDate field]
Go to Layout "showing weekly records"
Set variable [$count; Value: 51]
Set variable [i; Value: 0]
Set Field [Weekly_Record Table::StartDate; GetAsDate ($first_date)
Set Field [Weekly_Record Table::StartDate; Weekly_Record Table::StartDate + ($i * 7)]
Set variable [$i; Value: $i+1]
Exit Loop If [$i = $count )]
# User Hit Cancel
Comment: Set Field might be able to be done in one line but I don't have it on top of my head
Let ( dt = Date ( 1 ; 1 ; selectedYear ) ;
Dt - dayOfWeek ( dt ) + 1
calculates the date for Sunday of the first partial week of the year. If you want the first Sunday in the year:
Let ([ dt = Date ( 1 ; 1 ; selectedYear ) ;
Dt2 = Dt - dayOfWeek ( dt ) + 1];
Dt2 + ( year ( Dt2 ) < selectedYear ) * 7
) // add 7 unless first day of year is a Sunday
Retrieving data ...