Jump to content
Sign in to follow this  
john9210

fmp17 Create Sunday records script

Recommended Posts

I’m trying to write a script that creates records for each Sunday in any two-month period. Below, the script creates records for January and February 2019.

It simply checks each day of the month to see if it is a Sunday and if so, it creates a record.

It does fine except that the second month portion of the script creates a record for the first Sunday in March! No doubt this is due to the fact that the Loop exits after 32 days. However it seems to me that since the DayOfWeek calculation is based $month2=2 (February) it should not result in a record with a March date. Is it necessary to make the loop dependent on the number of days in the month? I was hoping to avoid that additional complexity.

Or is there a simpler script do the job?

 

gYear = 2019

#CREATE RECORDS FOR FIRST MONTH

Set Variable [ $month1; Value:1 ]

Set Variable [ $day; Value:1 ]

Loop

If [ DayOfWeek ( Date($month1;$day;Schedule::gYear ))=1 ]

New Record/Request

Set Field [ Schedule::Date; Date($month1;$day;Schedule::gYear ) ]

Set Field [ Schedule::Flag; 1 ]

End If

Set Variable [ $day; Value:$day+1 ]

Exit Loop If [ $day=32 ]

End Loop

 

#CREATE RECORDS FOR SECOND MONTH

Set Variable [ $month2; Value:2 ]

Set Variable [ $day; Value:1 ]

Loop

If [ DayOfWeek ( Date($month2;$day;Schedule::gYear ))=1 ]

New Record/Request

Set Field [ Schedule::Date; Date($month2;$day;Schedule::gYear ) ]

Set Field [ Schedule::Flag; 1 ]

End If

Set Variable [ $day; Value:$day+1 ]

Exit Loop If [ $day=32 ]

End Loop

Share this post


Link to post
Share on other sites

You should base your loops on actual dates, rather than just an index number. Why not set a variable $date to

$date = GetAsDate ( Date ($month1, 1, gYear ) )

Then have a single loop that increments $startDate by one each time, tests if it is a Sunday, does it's creation if it is, and exits the loop when

Month($date) = $month1 + 2 

(i.e. it has reached the month that is two months after the start month). Remember to exit the loop before testing whether that last date is a Sunday.

There are probably better ways to do this anyway, like finding the first date that is a Sunday and then just adding 7 to it repeatedly to find subsequent Sundays, and stopping when you reach Month 3.

 

Hope this helps.

Share this post


Link to post
Share on other sites
8 hours ago, rwoods said:

$date = GetAsDate ( Date ($month1, 1, gYear ) )

Why do you find it necessary to convert a date to date?

 

8 hours ago, rwoods said:

exits the loop when


Month($date) = $month1 + 2 

(i.e. it has reached the month that is two months after the start month).

The problem with this method is that when the requested period is November-December, the value of $month1 will be 11. And that means the loop will never exit, because Month($date) will never return 13.

 

 

 

Edited by comment

Share this post


Link to post
Share on other sites
4 hours ago, john9210 said:

I’m trying to write a script that creates records for each Sunday in any two-month period.

Putting aside for a moment the unnecessary complexity of your script, one cannot help wondering:

1. Why is it necessary to pre-create records for days on which nothing of interest has yet happened?

2. Supposing it is necessary, why not create records for all Sundays from now till doomsday (doomsday in Filemaker is December 31, 4000, so that would make around 100k records) and be done with it?

 

 

Edited by comment

Share this post


Link to post
Share on other sites

Back to your script: the reason why your method fails is that the Date() function happily rolls over the month and the year. So for example:

Date ( 2 ; 32 ; 2019 )

returns March 4 , 2019 and similarly:

Date (12 ; 32 ; 2019 )

returns January 1, 2020. This is what makes the Date() function such a powerful tool in date calculations.

 

Now, I would suggest you simplify your script to something like (untested):

Set Variable [ $startDate; Value: Date ( Get(ScriptParameter) ; 1 ; Schedule::gYear ) ]
Set Variable [ $endDate; Value: Date ( Get(ScriptParameter) + 2 ; 0 ; Schedule::gYear ) ]
Loop
  Exit Loop If [ $startDate > endDate ]
  If [ DayOfWeek ( $startDate ) = 1 ]
    New Record/Request
    Set Field [ Schedule::Date; $startDate ]
    Set Field [ Schedule::Flag; 1 ]
  End If
  Set Variable [ $startDate; Value:$startDate + 1 ]
