2 Replies Latest reply on Jun 28, 2010 7:19 PM by Steve Wright

    Creating a search form with multiple search criteria

    Parag

      Title

      Creating a search form with multiple search criteria

      Post

      I have 5 variables: Name, Age, Weight, Height and Place. Now my search script (below) can search a single variable, e.g. Name = Tom or Age = 18, etc. but it can't search multiple criteria, e.g. Name = Tom AND Age = 18.


      Enter Browse Mode
      Set Variable [ $sName; Value:Table::sName ]
      Set Variable [ $sAge; Value:Table::sAge ]
      Set Variable [ $sWeight; Value:Table::sWeight ]
      Set Variable [ $sHeight; Value:Table::sHeight ]
      Set Variable [ $sPlace; Value:Table::sPlace ]
      Enter Find Mode [ ]
      If [ $sName ≠ "" ]
      Set Field [ Table::Name; $sName ]
      Else If [ $sAge ≠ "" ]
      Set Field [ Table::Age; $sAge ]
      Else If [ $sWeight ≠ "" ]
      Set Field [ Table::Weight; $sWeight ]
      Else If [ $sHeight ≠ "" ]
      Set Field [ Table::Height; $sHeight ]
      Else If [ $sPlace ≠ "" ]
      Set Field [ Table::Place; $sPlace ]
      Else If [ ($sName ≠ "") and ($sAge  "") ]
      Set Field [ Table::Name; $sName ]
      Set Field [ Table::Age; $sAge ]
      Perform Find [ ]
      End If
      Perform Find [ ]
      If [ Get ( FoundCount ) = 0 ]
      Show All Records
      Show Custom Dialog [ Title: "No results found TestPB24Jun10"; Message: "No results have been found (TestPB24Jun10)"; Buttons:
      “OK”, “Cancel” ]
      Else
      Go to Layout [ <unknown> ]
      End If

       

      I am unsure why doesn't function correctly:

       

      Else If [ ($sName ≠ "") and ($sAge  "") ]

      Set Field [ Table::Name; $sName ]
      Set Field [ Table::Age; $sAge ]

       
















        • 1. Re: Creating a search form with multiple search criteria
          comment_1

          Try:

           

           

          Enter Browse Mode
          Set Variable [ $sName; Value:Table::sName ]
          Set Variable [ $sAge; Value:Table::sAge ]
          Set Variable [ $sWeight; Value:Table::sWeight ]
          Set Variable [ $sHeight; Value:Table::sHeight ]
          Set Variable [ $sPlace; Value:Table::sPlace ]
          Enter Find Mode [ ]
          Set Field [ Table::Name; $sName ]
          Set Field [ Table::Age; $sAge ]
          Set Field [ Table::Weight; $sWeight ]
          Set Field [ Table::Height; $sHeight ]
          Set Field [ Table::Place; $sPlace ]
          Perform Find [ ]
          ...

           

           

          • 2. Re: Creating a search form with multiple search criteria
            Steve Wright

            The problem is you are using IF / Else If,  so whats happening is this :

             

            If $sName is not empty, it will set the name field, then skip all other fields and perform the search on that one alone..

            It will use the first condition which is met and skip the others, if you have FileMaker Pro Advanced, you can watch what happens in the script debugger.

             

            Removing the If / Else If statements will perform the search using multiple criteria.

             

             

            Set Variable [ $sName; Value:Table::sName ]
            Set Variable [ $sAge; Value:Table::sAge ]
            Set Variable [ $sWeight; Value:Table::sWeight ]
            Set Variable [ $sHeight; Value:Table::sHeight ]
            Set Variable [ $sPlace; Value:Table::sPlace ]
            Enter Find Mode [ ]
            Set Field [ Table::Name; $sName ]
            Set Field [ Table::Age; $sAge ]
            Set Field [ Table::Weight; $sWeight ]
            Set Field [ Table::Height; $sHeight ]
            Set Field [ Table::Place; $sPlace ]
            Set Field [ Table::Name; $sName ]
            Set Field [ Table::Age; $sAge ]
            Perform Find [ ]
            
            If [ Get ( FoundCount ) = 0 ]
              Show All Records
              Show Custom Dialog [ Title: "No results found TestPB24Jun10"; Message: "No results have been found (TestPB24Jun10)"; Buttons: “OK”, “Cancel” ]
            Else
              Go to Layout [ "Some Layout" ]
            End If

             

             

             

            Just to point out another issue.. lets say you only wanted it to search on one field most of the time, but you wanted it to search two fields only if  name & age where filled out.

            So you enter a Name and an Age, using your code it would stop on the first IF because name is not empty, therefore it would only ever search by Name and not Age.

            i.e

             

             

            If [ $sName ≠ "" ]                  <<--Condition Met
              Set Field [ Table::Name; $sName ]  
            Else If [ $sAge ≠ "" ]              <<--skip all the rest
              Set Field [ Table::Age; $sAge ]
            Else If [ $sWeight ≠ "" ]
              Set Field [ Table::Weight; $sWeight ]
            Else If [ $sHeight ≠ "" ]
              Set Field [ Table::Height; $sHeight ]
            Else If [ $sPlace ≠ "" ]
              Set Field [ Table::Place; $sPlace ]
            Else If [ ($sName ≠ "") and ($sAge  "") ]
              Set Field [ Table::Name; $sName ]
              Set Field [ Table::Age; $sAge ]

             

             

            Instead, you would first want to check for Name and Age, if they both contain a value, it would search both fields, otherwise it would continue to check your conditions and search by any other single field.

            i.e

             

            If [ ($sName ≠ "") and ($sAge  "") ]  <<--Condition Met
              Set Field [ Table::Name; $sName ]  
              Set Field [ Table::Age; $sAge ]
            Else If [ $sName ≠ "" ]               <<-- Skip all the rest
              Set Field [ Table::Name; $sName ] 
            Else If [ $sAge ≠ "" ]
              Set Field [ Table::Age; $sAge ]
            Else If [ $sWeight ≠ "" ]
              Set Field [ Table::Weight; $sWeight ]

             

            Using the above example, If you only entered the Name and left the Age empty, the first condition would not be met, so it would move onto the next

             

             

            If [ ($sName ≠ "") and ($sAge ≠ "") ]  <<-- condition not met -- check next one
              Set Field [ Table::Name; $sName ]  
              Set Field [ Table::Age; $sAge ]
            Else If [ $sName ≠ "" ]               <<--Condition Met
              Set Field [ Table::Name; $sName ] 
            Else If [ $sAge ≠ "" ]                <<--skip all the rest
              Set Field [ Table::Age; $sAge ]
            Else If [ $sWeight ≠ "" ]
              Set Field [ Table::Weight; $sWeight ]

             

             

            You also had two Perform Find[] steps, you only need one, but that may have been after having to tediously type in the script steps :-P

            If only they could be copied and pasted :-)