3 Replies Latest reply on Jul 12, 2012 11:53 AM by philmodjunk

    Find Date Range

    RashinAlizadeh_1

      Title

      Find Date Range

      Post

      Hi,

      I have a database with the history of the office's executive elections. The officers were elected for terms from 1 to 5 years. Therefore there is some sort of election (some smaller than others) every year.

      I have a layout where the user can enter new election results as they happen which includes fields for First name, Last Name, , position, year of election, term duration (through which FM then automatically calculates the end of their term), and end of term.

      I want the user to be able to search a certain year, say 1984, and see all the elected people holding a position in that year. This would include people who were elected in 1984 or those whose term began before 1984 but has not yet ended.

      Thanks 

        • 1. Re: Find Date Range
          philmodjunk

          I gather that you have two fields that record the first and last year of the person's term?

          If so, you can manually or use a script to enter this criteria.

          Put < 1984 in the year elected field.

          Put > 1984 in the end of term field.

          Perform your find.

          This can be scripted so that the user enters the year into a global field or a custom dialog and then the script sets up this citeria and performs the find.

          • 2. Re: Find Date Range
            RashinAlizadeh_1

            I have a separate layout with one cell (YearRange::RangeCell) that stores what year the user is searching.

             

            Here is the script I wrote:

             

            *********************

            Show Custom Dialog [Search Year"; "Please Enter the year for which you would like to view the list of Councilors"; YearRange::RangeCell]

            Go to Layout ["Find Executive" (Enter new Data")]

            Perform Find [Restore]

            ***************************

             

             

            And for the Perform find I have specified the following:

             

            ****************

            Enter New Data::Year of election (greater or equal to sign: <=) YearRange::RangeCell

            AND Enter New Data::End of Term (less than or equal to sign: >=) YearRange::RangeCell

            ****************

             

             

            When I press the button that triggers this script I get the dialog box; I enter a year

            But when it switches to the other layout (Find Executive) in find mode it gives an error message "no records match this find criteria"

             

            I know the YearRange:RangeCell dialog box part of the script works, because it changes every time I enter a new date in the dialog box. So the problem must be with how I specified the search criteria. because when I press modify when I get the error message the boxes for year of election and end of term literally have "(greater or equal to sign: <=) YearRange::RangeCell" and "(less than or equal to sign: >=) YearRange::RangeCell" in them.

            Thanks

             

             

            • 3. Re: Find Date Range
              philmodjunk

              The problem is this step:

              Perform Find [Restore]

              The criteria you specify in this manner can only be explicit data or the name of a variable containing the data to be used as search criteria. You can't refer to the name of a field and get it to work.

              Write your script this way:

              Show Custom Dialog [Search Year"; "Please Enter the year for which you would like to view the list of Councilors"; YearRange::RangeCell
              If [ Get ( LastMessagechoice ) = 1 // OK was clicked ]
                Go to Layout ["Find Executive" (Enter new Data")]
                Enter find mode [] //clear the pause check box
                Set field [Enter new data::year of election ; "<" & YearRange::Rangecell]
                Set field [Enter New data::End of term ; ">" & YearRange::RangeCell]
                #Use additional set field steps to specify any additional criteria here
                set Error capture [on] // keeps error dialog from interrupting script when no records are found.
                Perform Find []
              End IF

              And make sure that global storage is specified for YearRange::RangeCell or this will not work.