5 Replies Latest reply on May 26, 2010 2:08 AM by comment_1

    How to create a Find Request for a dynamic Year?

    FBrew

      Title

      How to create a Find Request for a dynamic Year?

      Post

      Filemaker 10.0v3 on Mac OS X 10.5 and Windows XP clients.

       

      I am trying to create a button which will perform a Find. This button will be used every year. It needs to find student that entered the school after March 1 of the current year.

       

      The button will perform a script. In the script, there will be a Extend Found Set command run. In that Extend Found Set command, the Find Request will include a greater than or equal operator (≥). 

       

      How do I get the find request to find records after March 1 of the current year?

      I tried:

      Enrolldate ≥ 3/1/Year(Get(CurrentDate))

       

      But I get the error message:

      'The value of this field must be a valid date in the range of years 1 to 4000 and should look like "12/25/2003".'


        • 1. Re: How to create a Find Request for a dynamic Year?
          comment_1

          First, you cannot use criteria stored in a find request for this - stored criteria cannot be dynamic (at least not in version 10).

           

          Next, the expression "3/1/Year(Get(CurrentDate))" evaluates to .0014925373134328 (you divide 3 by 1, then by 2010).

           

          Try instead:

           

          Enter Find Mode []

          Set Field [ YourTable::Enrolldate ; "≥" & Date ( 3 ; 1 ; Year ( Get (CurrentDate) ) ) ]

          Extend Found Set []

          • 2. Re: How to create a Find Request for a dynamic Year?
            FBrew

            Thank you!

             

            That works, but for a single find. My apologizes, I should have mentioned that I need to do 3 extend found sets. (3 different find with the results combined)

             

            I tried below:

             

            #Find list of students that will be taking the bus and returning next year.

            Enter Find Mode []

            Set Field [ Table::takingBus; "Yes" ]

            Set Field [ Table::returning; "Yes" ]

            Perform Find []

            #Add to that found set a list of students that will be who didn't reply that and are taking the bus this year.

            Extend Found Set [Restore]

            #Add to that found set a list of students that entered 3/1 of the current year.

            Enter Find Mode []

            Set Field [ YourTable::Enrolldate ; "≥" & Date ( 3 ; 1 ; Year ( Get (CurrentDate) ) ) ]

            Extend Found Set []


            When I tried using Enter Find Mode [] again (3rd to last line), the debugger shows that the find is starting from 0 records.

            I tried replacing it with Extend Found Set [], but it doesn't help.

             

            How do I do multiple extended finds with that get(currentdate) restriction?

             

            • 3. Re: How to create a Find Request for a dynamic Year?
              comment_1

              I don't see why you don't do all of this at once, using one Perform Find [] with several requests, e.g,

              Enter Find Mode [ Restore ]
              # Restore the two requests regarding buses and replies

              New Request
              Set Field [ YourTable::Enrolldate ; "≥" & Date ( 3 ; 1 ; Year ( Get (CurrentDate) ) ) ]
              Perform Find



              • 4. Re: How to create a Find Request for a dynamic Year?
                FBrew

                 


                comment wrote:

                I don't see why you don't do all of this at once, using one Perform Find [] with several requests, e.g,

                Enter Find Mode [ Restore ]
                # Restore the two requests regarding buses and replies

                New Request
                Set Field [ YourTable::Enrolldate ; "≥" & Date ( 3 ; 1 ; Year ( Get (CurrentDate) ) ) ]
                Perform Find


                 

                When I try the above putting in my find requests into the Enter FInd Mode, it's combining all my find requests' results into AND statements rather than OR.

                 

                For in detail: In the Enter Find Mode [ Restore ], I have 7 lines of "Find Records" with 7 different criterias. So the finds should be accumulative. For example, If the first Find Record returns 3 records, and the the next 6 Find Records return 2 records each (6 x 2 = 12), then the total should be 15 records, but I'm getting only like 5 results. I know I should have more than 5.

                 

                With your help, I eventually went with many "New Request" and "Set Field" commands, which worked:

                Enter Find Mode []

                Set Field [ YourTable::isBusRider; "1" ]

                Set Field [ YourTable::field2; "1" ]

                New Request

                Set Field [ YourTable::willRideBusNextYear; "1" ]

                Set Field [ YourTable::field3; "1" ]

                New Request
                Set Field [ YourTable::Enrolldate ; "≥" & Date ( 3 ; 1 ; Year ( Get (CurrentDate) ) ) ]
                Perform Find


                Thank you very much!




                • 5. Re: How to create a Find Request for a dynamic Year?
                  comment_1

                   


                  FBrew wrote:

                  it's combining all my find requests' results into AND statements rather than OR.


                  As you have found, you need to open a new request for every OR. Your first two requests are static and could be stored in the Enter Find Mode[] step (that would be 2 requests x 2 criteria). That's what I meant by:

                   

                   

                  Enter Find Mode [ Restore ]
                  # Restore the two requests regarding buses and replies

                   

                  What you have now will produce exactly the same result, and the difference is largely cosmetic.