10 Replies Latest reply on Jul 18, 2013 10:12 AM by philmodjunk

    Best way to input search criteria in multiple fields?

    CarrieB.

      Title

      Best way to input search criteria in multiple fields?

      Post

           I'm trying to figure out the best way to do this ......

           User enters a date or date range in find mode and I would like it to find results for that date if it occurs in any of 3 different fields.

           Any advice is appreciated!

        • 1. Re: Best way to input search criteria in multiple fields?
          symbister

               enter Find Mode, enter the criteria in your first field, then 'Requests|Add New Request', then enter criteria into second field, then 'Requests|Add New Request', then enter criteria into third field - (this sets up an OR find) - then Perform Find

               all of which could be easily scripted if you're up to it..

          • 2. Re: Best way to input search criteria in multiple fields?
            CarrieB.

                 I would like to script it, but I can't quite figure out the right way to do it. I have no problem doing it manually, but would like my users to be able to just enter a date and have the script do the rest. Any specific instructions would be extremely helpful. Thanks!

            • 3. Re: Best way to input search criteria in multiple fields?
              symbister

                   Hi Carrie

                   Screenshot of a script that works for me...

                   Notes:
                   report_start_date and report_end_date are global date fields in your data table, which are used as the input fields in the Show Custom Dialog script step

                   for your search, you will need to include the lines:

                   Set Field [yourdatefieldA ; $don_search]
                   Add New Record/Request
                   Set Field [yourdatefieldB ; $don_search]
                   Add New Record/Request
                   Set Field [yourdatefieldC ; $don_search]

                   before the Perform Find script step

                   (ignore donation amount - but note that you can easily use a similar field for a third parameter)

                   happy scripting!

                    

              • 4. Re: Best way to input search criteria in multiple fields?
                CarrieB.

                     Ok. I just want to make sure I am doing this the best way. Right now, when my user wants to create a certain report, they click a button which takes them to a new layout and puts them in Find mode. On this page, they have 3 different fields they can put search criteria in. They can choose 1 or all of these fields. Depending on which button they choose at the end, it completes the Find Request and creates the report. Field B is my date field, that I want to search across 3 different fields. Can I use a global field for Field B while already in Find mode or should I make this layout in Browse Mode with all global fields and run all the Find requests via scripting after the user enters the criteria into the global fields?

                     Thanks!!

                • 5. Re: Best way to input search criteria in multiple fields?
                  CarrieB.

                       Hi again. I am working on this script as you posted it above and ran into a question. For my date search across 3 fields, I want results if it occurs in ANY of the 3 fields, not if it occurs in ALL of the 3 fields. Does this make a difference when scripting? Do I need to tell it to extend the found set? When I try to specify the find requests, it says "AND". Also, my "Enter Find Mode" is in a different script than my "Perform Find", as there is a window that gives the user multiple choices. Will this affect my use of the variable?

                       Thanks again!!

                  • 6. Re: Best way to input search criteria in multiple fields?
                    CarrieB.

                         I thought I had it, but I must still be doing something wrong. My first script looks like the image below. On Enter Find Mode, I specified the requests to enter the variable into each 3 date fields. (2nd image) Then the user goes back to the layout where they can enter more criteria into Field 1 and 2. The user has a couple button choices at the bottom of the screen which run a script that Performs the Find and takes them to another layout. However, the Find ignores the criteria they enter outside of the Custom Dialog Box. The date search worked great, but I need to figure out how to constrain the find to the rest of the search criteria entered.

                         Any thoughts? Thanks!

                          

                    • 7. Re: Best way to input search criteria in multiple fields?
                      CarrieB.

                           Oops. Lost the first image. Here it is.

                      • 8. Re: Best way to input search criteria in multiple fields?
                        CarrieB.

                             I figured it out! Just needed to constrain my Find with the global parameters for dates. Now I need to address an entirely new issue. Once I get my find results, there are Found Count totals at the bottom of the window. Since I am searching for those dates across 3 fields, I only want the found count for each field to reflect the records that fall in that date range, instead of the total number of records found. Is there a way to do this?

                             Thanks again!! Sorry for the confusion and multiple postings!

                        • 9. Re: Best way to input search criteria in multiple fields?
                          CarrieB.

                               I'm still having issues making this work simply. My biggest stumbling block seems to be the fact that I want the dates to go across 3 fields, but I also have other search criteria available. When I use the "new request" function, the search ignores the other criteria. I'm sure there is an easy way to fix this, but I'm not seeing it. Basically between the entering of Find Mode and performing the Find, there are 2 other fields that need to be set with user entered data. I tried running the Find first, then constraining the results with the date request, which seemed to work. Until I tried it on a machine running FileMaker 10. It doesn't constrain the results at all. Can someone help me with the correct scripting here? Thanks!!!

                          • 10. Re: Best way to input search criteria in multiple fields?
                            philmodjunk

                                 Multiple requests means you have "Or" criteria: Find the records matching criteria in request 1 or in requet 2 or in request....

                                 Specifying criteria in multiple fields fo the same criteria means ;you have "and" criteria. Find the records where name = "smith" , date is in range: 7/1/2013...7/30/2013 AND Job Description = "administrator"...