10 Replies Latest reply on Jul 6, 2014 8:41 PM by user19752

    Show all records in drop down list

    barcello

      Maybe it’s easy, but I can’t find the way to set, in a drop down list or a popup menu, a value that shows all the records!!

      In a report, I’m using a dropdown list to show a list of names from another table (using a values list). Every time I choose a name it shows some details related to that specific name.

      Answer:

      How can I include in the values list a value called “show all” that give me the possibility to show the details from all the records?

      Now I’m using a button that activates a script “show all” but it would be better to include in the dropdown list.

        • 1. Re: Show all records in drop down list
          Mike_Mitchell

          Barcello -

           

          If I'm understanding your question correctly, you have a filter (either a filtered portal or a list view that triggers based on a value in a parent field), and you want to include, in your dropdown list, an option to show all records from the related table. The bad news is that there is no way to do this with a native FileMaker setting. However, it can be done. Here's one way:

           

          1) Define a calculation field in your parent table that looks like this:

           

               List ( List ( relatedTable::value ) ; "Show All" )

           

          Use this for your value list, and attach it to your global field for the pull-down / drop down.

           

          2) Define a second calculation field that looks like this:

           

               Case ( parentTable::dropDownField = "Show All" ; List ( relatedTable::value ) ; parentTable::dropDownField )

           

          Use this as the parent-side field for your join.

           

          What does this do? First, you get a value list that consists of all the values in the related table, plus "Show All". Then, when the user chooses a value, the second field determines what value to use for the match. If he's chosen "Show All", the match field calculates itself to be equal to all the values in the child table. This will cause all the values in the child table to show up. Otherwise, it will just show the value the user has chosen.

           

          HTH

           

          Mike

           

          P. S. Note that this method may be slow if you have a lot of records in your child table, so you will have to evaluate performance.

          • 2. Re: Show all records in drop down list
            erolst

            Mike_Mitchell wrote:

             

            […]

            1) Define a calculation field in your parent table that looks like this:

             

                 List ( List ( relatedTable::value ) ; "Show All" )

             

            Use this for your value list

            […]

             

            List ( RelatedTable::value ) cannot be indexed, so this doesn't work.

             

            What does work is creating a value list of the field values, then create a calc field as

             

            Let (

              l = ValueListItems ( Get ( FileName ) ; "TheValueListName" ) ;

              List ( l ; "Show all" )

            )

             

            and use that field for the desired VL.

            • 3. Re: Show all records in drop down list
              BruceRobertson

              But that won't be storable either, will it?

              And - in what table would you define it?

              Can you provide an example file?

              • 4. Re: Show all records in drop down list
                Mike_Mitchell

                My bad. Bruce is right (as usual). The attached example demonstrates a means for doing it using a stored field in the local table. There's probably a better way (for example, setting the index field in a common globals table instead). But this will demonstrate the principle.

                 

                Mike

                • 5. Re: Show all records in drop down list
                  erolst

                  BruceRobertson wrote:

                  But that won't be storable either, will it?

                  Why not? You're reading in an already stored list and add a new literal value.

                  But you're right in a another (maybe unexpected) way: the field, and thus the value list, doesn't update itself when adding new data.

                   

                  I don't like drop downs / popups …

                  • 6. Re: Show all records in drop down list
                    rgordon

                    You could try using a popover with a scrolling portal to accomplish what you want.  Popovers give you a lot more capabilities than drop down menus or pop up lists

                    • 7. Re: Show all records in drop down list
                      barcello

                      Thanks’ guys! I thought it would be easier to have all records shown using a dropdown list, at least it’s something that it’s very common to use if you have to show all records.

                      I’ll try to apply Mike’s solution hoping not to have too many records!!!

                      • 8. Re: Show all records in drop down list
                        Mike_Mitchell

                        I tend to agree, but wanted to address the specific question.   

                        • 9. Re: Show all records in drop down list
                          edwardlscott

                          Nice implementation, Mike, if I may say so. 

                           

                          Using your same basic technique (a locally stored text field for defining the drop-down list with the "Show All" value included) I modified your example file for fun (slow day today).  My version does not use SQL "Select" statements (not my forte, although they are cool), and I added a "Cartesian" relationship for picking up the full list of names in the "Child" table.  The only triggered script needed by this approach is for re-setting the main select field to "Show All" when changing records.

                           

                          I added one little trick to keep "- Show All -" at the top of the pulldown list for convenience and similarity to web-browser behaviors.

                           

                          I'm curious of Barcello (the original poster) - was this what you were really looking for?

                           

                          Regards,
                          Ed

                          • 10. Re: Show all records in drop down list
                            user19752

                            There is a simple way,

                            adding a stored calculation field to value list table as

                            List ( value ; Char ( 9 ) & "Show All" )

                            and use this for value list.

                            Char(9) is for sorting at top.

                             

                            But this may be loss of disk space, I don't think so better than "using a button that activates a script “show all”"...