13 Replies Latest reply on Dec 2, 2011 1:14 AM by Fagreement

    Custom Complex Find

    Fagreement

      Title

      Custom Complex Find

      Post

      hi,

      I have to create an advanced search layout:

      the user can do:

      search for a field1 & field2

      search for a field1 or field2

      so I will create the list of operator in a value list (&, or)

       

      but i don't know how to continue:

      - should I create a field for each operator? (let's say that the user can perfor search using many fields: field1 or field2 and field3 ...)

      - once the user compose his query, how can i do the search via the script?

        • 1. Re: Custom Complex Find
          Fagreement

          a value list can display the list of fields related to a certain table?

          • 2. Re: Custom Complex Find
            philmodjunk

            a value list can display the list of fields related to a certain table?

            Sort of. You can use a custom value list to list the fields you want to make available for searching or you can set up a table of values where each record represents one field to be sorted, but setting up a value list that directly lists all the fields defined in a given table, no.

            This value list can contain the actual field names used to define them or more user friendly field names.

            Use global fields for specifying the fields and the criteria to be used in performing the search. A global field for specifying an operator can also be set up. Then your script can enter find mode and use set field and new record/Request to build the needed find requests to perform the specified search with the criteria the user enters and/or selects.

            Let's say you have two fields identified as Name, Address in Manage | Database | Fields.

            You define 5 global fields for the user to use to set up the query of your table, which is called "Contacts".
            gField1, gField2, gOperator, gCriteria1, gCriteria2

            One possible script would be:

            Enter Find Mode[] //clear the pause check box
            If [Not IsEmpty ( Globals::gField ) ]
               Set Field by Name ( "Contacts::" & Globals::gField1 ; Globals::GCriteria1 ]
            End If
            If [Globals::gOperator = "or" ]
                New Record/Request
            End If
            Set Field By name ["Contacts::" & Globals::gField2 ; Globals::gCriteria2 ]
            Set Error capture [on] // keep error dialog from interrupting script if no records are found
            Perform Find[]

            This is just a very simplified example to get you started. It is possible to set up a table for specifying criteria where each record represents one field, the criteria and the operator specified. A script can then use these records to build lists in variables, then enter find mode and generate requests similar to this script but in a loop that steps through the list of values in the variables.

            • 3. Re: Custom Complex Find
              Fagreement

              WAW!

              thanks  PhilModJunk but step by step, it's too complicated at once :-)

              I thought like you, but using global variables not global fields, never mind it's the same, no?

               

              let's say i have a table Document related to Collection and related to other tables also,  and I want to perform this type of advanced search:

              1. these gField, gOperator, gCriteria could be created in a separate table? independant from the "operation" tables?

              2. you said: Set Field by Name ( "Contacts::" & Globals::gField1 ; Globals::GCriteria1 ] i didn't understand it 100%

              3. If [Globals::gOperator = "or" ] => new request ???

              4. and if the If [Globals::gOperator = "and" ]?

               

              thanks you for your usual cooperation

              • 4. Re: Custom Complex Find
                philmodjunk

                I thought like you, but using global variables not global fields, never mind it's the same, no?

                Not quite, while global variables can be used to store criteria etc for a scripted find, they can't be directly edited on a layout by the user which is why I recommended global fields here.

                1) Unless they are used in a relationship, and this is not the case here, they can be defined in any table and will still be accessible from any script and layout in your file. I often define a separate "globals" table just to keep better track of my global fields.

                2) Set Field By Name is a script step that uses a calculation to compute the name of the target field unlike Set Field where you just select the table occurrence from a drop down list and then click the field name to specify it. This should be a text calculation that returns the table occurrence name ("Contacts::") and also the field name (Globals::gField1). Table occurrence names are the names of the boxes found in Manage | Database | Relationships. Most parts of FileMaker that ask you to select a table name are really asking you to select a table occurrence name, which enables FileMaker to determine what relationship, if any to use in evaluating the reference to that table.

                3) when performing a find, you start with what looks like a blank record and enter your search criteria there. This "blank record" is called a "request". If you use a single request and enter all of your criteria into it, you have an "and" type find where you find records that must match all of the criteria you specify. If you create one or more additional requests and enter different criteria into each request, you get an "or" find where you find all records matching the criteria on the first request or that match the criteria specified on the second request.

                When you are in find mode, New record/Request creates a new request instead of a new record.

                4) an "and" search is what you get when you keep all the criteria in one request, so you only create new requests if the user specifies using an "or" operator. Thus, nothing extra is done except for when "or" is specified.

                • 5. Re: Custom Complex Find
                  Fagreement

                  great, thanks.

                   

                  I have a pbm, an always I had it in FM, never found a solution with the list box:

                  gFIELD1 is a list box, display data from GlobalFields Table, the list box search all values from a table named SearcheableFIELDS, value = sFieldName, second field sFieldLongName. the pbm with the actual scenario is that the user when he open the list he saw the sFieldLongName (which is correct) but when he close the list after selecting the item then the field display sFieldName.

                  How can I display the sFieldLongName when he close the list but in the script I want always use the sFieldName of the field so I can search as you told me. It's a question of display

                  thanks

                  • 6. Re: Custom Complex Find
                    mgores

                    If you set it to pop-up menu rather than drop down list it will still display the sFieldLongName, even though the actual value is the sFieldname

                    • 7. Re: Custom Complex Find
                      philmodjunk

                      The other approach (both work, take your pick), is to put the sFieldName field from the related table next to or on top of the drop down list formatted global field. (Obviously, this requires a relationship based on the drop down field.). If you prohibit entry in the inspector's field behavior section for the sFieldName field, you can place it on top of the drop down with an opaque fill color to hide the drop down list. When the user clicks on this field, the drop down list behind it pops to the front and deploys. When the user exits, it returns to its hiding place behind the sFieldName field and the sFieldName field displays the sFieldName value you want them to see.

                      • 8. Re: Custom Complex Find
                        Fagreement

                        ok thanks

                        • 9. Re: Custom Complex Find
                          Fagreement

                          i have the database in SQL server and i USE odbc to connect FM to the database, so the globalfield table (use global storage) doesn't works:

                          is there other workaround?

                          • 10. Re: Custom Complex Find
                            Fagreement

                            following mu previous message:

                            can I use this table locally in FM (create the table gloablField in FM) while keeping the other "operation" tables in SQL server?

                            if yes, I have to create it in every machine (user side) where i want to put the application, no?

                            what's the disadvantage with this solution?

                             

                            thanks

                            • 11. Re: Custom Complex Find
                              Fagreement

                              I'm facing also another issue:

                              when using External Data Source (SQL server and i USE odbc to connect FM to the database,) I cannot see in FM the fields created as Image in Sql Server.

                              in another term, locally (in FM) I can create container field, but using External Data Source i cannot see a field type = image.

                              workaround?

                              • 12. Re: Custom Complex Find
                                philmodjunk

                                I haven't done much with ODBC, but don't see why you can't have a table defined in FileMaker that contains your global storage fields. You could switch to variables, if you have some sort of fields available to store the criteria entered by the user. Your script would copy the criteria from these fields into variables, then enter find mode and set up the criteria for the find.

                                if yes, I have to create it in every machine (user side) where i want to put the application, no?

                                Don't think so, but also am unclear as to how you are setting this up. You have to have a fileMaker file to access the SQL Server back end and I would think you can define additional Filemaker tables in it as needed to get your solution to work.

                                • 13. Re: Custom Complex Find
                                  Fagreement

                                  I have a FM application with SQL server as central database. Diffrent FM clients are conencted to this DB.

                                  when using External Data Source (SQL server and i USE odbc to connect FM to the database,) I cannot see in FM the fields created as Image in Sql Server, the reason is FM doesn't read the binary files from the external database.

                                   

                                  In fact in one of my layout I want to display in a portal a set of images (small images) and the idea is when th user click on a image the related image file will be opened. If I do it using FM database I have no pbm, but with SQL server DB I have an issue.

                                  Knowing that I don't want to create central FM database, as Workaround, how can I proceed?

                                  One idea is to store in SQL server field only the path of the images, until now no pmb. But the issue is how in FM layout I can display the small images in the layout?

                                  hope I was clear

                                  (it's urgent, thanks)