8 Replies Latest reply on Jul 31, 2017 8:03 AM by TomHays

    filtered valuelist with "show all" option

    p528

      Hello experts. I'm stuck on a problem.

      Here is what I like to achieve:

      I have to tables: COUNTRIES & CITIES

      The COUNTRY table has 200 country records with a status “active” or empty.
      In the CITIES table, I have a drop-down with countries from the COUNTRIES, showing me only the “active” countries.
      I’d like to add a “show all” checkbox to the CITIES table, in order when checked to see all countries in to drop-down.

      I found this interesting blog, which could lead to solve this:

      Filtered Value Lists, with “All” Option | A Filemaker Miscellany

      Unable to recreate this, I hope to finde help here.

      Anybody out there who knows how to do this?

        • 1. Re: filtered valuelist with "show all" option
          keywords

          Assuming the field with the Active/Inactive Value List is a global, I suggest you create another utility global field and set it up with a Value List with the single value, All. This field can then be set with a trigger that sets the other field to both values (or all values if there are more than two), which will have the effect of removing the filter. Your script would need to also cater for the converse situation when users uncheck the All field.

          • 2. Re: filtered valuelist with "show all" option
            p528

            Hi keywords

             

            Thank you for the input.

             

            If you're referring to a global active/inactive field in the country table, I guess that's not the right way.

            Each country is either active or inactive. A global field would change the status for all countries each time.

            I also have the idea to create another utility field in the country table, global or calculation field (?),

            which is either set to "all" or uses the countries status, depending on a switch in the cities table, active or all.

            And then using a dynamic value list in the cities table, based on the utility field in the countries table.

             

            I'm lost...

            • 3. Re: filtered valuelist with "show all" option
              keywords

              No, that is not what I mean. As I understand you, you have a list of Countries which may or may not be Active. That field needs to be  standard field that you are looking for is a list that dynamically shows either only the countries marked Active or All countries. The global field containing this value is used simply to filter the list, so you need a relationship that matches this global field to the active/inactive field.

              • 4. Re: filtered valuelist with "show all" option
                p528

                How does a global field filters a list?

                 

                 

                 

                • 5. Re: filtered valuelist with "show all" option
                  TomHays

                  In the UI situation you describe, I think simply creating a second Value List to display all items would work well.

                  Put both value lists, filtered and unfiltered, on the layout but display only one of them at a time.  Which one you display would depend on the value of the checkbox.

                   

                  This can be as easy as using Hide Object When in the Inspector if you are using a version of FMP 13 or later.

                   

                  In earlier versions you can put the value lists on separate (hidden) tabs and automatically change tabs using the checkbox and a script trigger.

                   

                  -Tom

                  • 6. Re: filtered valuelist with "show all" option
                    p528

                    Hi Tom

                     

                    So, your suggesting two value lists; one for all countries and one for filtered countries?

                    That means I have to create to fields in the CITY table? One for ll countries and one for filtered countries?

                    Or is there a way to choose  between value lists for the same field?

                     

                    Greetings

                    n.

                    • 7. Re: filtered valuelist with "show all" option
                      philmodjunk

                      In the countries table, define a calculation field named cselectFilter with a text result type:

                       

                      List ( status field ; "All" )

                       

                      In the cities field format a text field named filter with a value list of Active and All.

                       

                      For your conditional value list, set up this relationship:

                       

                      Cities::Filter = Countries::cSelectFilter

                       

                      Set up your value list to list values from countries, include only related values, starting from cities.

                      • 8. Re: filtered valuelist with "show all" option
                        TomHays

                        p528 wrote:

                         

                        So, your suggesting two value lists; one for all countries and one for filtered countries?

                         

                        Yes.

                         

                        You wouldn't need to create two fields in the CITY table.

                         

                        You would put the same field twice on the same layout.  Each instance of the field would use a different Value List for data entry.

                         

                        My suggestion will allow you to have the UI effect you desire without changing your data model or adding fields to either table.

                         

                        You haven't described how you are currently implementing your value list of Active countries.

                        Since you are only displaying the active ones and have a field containing Active or empty, I assumed that you have a stored calculation field in the COUNTRIES table such as

                        cCountryID_Active =

                        Case(

                        Status = "Active"; id_Country;

                        ""

                        )

                         

                        and are building your value list on that.

                         

                        If you are already using a relationship from CITIES to COUNTRIES to generate the filter such as

                        CITIES::cConstant_Active = COUNTRIES::Status

                        the other suggestions of modifying the filter field in the CITIES table should also be easy to implement if you are willing to add an additional field to to your COUNTRIES table solely to support the UI.

                         

                        -Tom