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

    Date Ranges

    cosmoqurries

      Title

      Date Ranges

      Post

      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.

       

      Thanks,

      CL

        • 1. Re: Date Ranges
          mrvodka
            

          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
            cosmoqurries
              

            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
              philmodjunk
                 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
                cosmoqurries
                  

                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
                  philmodjunk
                    

                  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
                    mrvodka
                      

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

                     

                    • 7. Re: Date Ranges
                      cosmoqurries
                        

                      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
                        philmodjunk
                          

                        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
                          cosmoqurries
                            

                          Awesome!

                           

                          Worked like a charm... I am happy!