3 Replies Latest reply on Jan 7, 2014 8:35 AM by philmodjunk

    Autofilters like excel

    Vicky

      Title

      Autofilters like excel

      Post

           Hi,

           Is there any way to create autofilters like excel's where a user can start filtering in any field. After the first filter, all the other fields show the related information and users can choose to narrow the results even more by filtering the other fields.  Users can also broaden the results by removing the filters from the fields they want.

           For example, if I were to classify plants by type, color, and name

      Fields - records

      type - fruit, leaf, tubers, stem, stem

      color - purple, green, orange, yellow, red, light green

      name - spinach, lettuce, carrot, eggplant, cherry, tomato, potato, asparagus, napa cabbage

            In excel I would be able to start filtering in any of the three fields.  

           If I choose the color green, I would get 

                                                                                                                                                                                                                                                                                                         
                          type                     color                     name
                          leaf                     green                     spinach
                          leaf                     green                     lettuce
                          stem                     green                     asparagus

           Then I can choose type leaf to narrow it down more

                                                                                                                                                                                                                                   
                          type                     color                     name
                          leaf                     green                     spinach
                          leaf                     green                     lettuce

           I can also broaden the search by removing the color filter while leaving the type filter

                                                                                                                                                                                                                                                                                                         
                          type                     color                     name
                          leaf                     green                     spinach
                          leaf                     green                     lettuce
                          leaf                     light green                     napa cabbage
                           

           Is there any way to have filtering like this in filemaker?  I tried to use conditional value lists, but it does not seem as flexible since the user must filter in a certain order.

        • 1. Re: Autofilters like excel
          philmodjunk

               In FileMaker you can set up global fields with value lists. Selecting a value in the field can trip a script trigger that performs a script. The script can use the data in the global fields to perform a find or to constrain a found set.

               For examples of scripted finds, see: Scripted Find Examples

               It's also possible to list records in a portal where typing info in a field referenced in the portal's filter causes the listed records in the portal to change to only those records that match.

          https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

               If you are using FileMaker 12 or newer, you can open this file from the File menu to get a copy converted to the newer .fmp12 file format.

          • 2. Re: Autofilters like excel
            Vicky

                 I looked at both of your methods and really liked the scripted finds. After trying to recreate your example, I have two questions.

                 1) Can it also be done using a dropdown list, because it is possible that there might be too many records for a checkbox to be the best option to use.

                 2) Since I'm trying to filter multiple fields, I added the field color to fruits table, attached a script both to fruit and color and both fields were able to filter.  There are some issues with the filters working together that I am still trying to figure out.   But is there any way to make it show all records if the user unchecks all the boxes?

                  

            • 3. Re: Autofilters like excel
              philmodjunk

                   1) yes, this can be done with a drop down list, or any other field format.

                   2) yes, the script can use IsEmpty to check for data in the search fields. If all are empty it can do Show All Records instead of performing the find.