5 Replies Latest reply on Sep 10, 2016 10:21 AM by alangodfrey

    Perform Find not working as expected

    hangry

      I have a table with a field called "Building".

       

      In script I use the Perform Find command to filter the records to include only those records where Building=$$FindBuilding, where $$FindBuilding is a global variable set earlier in script.

       

       

      This all works fine except when the value of $$FindBuilding starts with the word "Building".

       

      For instance I have records that have values for the "Building" field that are:

       

      Building A

      Building B

      Building C

       

      Whenever I run the script trying to filter the records for only those with, say, "Building A", it returns all records that have either "Building A", "Building B", or "Building C" in them.

       

      I also have records where the value for Building do not include the word "Building" in them. In these cases the Find works as expected.

       

      Can anyone help me understand why the find works differently based on the value searching for?

        • 1. Re: Perform Find not working as expected
          philmodjunk

          I can't replicate your results.

           

          Using FileMaker15, I created a small test file with a field of type text

          Created 4 records entering the text

          Building A

          Building B

          Building C

          Building C

          Building CC

           

          I then created and performed two scripts. The first is exactly like what you report, using =Building A as its stored criteria (Though in actual practice, I never use stored criteria. I used it here to keep the test exactly like yours.*) In the second, I used =Building C as my criteria.

           

          The first script found exactly 1 record, Building A as expected. The Second record found Building C and Building CC. Might the second example more closely match the results that you are getting?

           

          If so, the solution is to use ==Building C so that this is an exact match rather than a "starts with" match.

           

          *I normally use find scripts in this format:

           

          Enter Find mode[]--no pause option selected

          Set Field [Table::Field ; "==Building C"]
          Set Error Capture [on]
          Perform Find[]

          #Handle what happens when no records are found

          If [Get ( FoundCount ) = 0 ]

          • 2. Re: Perform Find not working as expected
            alangodfrey

            I suspect that the examples you give are misleading, and the true data would match Phil's speculation.  If so, it is another example of why it is usually better to give real-life descriptions and not abstract concepts.

            • 3. Re: Perform Find not working as expected
              hangry

              Actually this is real-life data. Changing the operator in the Find from = to == makes it work correctly. Thank you for your help.

              • 4. Re: Perform Find not working as expected
                philmodjunk

                Yet something is different in your DB from what you report or I would not be getting different results.

                • 5. Re: Perform Find not working as expected
                  alangodfrey

                  Phil tested your steps as described, and couldn't replicate your result.  His results are exactly as we would have expected.  And adding '==' is exactly the solution to stop a 'Building CC' record being found with a 'Building C' record.

                  In other words it looks like something else is going on, which may come back to surprise you later.

                   

                  Search for (no quote marks):

                  'building a' will return records with 'building a', 'building a2', 'building abcdef123' and 'a building'

                  'build a' will return records with 'building a', 'building a2', 'building abcdef123' and 'a building'

                  'a build ' will return records with 'building a', 'building a2', 'building abcdef123' and 'a building'

                  'ab build' will return records with 'building abcdef123'

                  '=ab build' will return records with 'building ab' and 'ab building'