7 Replies Latest reply on Dec 14, 2010 11:36 AM by raybaudi

    script to find next month's records

    JohnWolcott

      Title

      script to find next month's records

      Post

      I am trying to write a script that will find all records that have a date which occurs next month.  I have created variables

      ($_start_month) which equals Month(Get(CurrentDate)) + 1

      ($_end_month) which equals Month(Get(CurrentDate)) + 2

      ($_start_year) which equals If (Month(Get(CurrentDate))=12; Year(Get(CurrentDate))+1; Year(Get(CurrentDate)))

      The following script steps work:

      SetField [Dates:zz_Heading__gt; MonthName($_start_month) & " " & $_start_year]

      Enter Find Mode[]

      SetField [Dates::Date; Date($_start_month ; 1 ; Year(Get(CurrentDate))) & "...<" & Date($_end_month ; 1 ; Year(Get(CurrentDate)))

      Perform Find []

      It took about an hour to figure out I needed the space before the field separators, i.e. the ";".  I'm still not clear why the year is automatically updated when I add the vaule "1" to the Month when I use the variables in the find mode but is not updated for the heading field.  I assume it is because one is a date field type and the other is a text field type. 

      Since it is December, everything works, but come next November, will the year be correct, which should be the current year since the next month will be December, or will the year be the following year because the the variable ($_end_month) adds the value 2 to the month, which would take it into the follwoing year or is the date for the start month stored as a complete date (not just the month) and the date for the end month stored as a separate complete date, which is what I hope?

      Is there a better way to approach this search?

        • 1. Re: script to find next month's records
          raybaudi

          Set Error Capture[ On ]
          Enter Find Mode[ ]
          Set Field[ Dates::Date ; Let ( d = Get ( CurrentDate ) ; Date ( Month ( d )  + 1 ; 1 ; Year ( d ) ) & "..." & Date ( Month ( d )  + 2 ; 0 ; Year ( d ) ) )]
          Perform Find[ ]

          • 2. Re: script to find next month's records
            JohnWolcott

            Thanks for the more elegant solution using the Let function and setting the day = 0 to get the last day of the preceding month.  Neat trick.  Looks like I still need to set the variables ($_start_month) and ($_start_year) to get the heading field (the title) to work. 

            • 3. Re: script to find next month's records
              raybaudi

              No, that isn't needed.

              SetField [ Dates:zz_Heading__gt ; MonthName ( Get ( CurrentDate ) ) & " " & Year ( Get ( CurrentDate ) ) ]

              • 4. Re: script to find next month's records
                JohnWolcott

                That didn't work for me because I want the title to read next month's Name and Year, as in January 2011 for next month's report.  I tried using the Let function to set that variable as well, but when I added +1 to the month, January was correct, but the year didn't roll over to 2011. Maybe I did something wrong.  The line I used was

                SetField [ Dates:zz_Heading__gt ; Let (d = Get ( CurrentDate ) ; MonthName ( d ) + 1  & ", " & Year ( d ) )

                which gave the field the value 1, 2010.  So neither the MonthName nor the year worked.  I also tried

                Let (d = Get ( CurrentDate ) + ( Month ( Get ( CurrentDate ) + 1 ) ) ; MonthName ( d )  & ", " & Year ( d ) )

                but that just gave me the current month and year.

                • 5. Re: script to find next month's records
                  raybaudi

                  Try:

                  Let([
                  d = Get ( CurrentDate );
                  d = Date ( Month ( d )  + 1 ; 1 ; Year ( d ) )
                  ];
                  MonthName ( d ) & " " & Year ( d )
                  )

                  • 6. Re: script to find next month's records
                    JohnWolcott

                    Wow!  That worked and reduced my script from 11 steps to 8 steps, but I've never seen a [ inside a ( so I would appreciate an explanation, if possible.  In other words, I understand nested ( ) but I don't understand what filemaker does with [ ] .   I realize that

                    [d = Get ( CurrentDate ) ; d = Date ( Month ( d ) + 1 ; 1 ; Year ( d ) ) ] sets the variable d to the next month and year, but I've never seen a calculation in that part of the Let() function.

                    Also, I find it interesting that Date ( 2 ; 0 ; 2011 ) works in a script or calculation but not if I enter it as 2/0/2011 into a field in find mode.  Filemaker doesn't recognize it as a valid date.

                    • 7. Re: script to find next month's records
                      raybaudi

                      This is the format of the Let ( ) function:

                      Let({[}var1=expression1{;var2=expression2...]};calculation)

                      If you need to declare more than one variable, then you'll need to use the square brackets.

                      "Also, I find it interesting that Date ( 2 ; 0 ; 2011 ) works in a script or calculation but not if I enter it as 2/0/2011 into a field in find mode"

                      FileMaker evaluates the Date ( ) function as a date while the string: 2/0/2011 isn't evaluated as a valid date.