13 Replies Latest reply on Nov 23, 2011 6:00 AM by user16037

    "Advanced Search" part 2

    user16037

      Title

      "Advanced Search" part 2

      Post

       ref:

      Creating "Advanced Search" Layout
      Forum post posted September 23, 2011 by mat curtis
       
      I have been trying to create an advanced search page to search a library catelogue. The issue that I am having trouble with is to add a series of operators in to create "And", "Not" and "Or" searches.
       
      The following script that PhilModJunk devised, works very well to create searaches based on a single text input and the user choosing which field the criteria relates to. This gives an "Or" search if two or more fields are chosen. I would like to expand that by adding another Value list that allows the user to perform searches in other fields that create an add, include or omit search criteria from the found set.
       
      Thanks for your help
       
      • If [ ValueCount ( global fields table::SearchFieldsList ) ]
      • Go to Layout [ “Record View” (IMarEST test db) ]
      • Enter Find Mode [ ]
      • Loop
      • Set Variable [ $I; Value: $I + 1 ]
      • Set Field By Name [ "IMarEST test db::" & GetValue ( global fields table::SearchFieldsList ; $I ); global fields table::SearchText ]
      • Exit Loop If [ $I = ValueCount ( global fields table::SearchFieldsList ) ]
      • New Record/Request
      • End Loop
      • Pause/Resume Script [ Indefinitely ]
      • Set Error Capture [ On ]
      • Perform Find [ ]
      • If [ Get ( FoundCount ) = 0 ]
      • Go to Layout [ “Record View” (IMarEST test db) ]
      • Show Custom Dialog [ Title: "No Record Message"; Message: "The were no records matching these criteria found"; Buttons: “OK” ]
      • Else
      • Sort Records [ Specified Sort Order: IMarEST test db::Subject; ascending ] [ Restore; No dialog ]
      • End If
      • Else
      • Show Custom Dialog [ Title: "No Field error"; Message: "No field was selected for these search criteria"; Buttons: “OK”, “Cancel” ]
      • End If

        • 1. Re: "Advanced Search" part 2
          philmodjunk

          What is the difference between "add" and "include"?

          For omit this snippet of code generates an Omit type request:

          #should already be in find mode....
          New Record/Request
          Set Field [//specify your omit criteria here]
          Omit Record
          Set Error Capture [on]
          Perform FInd[]

          This is the same as entering criteria by hand and clicking the Omit button. The main thing to be careful of is to make this the last request of those generated by your script. FileMaker processes Omit requests by comparing the specified criteria to those records found by the other requests and omits those that match so it has to be the last request. (And you can have more than one "omit" request as long as they are all after the other requests.)

          • 2. Re: "Advanced Search" part 2
            user16037

             Ah! that was meant to be "and" ie a search based on two elements to create a single criterium, as opposed to a "or" search.

            mc

            • 3. Re: "Advanced Search" part 2
              philmodjunk

              For an And search, just leave out the New/Record request steps. Criteria entered all in the same "request" is "and" based. Put it in separate requests and you get "or" logic.

              "include" as in keep your current found set, but add to it any additional records matching the specified critieria?

              If so keep to your existing scripted methods, but use Extend Found Set[] instead of Perform Find[].

              (And you can also use Constrain Found Set to omit records from your existing found set.)

              • 4. Re: "Advanced Search" part 2
                user16037

                 OK may not have expalined myself well. The "AND" "omit" and "include" operations are chosen by the user in a drop down menu. I am trying to work out how to determine the action after the choice has been made.

                i.e. if IMarEST test db::operator = "AND" then link the two search fields in a single request; "OR" then search the two fields by two criteria (as above); or "NOT" then to omit the records that have the second criterium.

                mc

                 

                • 5. Re: "Advanced Search" part 2
                  philmodjunk

                  Use If steps to control what steps actually execute to build the series of Find Requests with search data.

                  You can thus put the New Record/Request step inside an if step like this:

                  If [IMarEST test db::operator = "OR" ]
                     New record/Request
                  End IF

                  Then the criteria are kept on the same request when "AND" is specified, but put in separate requests when "OR" is specified.

                  • 6. Re: "Advanced Search" part 2
                    user16037

                     

                    • Dear PhilModJunk,

                    Thanks for the previous help. It has taken me a long way to my final goal.

                    The script below allows me to generate finds for all combinations of fields and finds that I need except one - a NOT followed by an AND. As you can see from the image below I have two operators "Operator 1" and "Opertator 2" with the same options in each AND NOT and OR. For the NOT followed by AND I have to go through three search requests and a find before I omit the two criteria set. As you can see I have tried to put in a clause script to by-pass the other steps but that does not seem to work. I realise that I could take away the two superfluous searches and then omit the joint search but that seems a very long way around. Where am I going wrong? In addition is there any "smoothing-up" I can do on my current script?

                    Thanks for your help,

                    Mat

                     

                    If [ ValueCount ( global fields table::SearchFieldsList ) ]

                    • Go to Layout [ “Record View” (IMarEST test db) ]
                    • Enter Find Mode [ ]
                    • Set Variable [ $I; Value:$I + 1 ]
                    • Set Field By Name [ "IMarEST test db::" & GetValue ( global fields table::SearchFieldsList ; $I ); global fields table::SearchText ]
                    • If [ global fields table::Operator 1 = "OR" or global fields table::Operator 1 = "NOT" ]
                    • New Record/Request
                    • // Else If [ global fields table::Operator 1 = "NOT" and global fields table::Operator 2 = "AND" ]
                    • // Perform Script [ “NOT and AND” ]
                    • End If
                    • If [ ValueCount ( global fields table::SearchFieldsList 2 ) ]
                    • Set Field By Name [ "IMarEST test db::" & GetValue ( global fields table::SearchFieldsList 2 ; $I ); global fields table::search text 2 ]
                    • If [ global fields table::Operator 2 = "OR" ]
                    • New Record/Request
                    • Else If [ global fields table::Operator 2 = "NOT" ]
                    • New Record/Request
                    • End If
                    • If [ ValueCount ( global fields table::SearchFieldsList 3 ) ]
                    • Set Field By Name [ "IMarEST test db::" & GetValue ( global fields table::SearchFieldsList 3 ; $I ); global fields table::search text 3 ]
                    • End If
                    • End If
                    • Set Error Capture [ On ]
                    • Perform Find [ ]
                    • If [ global fields table::Operator 1 = "NOT" ]
                    • Enter Find Mode [ ]
                    • If [ ValueCount ( global fields table::SearchFieldsList 2 ) ]
                    • Set Field By Name [ "IMarEST test db::" & GetValue ( global fields table::SearchFieldsList 2 ; $I ); global fields table::search text 2 ]
                    • Omit Record
                    • End If
                    • If [ global fields table::Operator 2 = "NOT" ]
                    • New Record/Request
                    • If [ ValueCount ( global fields table::SearchFieldsList 3 ) ]
                    • Set Field By Name [ "IMarEST test db::" & GetValue ( global fields table::SearchFieldsList 3 ; $I ); global fields table::search text 3 ]
                    • Omit Record
                    • End If
                    • End If
                    • End If
                    • Set Error Capture [ On ]
                    • Constrain Found Set [ ]
                    • Go to Layout [ “Find results” (IMarEST test db) ]
                    • If [ Get ( FoundCount ) = 0 ]
                    • Show Custom Dialog [ Title: "No Record Message"; Message: "There were no records found matching these criteria"; Buttons: “OK” ]
                    • Else
                    • Sort Records [ Specified Sort Order: IMarEST test db::Subject; ascending ] [ Restore; No dialog ]
                    • End If
                    • Else
                    • Show Custom Dialog [ Title: "No Field error"; Message: "No field was selected for these search criteria"; Buttons: “OK”, “Cancel” ]
                    • End If
                    • 7. Re: "Advanced Search" part 2
                      philmodjunk

                      First thing that jumps out at me is that we no longer have a list of fields in a single field. We have three fields that are either empty or contain just one field.

                      This means we no longer have to make the extra effort to extract field names from a list of the same. We can just refer directly to the global text field.

                      Set Field By Name [ "IMarEST test db::" & GetValue ( global fields table::SearchFieldsList ; $I ); global fields table::SearchText ]

                      can become:

                      Set Field By Name [ "IMarEST test db::" & global fields table::SearchField; global fields table::SearchText ]

                      I'd also consider setting up the searchFields and operator tables with either radio button or pop up menu formats instead of a drop down list, but that's an adjustment that I think will make this easier for the user--it won't affect how the script runs--except to more clearly limit the user to single choices in each field.

                      Omit type requests do have to be placed at the end of all other requests, so including a not operator does complicate the process. Let's see if we can simplify some other factors first before tackling that issue. I suspect we may need to build a list of operators, field names and search criteria sorted to make sure that the "not" criteria are listed last in this list before entering find mode and creating the find requests.

                      • 8. Re: "Advanced Search" part 2
                        user16037

                         

                        Dear PhilModJunk,

                        Cleaned up the script as advised. I completely take on board your suggestions regarding the radio buttons for the field formats but the users are used to the drop down option as used in the original version that I am trying to replicate as much as possible.

                        When you say " I suspect we may need to build a list of operators, field names and search criteria sorted to make sure that the "not" criteria are listed last in this list before entering find mode and creating the find requests" do you mean all the possible combinations of "AND", "OR" and "NOT" with the field choices?

                        Thanks very much,

                        Mat

                         

                         

                        • If [ ValueCount ( global fields table::SearchFieldsList ) ]
                        • Go to Layout [ “Record View” (IMarEST test db) ]
                        • Enter Find Mode [ ]
                        • Set Field By Name [ "IMarEST test db::" & global fields table::SearchFieldsList; global fields table::SearchText ]
                        • If [ global fields table::Operator 1 = "OR" or global fields table::Operator 1 = "NOT" ]
                        • New Record/Request
                        • End If
                        • If [ ValueCount ( global fields table::SearchFieldsList 2 ) ]
                        • Set Field By Name [ "IMarEST test db::" & global fields table::SearchFieldsList 2; global fields table::search text 2 ]
                        • If [ global fields table::Operator 2 = "OR" or global fields table::Operator 2 = "NOT" ]
                        • New Record/Request
                        • End If
                        • If [ ValueCount ( global fields table::SearchFieldsList 3 ) ]
                        • Set Field By Name [ "IMarEST test db::" & global fields table::SearchFieldsList 3; global fields table::search text 3 ]
                        • End If
                        • End If
                        • Set Error Capture [ On ]
                        • Perform Find [ ]
                        • // If [ global fields table::Operator 1 = "NOT" and global fields table::Operator 2 = "AND" ]
                        • If [ global fields table::Operator 1 = "NOT" ]
                        • Enter Find Mode [ ]
                        • If [ ValueCount ( global fields table::SearchFieldsList 2 ) ]
                        • Set Field By Name [ "IMarEST test db::" & global fields table::SearchFieldsList 2; global fields table::search text 2 ]
                        • Omit Record
                        • End If
                        • If [ global fields table::Operator 2 = "NOT" ]
                        • New Record/Request
                        • If [ ValueCount ( global fields table::SearchFieldsList 3 ) ]
                        • Set Field By Name [ "IMarEST test db::" & global fields table::SearchFieldsList 3; global fields table::search text 3 ]
                        • Omit Record
                        • End If
                        • End If
                        • End If
                        • Set Error Capture [ On ]
                        • Constrain Found Set [ ]
                        • Go to Layout [ “Find results” (IMarEST test db) ]
                        • If [ Get ( FoundCount ) = 0 ]
                        • Show Custom Dialog [ Title: "No Record Message"; Message: "There were no records found matching these criteria"; Buttons: “OK” ]
                        • Else
                        • Sort Records [ Specified Sort Order: IMarEST test db::Subject; ascending ] [ Restore; No dialog ]
                        • End If
                        • Else
                        • Show Custom Dialog [ Title: "No Field error"; Message: "No field was selected for these search criteria"; Buttons: “OK”, “Cancel” ]
                        • End If
                        • 9. Re: "Advanced Search" part 2
                          philmodjunk

                          An alternative to the radio button format that also implies a single selection per field format is a Pop up menu. Some developers avoid this option as they don't like the "blocky" appearance you get with it, but this appearance can be hidden by specifying 0 width or transparent borders for the field.

                          I'm leaning towards taking the data from the global fields and producing a list of user specified criteria an operators, sorted so that "not" criteria is listed last. This way, we can loop through the user specified criteria and the "not" criteria will then be automatically created last.

                          My apologies for not thinking this all the way through, but today I see that it might actually return your script to it's original list of criteria, list of field names format, but with a third list, the operators included.

                          I'm thinking of code similar to this to build the lists:

                          Set Variable [$$CriteriaList ; value: global fields table::SearchText ]
                          Set variable [$$OperatorList ; value : "AND" ]
                          Set Variable [$$FieldList ; value: "IMarEST test db::" & global fields table::SearchField ]
                          If [global fields table::operator = "Not" ]
                            Set variable [$$CriteriaList ; value: List ( $$CriteriaList ; Global fields table::SearchText 2) ]
                            Set variable [$$OperatorList ; value : List ( $$OperatorList ; "NOT" ) ]
                            Set Variable [$$FieldList ; value: List ( $$FieldList ; "IMarEST test db::" & global fields table::SearchField 2 ) ]
                          Else
                            Set variable [$$CriteriaList ; value: List ( Global fields table::SearchText 2 ; $$CriteriaList ) ]
                            Set variable [$$OperatorList ; value : List ( global fields table::operator ; $$OperatorList ) ]
                            Set Variable [$$FieldList ; value: List (  "IMarEST test db::" & global fields table::SearchField 2 ; $$FieldList ) ]
                          End If

                          Do the same for the last set of criteria, operator and field name fields. If the "NOT" operator is specified, append your data to the end of these lists. If AND/OR are specified, append to the beginning. As written the code may mess things up if a field is left empty so we may need to add additional terms to the set variable steps to ensure that the terms inside the List function are never empty.

                          • 10. Re: "Advanced Search" part 2
                            user16037

                             Hi PhilModJunk,

                            I have a side question to ask:

                            The "Save a Copy as" function is greyed out. I have looked at a forum post with the same question but do not understand the answer. I am having wranglings with our IT department to see if it is a server access issue or something within FM. In the application (pro advanced 11) I have full access as the administrator but I am unable to copy or delete files from the server. also the launch PHP assistant is greyed out if it is relevant.

                            Thanks,

                             

                            Mat

                            • 11. Re: "Advanced Search" part 2
                              user16037

                               joshonefive

                              "Save a Copy As..." greyed out, why?Answered
                              Forum post posted January 29, 2009 by joshonefive
                               
                              Here is the original post. mc
                              • 12. Re: "Advanced Search" part 2
                                philmodjunk

                                You cannot use save a copy as from a file hosted on a server.

                                If the IT department has given you access to the server admin application that controls fileMaker server you can use a schedule to make a back up copy of the file and you can then pull that copy across the network to your machine where you can open the copy and work with it.

                                If the file is hosted by FileMaker Pro, you can use remote desktop access to access the server, and use save a copy as from that machine's copy of FileMaker Pro.

                                • 13. Re: "Advanced Search" part 2
                                  user16037

                                   Thanks for that. Very helpful.