1 2 3 Previous Next 30 Replies Latest reply on Apr 7, 2012 7:24 AM by johnhorner

    Creating Sub-Menu Pull Down Lists

    jessekorosi

      Within a database I am generating I have a column titled: Category. I have made this a pull down menu that has about 5 different options within it. Next to this column is a column titled Sub-Category. I would like the pull down list that shows up to be decided on based on what was chosen within the Category column. Therefore if you choose Category "A", you then get options within the Sub-Category column that relate to "A". But if you chose Category "B" you would get a different set of options in the sub category to choose from.

       

      After some digging I found a few different walk throughs that tried to explain this, one of which was a PDF downloaded from the File Maker Site titled: Getting_Started_with_FileMaker. Although within any of the content I have read thus far I have not seen a specific step by step walk through explaining how to set this up.

       

      Any help in finding a very easy to follow step by step guide (remembering that I am very new to this!) would be greatly appreciated.

       

      Thanks,

      Jesse Korosi

        • 1. Re: Creating Sub-Menu Pull Down Lists
          Stephen Huston

          Hi Jesse,

           

          It sounds like you are working with creating  Value Lists for selections.

           

          Take a look at the options for showing only related values using field contents. You may need to create a source table of values with fields for category1, value2, etc, with relationships among the categories, so that the results of your first selection narrow your options for sub-selection value lists.

           

          Create Table Occurances which include the earlier fields' category selection as part of the relationship keys, then build your secondary value list off of the related field values.

           

          I apologize that this is a bit vague/theoretical without more specifics.

           

          Stephen Huston

          • 2. Re: Creating Sub-Menu Pull Down Lists
            Mike_Mitchell

            Jesse -

             

            Stephen's suggestion is on target. I've done this before in a couple of instances. What you can do is create a simple value list for the first level, then another value list based on a join to a related table, where the key field is the field in which the user has selected the first value.

             

            So, for example, say you want to create a list of states, followed by a list of cities that exist within those states. Your first value list just contains all the states. You have a table in the database that holds all the cities, but each record also has a field for state, like this (simplified example):

             

            City                     State

             

            Atlanta                 GA

            Albequerque         NM

            Albany                 NY

            .

            .

            .

            Washington          DC

             

            So, you build a join from your first selection field to the State field in the Cities table. Then base your value list on that join - show me all the City values from the City table where City = Selection1.

             

            If you need a third level, you can create another value list based on a two-predicate join (the value in field1 plus the value in field2). And so on. It's a little klunky, but it does work.

             

            Mike

            • 3. Re: Creating Sub-Menu Pull Down Lists
              Claw'd

              Hi Jesse

               

               

              try this, much the same as other answers but a step through.

               

               

              Create two tables, 1) categories 2) Subcategories

               

               

              Create a field in Categories: Categories (txt) In Subcategory: Categories (txt) and Subcategory (txt)

               

               

              Create a relationship between categories table and Subcategory table

               

               

              Categories = Categories and check the Allow Creation of Related Records box on the Subcategory Side

               

               

              On the Categories layout change to form, remove all fields except the Category field, under this place a portal from Subcategory Table with the Subcategory field on it.

               

               

              Create a record and put a name the Categories field

               

               

              Create as many subcategories as you want in the portal, as they are created they automatically create the relationship key based on the "Categories" field in the Categories table.

               

               

              To add a new Category create a new record and fill in the Subcategory field as before.

               

               

              In the table you want to access the lists from create a field "Categories" and "Subcategory". Add an instance of the "Categories" Table in the relationship graph and link Categories to Categories (=).

               

               

              Put the "Categories" Value List on the Category Field and "Subcategory" VL on the Subcategory field, create a new record and check it out:)

              • 4. Re: Creating Sub-Menu Pull Down Lists
                beverly

                Alternate method.

                 

                If the number of values in a sub-menu are not great AND you don't need any "ID" or other fields relating to the menu and sub-menus, this works:

                     categories (table):

                          category (text)

                          subcategories (text)

                 

                Then set the subcategories field to "values" (return delimited). Use the relationship to the category field to show the values from the field subcategories (in the value list definition).

                 

                example:

                     STATE CITIES

                     MI         Lansing

                                  Detroit

                                     Grand Rapids

                     OH        Columbus

                                   Cleveland

                                      Cincinnati

                etc.

                 

                The values in the submenu field will sort alpha, so order doesn't matter. One table, less relationships to set up.

                 

                Beverly

                • 5. Re: Creating Sub-Menu Pull Down Lists
                  jessekorosi

                  Thanks for all of your feedback guys! 

                  I thought I would insert a few screen shots here in case anyone else comes across this thread looking for assistance!

                  Screen shot 2011-12-12 at 1.41.05 PM.png

                  Screen shot 2011-12-12 at 1.40.56 PM.png

                  Screen shot 2011-12-12 at 1.41.54 PM.png

                   

                  Screen shot 2011-12-12 at 1.39.18 PM.png

                  Screen shot 2011-12-12 at 1.43.05 PM.png

                  Screen shot 2011-12-12 at 1.43.37 PM.png

                  Screen shot 2011-12-12 at 1.44.09 PM.png

                  Screen shot 2011-12-12 at 1.44.22 PM.png

                  Screen shot 2011-12-12 at 1.44.37 PM.png

                  • 8. Re: Creating Sub-Menu Pull Down Lists
                    chris.schmitz

                    Good answer 5178. The only thing I would throw in on your concept that if you base the relationship on the text your using to link the two tables, you could run into a problem if you ever have to change the text for a Category.

                     

                    If you have a Category text that is equal to "Shoe" and you realize that you need to change it to "Shoes", or if you accidentally type in "Shoes " and need to change it  to "Shoes", or if you realize you have a carriage return in the Category text and remove it later, etc, In any of the situations and many others if you change the initial string of category then you'll break the link between the Category and any related SubCategory records.

                     

                    You can get around this by creating an ID field in the Category and SubCategory tables. In the Category table, allow the ID field to auto enter a serial number. Depending on how you want to populate the SubCategory records, you can either allow creation of the SubCategory across the relationship which would automatically link the category and SubCategory when creating subcategories from a record in the Category context, or you can script a process that links the Category to the SubCategory

                     

                    #starting from the desired category record

                    set variable ($CategoryID; Category::ID)

                    goto layout (SubCategory)

                    New Record

                    Set Field (SubCategory::ID; $CategoryID)

                    #etc

                     

                    Either way, using a display text to link two records can be dangerous because it's human input controling the link.

                     

                    Something to think about.

                    • 9. Re: Creating Sub-Menu Pull Down Lists
                      Claw'd

                      Hi Chris

                       

                      Yes changing the main catagory certainly loses all the subcatagories but in this case I would script a "Change Catagory Name" function on a button that populates a global with the catagory to be changed and a variable with the new catagory so that these values can be used in a find and replace in the subcatagories table. This is simple and avoids the complications of IDs and keeping them synchronised and gives the user a defined way of changing values.

                       

                      Patrick

                      • 10. Re: Creating Sub-Menu Pull Down Lists
                        chris.schmitz

                        Gotcha,

                         

                        That's definitely one way of doing it. Personally, I think that's getting way more complicated than using IDs. There isn't really any syncronizing of IDs that you would have to do. If you use the IDs they get set once and then there's really no need to change them unless you're wanting to shuffle sub categories between main categories. Even then, it's probably going to be easier to change a sub category's link using a number than a string of text.

                         

                        Really, you don't ever even need to see the IDs at all. If you have the ID's auto serialize for category, they'll handle themselves and you don't need to see the field. If you create the sub category records from the category context, they'll add the IDs themselves to the sub category records so you don't need to see them. Even if you use a script to create the sub category records you wouldn't need to see the fields.

                         

                        Either way you do it is fine. I just think you could save a lot of time, file overhead, and have way less risk of data corruption with a simpler set up (eg IDs).

                        • 11. Re: Creating Sub-Menu Pull Down Lists
                          Claw'd

                          Haha, the more one thinks about this the more (I anyway) think it's a bad idea to go changing catagories any way.

                          In a multi user solution some sort of control is required: adding onto the scripted changing of catagories one could easily add some error checking, permissions and a way of globaly changing all instances of the to be changed catagory as changing catagories in a list inevitably leads to finds not returning a complete set of items because the new catagory has been used and is different enough from the original that it is excluded.

                           

                          I don't think there is an easy solution to the whole value list problem especially when people have the ability to edit the entries but some control needs to be in place, after all, value lists are used for easy input of data and making the data consistant and easy to find and subsequently sort on. Interesting topic.

                          • 12. Re: Creating Sub-Menu Pull Down Lists
                            chris.schmitz

                            I agree that would be hard in a multi user environment, that's why I lean towards ID links. The users can change the category names all they want and it would not affect the relationship link between the categories and subcategories at all. They could even remove the category name all together and it would still retain the relationship. I just imagine having 1 category records with hundreds of related records in the subcategory list and then trying to change all of them at the same time in a multi user environment and it makes my stomach turn

                             

                            When I think of accomplishing this task, I think of this:

                            ================================================

                             

                            Category Table

                            --------------------

                            __ID     <- number field with auto enter serial number

                            CategoryName <- text field

                             

                            SubCategory Table

                            --------------------------

                            __ID     <- number field with auto enter serial number (not needed for the link, but all tables should have unique identifiers)

                            _categoryID     <- number field

                            SubCategoryName     <- text field

                             

                            Interface Table

                            --------------------

                            __ID     <- number field with auto enter serial number (not needed for the link, but all tables should have unique identifiers)

                            CategoriesList     <- text field

                            SelectedCategory     <- number field

                            SubCategoryList <- text field

                             

                            Relationships

                            ----------------

                            - Category::__ID = SubCategory::_categoryID     <- allow creation of records on the subcategory side, delete if related record is delete on the subcategory side

                             

                            - Category::__ID X Interface::__ID     <- Cartesian join (x) between IDs, means 'every record in table A can see every record in table B)

                             

                            - Interface::SelectedCategory = ShowSubCategory::_categoryID     <- new table occurrence using the subcategory table

                             

                             

                             

                            Category/Subcategory Management Layout      <- show records from Category in form view

                            --------------------------------------------------------

                            1 field for the category name

                            1 portal using the subcategory table occurance      <- you can select the last row in the portal to create the sub categories, the current Category's ID is automatically tied to the subcategory

                             

                            Interface Layout     <- Show records from Interface table in form view

                            ---------------------

                            1 field for the Interface::CategoryList field      <- has a value list "Categories" , show as popup menu

                             

                            1 field for the Interface::SubcategoryList field    <- has a value list "Selected Subcategories"

                             

                            Value Lists

                            ---------------

                            Categories: List that uses the values from Category::__ID (first field) Category::CategoryName (second field), script trigger to script "Set Selected Category", only show second field

                             

                            Selected Subcategories: shows values from the ShowSubCategory::SubCategoryName field

                             

                             

                            Scripts

                            ---------

                            Set Selected Category:

                            - Set field (Interface::SelectedCategory; CategoryList)

                            ================================================================================

                             

                             

                            Basically, the relationship between category and sub category is linked by ID, the category value list is built off of ID and name and the popup menu will allow you to store the ID number and show the name, and when you select a category it will set the selected category field with the id from the value list which shows the sub categories with that given ID in the second value list.

                             

                            It's a rough sketch and I haven't built it out to trouble shoot an look for any gotchas, but that's the leanest way I can see to do this idea.

                             

                            The nice thing about doing this kind of a set up is that if you need to change a category name, you can go in and change it and none of the links break. There's minimal scripting, and it's taking advantage of the program's tools as much as possible instead of building it new tools.

                             

                            Def not an easy thing to accomplish, but doable

                            • 13. Re: Creating Sub-Menu Pull Down Lists
                              alas

                              Just another thought when implementing this type of solution.  If someone selects a Category and then from the subcategories that appear, selects a subcategory, and then they unselect the category, does the subcategory selection "go away".  I have done this approach with check boxes, and FM does not "clear" the subcategories selected when the category is unchecked.  They "go away" from the subcategory field display but the selection is actually still there in the field.  And if the person selects a new category and then new subcategories, they get added to the previous subcategory selections with paragraph marks in between then.

                               

                              I got around this problem by changing the field definition of the subcategory field to include an auto-enter calculation.  I used the following:

                               

                              Let(

                              $trigger = Flag_Category ;

                              FilterValues(

                              Flags ;

                              ValueListItems( Get( FileName ) ; "Flags" )

                              )

                              )

                               

                              What this does is filter out the items in the "Flags" field based on the category that was selected.  So unselecting a category automatically unchecks the flags selected in the "subcategory" field. 

                              • 14. Re: Creating Sub-Menu Pull Down Lists
                                FCallanan

                                I often use the method Beverly describes. I find it to be quick and simple in most cases.

                                 

                                Frank Callanan

                                Camden. Maine

                                1 2 3 Previous Next