4 Replies Latest reply on Jul 6, 2012 6:27 AM by philmodjunk

    How do I create a search for whether a field "contains" a value rather than just "matching"?

    AM_1

      Title

      How do I create a search for whether a field "contains" a value rather than just "matching"?

      Post

      I have a search form in find mode. I want to be able to enter a search value in a field and for records to be found depending on whether they "contain" that value rather than just "match" the value. I know I could enter the value surrounded by wild cards (* value *) but I'm setting up the database for other users and want this to happen automatically.

      Is there some way of changing the behaviour of a seach field to this, or is there some other simple and elegant way to do this? I'm sure it is a common requirement but I haven't been able to find an answer so far.

       

      Thanks for any help.

        • 1. Re: How do I create a search for whether a field "contains" a value rather than just "matching"?
          philmodjunk

          I think your best bet, if you don't want your users to enter their own find operators (ie. wildcards) is to set up a  layout with global fields where they enter or select criteria and then a script performs the find, inserting operators as appropriate to get the desired results.

          Scripts are often used in FileMaker solutions as a way to simplify the query process in order to make it more user friendly.

          Here's a greatly simplified example. Say you have a global field named Globals::gName and you want to use the data in it to perform a search where the text entered is enclosed in a pair of * operators.

          Go to Layout [specify a layout based on table being searched]
          Enter find mode [] //clear the pause check box
          Set field [Yourtable::yourNameField ; "*" & Globals::gName & "*"]
          Set Error capture [on]
          Perform Find []

          • 2. Re: How do I create a search for whether a field "contains" a value rather than just "matching"?
            AM_1

            Thanks PhilModJunk,

             

            I'll give your solution a go.

            • 3. Re: How do I create a search for whether a field "contains" a value rather than just "matching"?
              AM_1

              PhilModJunk

               

              Yup, that worked very nicely thank you.

               

              I don't quite understand about the global field bit nor the "set error capture [on] "  but as I understand tne script basically adds wildcards around the entered data value and then resubmits it for the search.

              • 4. Re: How do I create a search for whether a field "contains" a value rather than just "matching"?
                philmodjunk

                Without set error capture, a find that finds no records or that lacks any valid search criteria will cause the script to be interrupted by an error message that doesn't really fit for scripted finds. This step suppresses that message. If you want you can use Get ( LastError ) immediately after the find is performed to check for and identify find errors and use your own custom dialogs to handle them.

                You can also use Get ( FoundCount ) to test for no records found and display a custom dialog that no records are found.

                With the script written the way it is. Any field used for entering search criteria must have global storage enabled. Try this simple experiment: Put a global and a non global field on a layout. Enter data into each. Enter find mode. Which field goes blank? Which still displays the data entered into it? Only the global fields still have their data accessible when in find mode and this is also true for your script.

                THere are two other advantages for global fields: 1) In a multi-user environment, each user's changes to values in global fields are not visible to other users and thus they each can perform different searches without interfering with each other. 2) Using a global field ensures that no user can accidentally modify data when they thought they were entering search criteria as theses fields will be separate from your data fields.