7 Replies Latest reply on Nov 4, 2010 10:31 AM by philmodjunk

    Searching In Filemaker Pro 11

    RonShelton

      Title

      Searching In Filemaker Pro 11

      Post

      I am having some issues doing a search in Filemaker Pro, in regards to doing a search using logical operators like in MS Access 2007.

      I need to do a search (recently converted Access 2007 database), that will get data from one filed, then have another search take those results and refine them with another field.

      Example would be a membership list (multiple years).  Find the memberships that active in 2010, then keep those results and also find the renewals in 2011 and display both data as a final results.

      This would show members in 2010 and renewals for 2011 in the final output.

      Thanks in Advance for steering me in the right direction on this.

      Ron

        • 1. Re: Searching In Filemaker Pro 11
          philmodjunk

          Several approaches are possible and can be done by hand or in a script:

          1) specify criteria for 2010 membership. Create a new request (new request in request menu while in find mode). Specify criteria for 2011 membership. Perform find.

          2) Specify criteria for active members. Enter 2010...2011 in year field. Perform find.

          3) Specify criteria for first find and perform it.

          4) enter find mode and specify criteria for second search, but use Extend Found Set to add these records to the results of the first find.

          • 2. Re: Searching In Filemaker Pro 11
            RonShelton

            Thanks PhillModJunk,

            Couple of more questions on the same issue.

            Each year has it's own field all the way back to 1992.  Currently there is either a xmark (piror to 2002) and the actual date of renewal (post 2002) saved in a text mode (not date).  So a date range over those years does not work in it's current form, unless we change the type? (on Parts 1 & 2) or structure.

            On the second half, I did number #3.  When I did #4, I get the error:  "The provided find criteria is not valid.  Enter a valid request before proceeding".

            Looks like it ignored the first set of results.

            Thanks in advance.

            Ron.

            • 3. Re: Searching In Filemaker Pro 11
              philmodjunk

              I just did a quick manual test on my Known Bugs List database that I share out to interested parties.

              1. I entered find mode and typed the word "list" into a text field and performed the find--getting 22 records.
              2. I entered find mode again and typed "Menu" into the same field. Instead of clicking Perform Find, I select Extend Found set from the requests menu.
              3. I now have 41 records which contain either list or menu in the text field where I entered this criteria.

               

              If you have any date data stored in text fields, I strongly recommend that you convert them into a date if at all possible. This makes them much easier to work with.

              If the date info is in a valid format, you can simply change the field type to date.

              • 4. Re: Searching In Filemaker Pro 11
                RonShelton

                Would that also apply to the pre-2002 data that was only represented by a X in the field rather than the actual data?

                Ron

                • 5. Re: Searching In Filemaker Pro 11
                  RonShelton

                  After performing the change on the database, still the same number of results.  Using all of the instructions as you outlined above.

                  I'm clueless on what's happening here.

                  Thanks in Advance.

                  Ron

                  • 6. Re: Searching In Filemaker Pro 11
                    RonShelton

                    Phil,

                    Since I downloaded your database and love the design, it it possible you can revamp mine to look like yours.  Since I know that you also run a consulting company, is it possible to get a quote for work on redesign & tuning up the database?  I can upload it to you private address if you need to see what it looks like.

                    Thanks in Advance,

                    Ron

                    • 7. Re: Searching In Filemaker Pro 11
                      philmodjunk

                      Your data should have a consistent  format. If you are searching for dates or a range of dates, this information should be in a date field with a valid date. If it isn't, this is something you should fix before attempting such a search.

                      One way to do such a batch update of your data, is to use Replace Field Contents. This is a tool you can look up in FileMaker help. Since it changes the value of a specified field for every record in your found set, it's a very good idea to save a back up copy just before trying out this method so you can toss the file and start over if you get results you didn't expect.