11 Replies Latest reply on Mar 26, 2012 10:36 AM by philmodjunk

    List Filtering

    Vinny

      Title

      List Filtering

      Post

      I have several instances where I would like to present a list (i.e. of customer accounts), and have several filters for the user to apply.  These filters will have defaults (set at layout load).  I also want to have a search bar, with spotlight search capabilities (if not too complex).

      I have developed a List view version of this, but applying all the filters (global fields in pop up menus) is a pain, and the search & filtering seems to have to be applied any time something changes.

      So...I started looking at using a filtered portal.  This works great for filtering, but not so great for the search function.

      With the list view, I can use the find mode method to implement my search (enter find mode, set field, perform find), and it results in the type of find that I want and like ("wor" will include items with "word" or "work", and also handles multiple words.

      Is there a simple way to implement the same search type in a filtered portal as there is with the list view?  I was hoping to avoid using a custom function, but will if I have to.  I don't like the search results that ExplodeMultiKey gives...

       

      Please let me know.

       

      Thanks!!

        • 1. Re: List Filtering
          philmodjunk

          Filtering a found set of records is often done with list or table views of your data. Scripting can make the process simpler for the user and even an automatic process.

          What you describe can also be done with a filtered portal and it no longer requires a multi-key: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

          • 2. Re: List Filtering
            Vinny

            Well, I would actually prefer to use a list view.  But, what I found is that the filtering requires scripting.

            Any time either a filter changes or the search bar changes, I need to re-apply the search AND the filtering in scripts.

            This is ok, but I'm trying to figure out a method to make this script generic so I can re-use it on multiple list layouts from different tables.

            What I ended up having was a generic script that did this, but I had to supply all the filter fields via a script parameter.  This made the script parameter very long and ugly.

            Do you have any examples of using a list view with drop down or pop up filters AND a search bar?

            • 3. Re: List Filtering
              philmodjunk

              I don't have any demos of that type at this time. As a general rule, creating fully "generic" scripts in FileMaker is difficult at best and impossible at worst. Scripts tend to be "layout sensitive" even with the best of efforts to avoid it and your layouts won't be the same from file to file--especially in terms of the table to which they refer via the table occurrence named in "show records from" in layout setup.

              That said, by using GetFieldName in script parameters  to pass table::field name references to the script and using set field by name and getfield, you can do a lot to produce a script that can be moved from file to file with a minimum of post import correction. Said script parameters can even contain a list of multiple items or an expression that uses the Let function to assign values to variables.

              • 4. Re: List Filtering
                Vinny

                Well, I spent a night working on this...

                I learned last night that pop ups can have multiple values.  I like this option for filtering, as it give the capability of a check box type set up.  I personally don't like the check box field from filemaker so I prefer pop ups.

                That being said, my script turned into a nightmare trying to apply all the filters set up on a page.

                I have a search bar, and two filters (will have more filters in the future) on my list view layout.

                I handle changes to the search (using a search button) and/or filters via script triggers (on Modify) and basically:

                1) Show all records

                2) Perform Find with search term, Filter 1, and Filter 2 placed into the fields as desired

                This works great until I have multiple values.  I don't think I can place multiple values in a find operation, so I tried using the "new record" while in find mode in the script to capture all occurrences in a filter.

                For example:

                Filter1 = Customer, Lead

                Filter2 = Active Customer, Inactive Customer

                In order for me to do "Customer OR Lead" and "Active Customer OR Inactive Customer", I would have to create several iterations of records while in find mode....

                I wish there was a way to:

                1) Show All Records

                2) Perform Find on Search Bar text

                3) Constrain Records: "Customer OR Lead", and "Active Customer OR Inactive Customer"

                 

                I also wish I didn't have to re-apply the search term every time I filter if I didn't have to, but there appears to be no way to get back to step #2 once I have applied the constrain on the record set.

                 

                Any ideas?

                Any ideas?

                • 5. Re: List Filtering
                  philmodjunk

                  First of all, you can drop out Show All Records. This step would not be needed for your script as it has no effect on the results produced by the find. We call "new records" created in find mode "new requests". I recommend global fields for your filter values if you have not already set them up this way. Global fields can be directly referenced in Set field steps after entering find mode in order to set up your search criteria.

                  If you have two filtering fields that list multiple values and you need find records with filterField1, value1, filterfield2, value1, filterfield1, value2 OR filterfield2, value2, you can either construct nested loops that generate all the needed requests (Duplicate record will duplicate a request so you don't have to respecify all the criteria.) Or you can perform one find at a time, but use Extend Found Set instead of perform find for all but the first specified value.

                  • 6. Re: List Filtering
                    Vinny

                    Thanks for the tip on not needing to show all records.  I failed to recognize that I didn't need it anymore.

                    Also, thanks for the extend found set idea.  I will implement that.

                    As for the globals - yes, I'm using them.

                    As for the looping - yes, I'm doing that, but I'm finding it to be a real mind bender in order to catch all the possibilities. For example, two filters, with two values selected each would be (shown with array-like notation):

                    Filter1[1] and Filter2[1]

                    Filter1[2] and Filter2[1]

                    Filter1[1] and Filter2[2]

                    Are you saying that with the Extend Found Set feature, I could search on Filter1[1], and then extend found set on Filter1[2], and Filter2[1] and again on Filter2[2]?  In other words, applying Find for Filter1[1] will still hold?  Or conversely, wouldn't this also extend to records that may have Filter1[3], which wasn't a selected value?

                    This would be easier in scripting...at least from the mind bending perspective.

                    • 7. Re: List Filtering
                      philmodjunk

                      Extend found set adds records to those currently present in your found set, so this should simplify the looping process to a single loop through all possible criteria instead of nested loops. The key is to start with either an empty found set or to use the first criteria with perform find and all subsequent criteria "extend found set" operations.

                      Conversely, "constrain found set" only applies criteria to the current records in the found set omitting those that don't match, so this can sometimes be used to apply criteria to unstored or unindexed fields to get a result many times faster than a more standard find.

                      • 8. Re: List Filtering
                        Vinny

                        Ok, but I'm not sure I have the answer to my last question.

                        As an example, I have automotive companies as customers, with Type and Active Status as my two filters.

                        If I want to search/Find for "Ford" in the search box (may have multiple Ford listings), and I filter Type="Lead" and "Customer", and I also filter Activestatus="Active" and "Inactive", then, how would I walk through it?

                        I'm not sure how I would obtain an empty set with no records.  If I do a find with "Ford" int he AccountName field, I'll get all Ford accounts, andwould have to work to constrain the set.

                        Once I figure out how to get an empty set (my layout starts with all accounts listed), then I:

                        1) Extend Found set: Type = Lead AND Type = Customer

                        2) Extend Found set: Active Status = "Active" and Active Status = "Inactive"

                        ???

                         

                        • 9. Re: List Filtering
                          philmodjunk

                          Starting with an empty found set is one option, not the only one.

                          Set Error Capture [on]
                          Enter find mode []
                          Set field [ Yourtable::Field ; "value that you know will never match any record"]
                          Perform find[]

                          Will produce an empty found set. But you can just perform a find for "Ford" as step one and then use extend found set to include records where the type field contains "Lead", the extend for records where type contains "customer", etc. But keep in mind that this insistance on "or" for every filter criterion means that records for "General Motors", but type = "lead" will be included in your found set. That matches what you requested in your original post, but doesn't appear to match what you want in this example.

                          I think you'd want to specify "ford" each time you extend the found set or use it as the final step with a contstrain found set. Either way you'd end up with only records that contain "ford" but then match to on or the other of the rest of your specified search criteria.

                          • 10. Re: List Filtering
                            Vinny

                            Well, as I get more detailed in my explanations I find I'm pulling in new issues - sorry for that.  What I *want* is to have the standard search capabailities (find mode for the search box), and have filters to narrow down the search.

                            BUT, what I'm finding is that the extend, and constrain functions make it very difficult to handle that - especially if my filters have multiple criteria in each.

                            An example is to find all "Ford" customers that are (Filter1) "Leads", or "Qualified Leads", AND (Filter2) are "active" customers.

                            I can easily do a find for Ford and active customers, then extend the set to include Qualified Leads & Leads, but it will also bring in other customers...

                            AND with the extend / constrain features, it's difficult to do a loop to iterate through the possible combinations without ending up with a huge and overly complicated script.

                            I'm surprised this isn't easier than it is.

                            I agree I would need to add ford to the extend step.  I'm going to try to see if I can script it with that, and with a loop of somekind to get through the possible Filter combinations....

                            • 11. Re: List Filtering
                              philmodjunk

                              An example is to find all "Ford" customers that are (Filter1) "Leads", or "Qualified Leads", AND (Filter2) are "active" customers.

                              Perform a find for  "ford", "Leads", "active"

                              A single request can produce the results specified in your example. Or, for a more generalized approach, you can:

                              Perform a find for "ford", "Leads"

                              Extend it to include "ford", "qualified leads"

                              constrain it to "active" customers.

                              I think you need to sit down and work out the way you need your criteria from your filter fields to interact with each other. Which are exclusive? (AND) which are inclusive? (OR)

                              Use extend found set to build up a set of records based on inclusive criteria, then loop through any exclusive criteria to trim down the found set with constrain found set steps to produce your final result.

                              You are not the first to discover that this thpe of multiple criteria, generalized search, is more complex than anticipated. FileMakers "query by example" approach makes simple searches easy to set up and very accessible to new users, but as the complexity of your data searches increases, you reach a point where you find it's no simpler and possibly more complex than just setting up an SQL query like you would use in many other DB applications.