End Loop
Commit Records[]

Calling  this script with a parameter of 5 should create a record for every Sunday in the period of May - June of the year in gYear.

Note that there is no checking for duplicates. Calling the script twice with the same parameter could result in having 2 records for each Sunday in the given range.

 

7 hours ago, rwoods said:

There are probably better ways to do this anyway, like finding the first date that is a Sunday

Right. And that would go something like (again, untested):

Set Variable [ $startDate; Value: Let ( 
day1 = Date ( Get(ScriptParameter) ; 1 ; Schedule::gYear ) ;
;
day1 + Mod ( 1 - DayofWeek ( day1 ) ; 7 )
) ]
Set Variable [ $endDate; Value: Date ( Get(ScriptParameter) + 2 ; 0 ; Schedule::gYear ) ]
Loop
  Exit Loop If [ $startDate > endDate ]
  New Record/Request
  Set Field [ Schedule::Date; $startDate ]
  Set Field [ Schedule::Flag; 1 ]
  Set Variable [ $startDate; Value:$startDate + 7 ]
End Loop
Commit Records[]

 

Edited by comment

Share this post


Link to post
Share on other sites

@comment Well, I'm not 100% sure how the implicit type-casting works with FM variables, so I was just ramming home the point to FileMaker that this was definitely a date!

Share this post


Link to post
Share on other sites

@rwoods

The result of Date() is date. The result of GetAsDate() is also date. If type-casting works with the data type returned by the calculation, then it must work the same for both. If it doesn't, then no amount of repeating the message will help.

 

Edited by comment

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By ChangeAgent
      Is this at all possible?
       
      I want to have two buttons that will each do the following. 
      Have one button to ‘Strike Thru’ text that is highlighted before.  Means not all the text in the field will be changed.
      And one button to change the text colour to Red, again highlighted before.  Means not all the text in the field will be changed.
       
      Any tips how to do this?  Please bare in mind I am a FMP beginner.  Thanks. 
    • By lutfishah
      Dear All,
      I am trying to put a conditional formatting validation which turns Red when the value is entered wrong. Basically all my tries have failed so I would like your help here.
      The criteria of entering a value is 
      xxxx-xxxx-xxxx-xxxx
      1111-2222-3333-4444
      1. Length of value is  = 19     OR      this should be a 16 digit number, in groups of 4 separated with dashes
      2. Non-Digits characters are not valid.
      Please could you help me with this and I will really appreciate. 
      Many thanks
      Regards
      Lutfi
       
    • By Koonce
      I have filemaker server on virtual server, I have a script that exports emails and or names i cannot get the file to be saved to the local desktop it tries to save to server with an error when i do manually it works fine so i am doing something wrong...
      Thanks in advance
    • By ThomaStudios
      I have been using FM17Adv for some time.  I am on a MacPro 12-core tower and I had been running macOS Sierra.  All the internal scripts I had in one database worked perfectly.
      I recently updated to High Sierra and now I am getting script errors I don't understand.  And this is from the same exact file I used previously.  I have one script where I want it to open a folder in Dropbox so I can access files there.  But I am getting this error:  Can’t make "Dropbox" into type integer.  Then I get a -1700 error dialog immediately after that.  Can anyone explain how I can fix this.  I have not been able to find anything online.
      The script is a native AppleScript, and I suspect that has something to do with this.  Here is the script:
      tell application "Finder"     activate     open folder " Pieces 2011" of folder "Teaching Material" of folder "Dropbox" of "  ThomaStudios 4TB"     select Finder window 1 set position of Finder window 1 to {2877, 167} end tell
      Thanks.
      J D Thomas ThomaStudios
       
    • By Caio Euzébio
      Hi Guys,
      There are any logic or resource that i can use to make like a "text to columns".
      Make text from field one flow to another fields but only with in the middle of coma regarding your sequence, like a excel?
      Full text with Comas Text1 Separated Text2 Separated Text3 Separated How,Are,You? How Are  You? Could please help me, i need a lot of this resource.
       
       
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.