9 Replies Latest reply on Apr 28, 2009 12:19 PM by cosmoqurries

    Date Ranges



      Date Ranges


      I am new to FM10 - I have been converting databases for our clients, but I am not very fluent in script writing.


      I have a database with one table. I want to create a main page that gives users an option to select a season. After clicking on a season, a page would show all the program numbers listed by the selected season air dates. The table is called Table, the two fields that are needed to show are: ShowNumber and AirDate.


      The main page will show the following:

      Season Three (2008-2009)

      Season Two (2007-2008)

      Season One (2006-2007)


      I can't write the script to successfully pull out the Programs/Shows using data range calculation. Any help would be greatly appreciated.




        • 1. Re: Date Ranges

          You should define when the start of a season is, perhaps in a table for the shows or consistent season.


          To find a date range in a script is as such:


          Enter Find Mode []

          Set Field [YourDate; ">=" & $StartDate]

          New Request

          Set Field [YourDate; "<=" & $EndDate]

          Perform Find



          Your $StartDate and $EndDate can be set in a Set Variable step when you figure out how to grab the range that you want.

          • 2. Re: Date Ranges

            Thank you for your quick reply.


            I am thinking of writhing this script for the Season 3 layout - creating three different layouts, all together.


            Do you recommend I set $StarDate to Date(01;01;2008) and $EndDate to Date(12;31;2009)

            • 3. Re: Date Ranges
                 You should also be able to use Set Field [table::datefield, $StartDate & "..." & $EndDate] in a single find request. This didn't work back in the days of FMP 5.5 but seems to work just fine in newer versions.
              • 4. Re: Date Ranges

                I have used Set Field [table::datefield, Date(01;01;2009) & "..." & Date(12;31;2009)] but it won't take it - it requests an operator.


                Sorry to sound like I know nothing - but where do you set $StartDate and $EndDate? Do I have to create new fields in my season 3 table, or the existing table: Table.

                • 5. Re: Date Ranges

                  try Getastext(date(1; 1; 2009)) & "..." & getastext(date(12; 31; 2009))


                  $StartDate And $EndDate are variables


                  If you use them you can calculate the terms between the "..." or that you use with inequality operators in two find requests. When you enter find mode, your script won't be able to "see" the contents of your current fields unless they are global fields. using variables avoids that problem.


                  Thus you could use


                  Set Variable [$startdate, getastext(date(1; 1; table::year))]

                  Set Variable [$Enddate, getastext(date(12; 31; table::year))]

                  Enter find mode

                  Set field [table::datefield, $startdate & "..." & $Enddate]

                  Perform find


                  Should work. Mr Vodka's method is just as valid as mine, his just has two find requests instead of using the range, "..." operator.

                  • 6. Re: Date Ranges

                    Do not specify the find request. Do it in a Set Field step.


                    • 7. Re: Date Ranges

                      Here is my script:


                      Go to layout ["Seasons" (Table)]

                      Set Variable [$StartDate; Value:GetAsText ( Date ( 1 ; 1 ; 2008 ) )]

                      Set Variable [$EndDate; Value:GetAsText ( Date ( 12 ; 31 ; 2009 ) )]

                      Enter Find Mode[]

                      Set Field [Table::AirDate  ≥  $StartDate & "..." & $EndDate]

                      Perform Find []


                      I am using FM Pro 10. It won't accept ; or , after AirDate in Set Field. It needs an operator. But I can't get the result right. What's wrong?


                      Thanks in Advance. You guys have been great.

                      • 8. Re: Date Ranges

                        When you add set field to your script, you have two buttons: Specify... and Specify...


                        Click the upper Specify button and select your field, Table::AirDate .

                        Click the lower Specify button and enter your expression: $StartDate & "..." & $EndDate

                        • 9. Re: Date Ranges



                          Worked like a charm... I am happy!