5 Replies Latest reply on May 21, 2012 2:29 PM by alexjeane

    Use Global Text Field to Find Records



      Use Global Text Field to Find Records


      We have a list of email addresses that need to be searched for in one of our databases.  Instead of going through one by one, I've heard there is a way to set up a global text field, put in a list of email addresses into it, and through some magic of scripting search through that field for all records with that contain one of those email addresses.

      I am mostly confused on how to set this all up.  I have a global text field, but not sure what exact sort of script is necessary to search through that field and compare it with another.

      Would anyone have an idea of where to start?  Bear in mind, I am just beginning to learn FileMaker and still learning all the appropriate terminology.

        • 1. Re: Use Global Text Field to Find Records

          Here's a possible script. Please read the notes that come after and feel free to use Post A Answer to post commetns or ask any follow up questions about the script that you may have:

          Say you have a global field named: YourTable::gEmailAddressList--a text field where there is a return separating each address in the list in this field. Furthermore, there are two ways to read your description of this problem.

          1) You might want a script that finds records with the first address, does somethihg with them, then finds reocrds with the next listed address,  does something with them and so forth....

          2) You might want to pull up a single set of records--called a found set--of all records that contain the first listed email addres OR the second email address OR the third... Etc.

          The script used would be slightly different for each option. I'll assume 2) for this response.

          Go to layout [//select a layout based on the table you want to search for these email addresses]
          Enter Find Mode [] // clear the pause check box
             Set variable [$K ; value: $K + 1 ]
             Set Field [Yourtable::EmailAddress ; Quote ( GetValue ( YourTable::gEmailAddressList ; $K ) ) ]
             Exit Loop If [ $K > valuecount ( YourTable::gEmailAddressList ) ]
             New Record/Request
          End Loop
          Set Error capture [on]
          Perform Find []


          1. gEmailAddressList must have global storage specified in field options in order for this script to work. It can be defined in any table in your file--even a special table reserved for global fields.
          2. In Find Mode, New Record/Request creates a new request instead of a new record and set field is entering search criteria into this request. It is not modifying data in your database. It is setting up a search to find records with Email address #1 OR Email Address #2 Or ...
          3. The @ symbol in email addresses is also used in FileMaker as a special operator in find requests. The Quote function used here encloses the email address in quotation marks so that the @ symbol is not interpreted by Filemaker as that special find operator but as the actual @ symbol.
          4. Set Error Capture [on] Keeps an error dialog from interrupting your script if no records are found. You may want to add an If step after the perform find that uses Get ( FoundCount ) to see if any records are found. You can then use Show Custom dialog to display a message to the user if you want when no records are found. You may also want to remove or disable this step during testing so that you can see that error dialog as it may help you figure out why the find failed for you.
          5. Please look up any functions used in this script in Filemaker Help to learn more about them if they are unfamiliar to you.
          • 2. Re: Use Global Text Field to Find Records




            I'm playing around with the script and its expecting an operator in the line


            [Yourtable::EmailAddress ; Quote ( GetValue ( YourTable::gEmailAddressList ; $K ) ) ]


            specifically in the Quote section.  Would there be any common pitfalls on my end that might lead to this error?

            • 3. Re: Use Global Text Field to Find Records

              Check your parenthesis. This is a common error message if you have one missing, an extra one or one in the wrong location.

              Reviewing the above expression, I don't see any such error in the above post, but you may have one in your version of the same.

              • 4. Re: Use Global Text Field to Find Records

                Also, please review these instructions:

                When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                • 5. Re: Use Global Text Field to Find Records

                  Yep, those Specify buttons confused me.  Once you explained that, it all worked wonderfully.  Thank you!