3 Replies Latest reply on Jul 22, 2012 10:48 AM by Michelle_1

    Search in 2 different fields

    Michelle_1

      Title

      Search in 2 different fields

      Post

      Hello!

      i have a question about one search i am making.

      I have a table with a field "Date_1" and a field "Date_2"

      I perform a search to show me all records betwen 2 dates. This search is based ONLY on the field "Date_1"

      What i need is that the search would show me all records from "Date_1" AND from "Date_2"

      Actually i make a search to show me all records that "Date 1" is betwen 01 and 10 July

      But i have a record that "Date 1" is 20 April but "Date_2" is 03 July.

      So i need my search to include this record in the results.

      My Script line look like:

      Set Field (MyTabe::Date_1; MyTable::S_DateBegin &"..."& MyTable::S_DateEnd)

       

      Thank you all in advance :)

        • 1. Re: Search in 2 different fields
          davidanders

          Enter Find Mode []

          Set Field [Table::Date_1; 7/1/12...7/10/12]

          New Record/Request

          Set Field [Table::Date_2; 7/1/12...7/10/12]

          Set Error Capture [On]

          Perform Find []

           

          This will find records within the date range in both fields.

           

          http://help.filemaker.com/app/answers/detail/a_id/5210/~/multiple-find-requests

          Multiple Find Requests
          Answer ID: 5210
          Last Updated: Oct 20, 2011
          Some FileMaker Pro owners may be familiar with database programs that require you to enter search requests using "and/or" logic. If you are trying to understand how to apply these techniques to FileMaker Pro, here are some guidelines to keep in mind:

          • The Add New Request command lets you have more than one find request at a time. Add New Request shows up when you are in Find Mode and can be found under the Requests Menu. Multiple Find requests behave as "or" criteria. For example, suppose you have a database that contains a State field and you want to find all the records that contain "California" or "Hawaii."

          1. Enter Find Mode.
          2. Type "California" into the State field.
          3. Select New Request from menu.
          4. Type "Hawaii" into the State field.
          5. Perform the Find.

          • 2. Re: Search in 2 different fields
            philmodjunk

            You appear to have two date ranges:

            Your criteria: S_dateBegin...S_DateEnd

            And

            You data: Date_1...Date_2

            I think that you want to find all records in MyTable where Date_1...Date_2 overlaps the interval in S_dateBegin...S_DateEnd

            If so, the date intervals can overlap three different ways:

            Let's say S_dateBegin = July 1 and s_DateEnd = July 10.

            1) S_dateBegin falls somewhere on the Date_1...Date_2 range.

            Such as: Date_1 = June 1 and Date_2 = July 4

            2) S_dateEnd falls within Date_1...Date_2

            Such as Date_1 = July 3 and Date_2 = August 1

            3) Criteria interval encloses entire Date_1...Date_2 range

            Date_ 1 = June 20    Date_2 = July 30

            A find will need 3 requests to find records in MyTable for all three forms of range overlap:

            Enter Find Mode[]
            #Case 1
            Set Field [MyTable::Date_1 ; "<" & MyTable::S_DateBegin ]
            Set Field [MyTable::Date_2 ; ">" & MyTable::S_DateBegin ]
            #Case 2
            New Record/Request
            Set Field [MyTable::Date_1 ; "<" & MyTable::S_DateEnd ]
            Set Field [MyTable::Date_2 ; ">" & MyTable::S_DateEnd ]
            #Case 3
            New Record/Request
            Set Field [MyTable::Date_1 ; ">" & MyTable::S_DateBegin ]
            Set Field [MyTable::Date_2 ; "<" & MyTable::S_DateEnd ]
            Set Error Capture [on]
            Perform Find []

            Note: S_DateBegin and S_DateEnd must both have global storage specified. Date_1 and Date_2 must be of type date.

            • 3. Re: Search in 2 different fields
              Michelle_1

              Super!!

              I was forgetting the line "New record/Request"

               

               

              Thaaaank you!!