7 Replies Latest reply on Aug 10, 2012 6:21 AM by Oliver_Reid

    How to perform an AND find searching in one field

    rbogdanoff

      I have a layout of people with a portal to a linked table showing all individual donations for each person. I want to search in the DonationType field to find all people who have made donations to two different programs. If I do a Find and enter "program 1" in the DonationType field, then add a new Find criteria and enter "program 2" in the DonationType field, it does an OR search. How can I do an AND find in this situation, to find only those people who gave to both programs?

        • 2. Re: How to perform an AND find searching in one field
          richardsrussell

          Every table I create has a bunch of standard fields that I ALWAYS want to have available. One of them is "Tag", a number field with validation requiring it to have values only from the value list "One", which contains a single value (guess what). The "Tag" field appears on the data-entry screen for each table, formatted as a checkbox based on value list "One". Do likewise with your table of people.

           

          Start by doing a Find for any "Tag" boxes with check marks. (If you've just created it, skip this step.) If you turn any up, use the "Replace Field Contents..." command under the "Records" menu (or its command-key equivalent, ⌘-=) to replace the contents of "Tag" with "" (nothingness). That's to make sure you don't have any left over from your previous Find operation (IE, you're starting clean).

           

          Do a Find for donations to Program 1. Use "Replace Field Contents..." to check all the "Tag" checkboxes in that found set. Then do a Find for donations to Program 2 AND a check in the "Tag" box. Voilá!

          • 3. Re: How to perform an AND find searching in one field
            comment

            Richard S. Russell wrote:

             

            Use "Replace Field Contents..." to check all the "Tag" checkboxes in that found set. Then do a Find for donations to Program 2 AND a check in the "Tag" box. Voilá!

             

            I am afraid your suggestion is an invitation to disaster in a multi-user situation.

            • 4. Re: How to perform an AND find searching in one field
              richardsrussell

              Well, as I said, I include the "Tag" box as a standard feature in every table I create for every file I make, and I've got 'em installed at a couple of dozen clients, each with its own unique set of files. So far no complaints and no disasters. On the contrary, several folx have said how much they appreciate the feature. It DOES come with a little "?" explanation button right next to the "Tag" box which contains my caveat that "Tag" is intended for TEMPORARY use only, and you should never count on it having the same value in it that it had last time you used it. Unless you're dealing with complete idiots, people can live with this.

               

              Of course, none of these outfits has more than a dozen employees. If you're talking hundreds, you might want to set up separate "Tag" boxes for various subgroups within the organization, on the assumption that they'll talk to each other.

              • 5. Re: How to perform an AND find searching in one field

                Find using the first criteria. Enter find mode and add second criteria, but use the Constrain Found Set option instead of Perform Find.

                 

                One of the most under-used features of FMP, and it frequently helps speed up some searches (at least in previous versions, not sure now.) This can also be scripted. I've used this same method with a table housing 2 Million records with a looping script that sets values and constrains along the way.

                • 6. Re: How to perform an AND find searching in one field
                  comment

                  Richard S. Russell wrote:

                   

                  Well, as I said, I include the "Tag" box as a standard feature in every table I create for every file I make, and I've got 'em installed at a couple of dozen clients, each with its own unique set of files. So far no complaints and no disasters.

                   

                  It's only a matter of time...

                   

                  For one thing, you cannot tag a record that anoher user happens to be editing. So that record will be omitted in the second find, regardless of whether it matches the second request or not. For another, if two (or more) users are performing a search at roughly the same time, they will conflict. Tagging records is simply something that is not done. Not to mention that a much simpler solution exists.

                  1 of 1 people found this helpful
                  • 7. Re: How to perform an AND find searching in one field
                    Oliver_Reid

                    Yes this is what Constarin found set" is designed for.