11 Replies Latest reply on Feb 12, 2013 11:11 AM by FarfelKnabe

    Dynamic Menues with three levels (or four)?



      Dynamic Menues with three levels (or four)?


           I am not an experienced FMP user, but I try and with a trial and error doing I get most of the things I want to work. But this one function has eluded me for a long time now.

           I am trying to implement a dynamic menu with three levels into my database. And that first two was not very hard to to following a video tutorial on youtube (http://youtu.be/nVTRGhIEz6E). But this tutorial only shows how to make two levels in the menu structure.

           I would like to have a third level, under the second level.

           That is if I choose a value from the main menu (list) , then in second menu I get a sorted list in relation to the value in the first menu. I now have a third menu that I want to have sorted in relation to the choice I made in the second menu!

           I have tried to manage this in the relationships by trial and error but I can't seem to find the right sets of relations to get it to work. So I now think there is something else involved to make it work, or at least that is why I am asking here at the forum for help!

           We can take this as an example: I have a database over the movies I see at the cinemas. And since I travel a lot I watch movies in new cities and in new countries all of the time!

           The first table, named "Movies" contains the Movies. And here is where all the relations is stored. All the movies are seen at different Cinemas, in different Cities in different Countries.
           - Life of Pi
           - The Hobbit
           - Skyfall
           - Jack Reacher
           - Hansel and Gretel
           - Total Recall
           - Django Unchained
           - Killing Them Softly
           - The Bourne Legacy
           - The Dark Knight Rises
           - The Avengers
           - Prometheus
           - Dark Shadows
           - Battleship

           The second table, named "Country" contains the Country where I watched the Movie.
           - USA
           - England
           - Australia

           The third table, named "City", that is which City in that particular Country.
           - New York
           - Chicago
           - London
           - Liverpool
           - Sydney
           - Melbourne

           And last I also have a fourth table called "Cinema", which is the particular Cinema in that particular City.
           - AMC Empire Times Square
           - Angelika Film Center
           - Landmark Sunshine Cinema
           - Logan Theater
           - AMC Theater
           - Davis Theater
           - Odeon West End
           - BFI Southbank
           - Liverpool One
           - Wolton Picture House
           - Event Cinemas
           - Palace Verona
           - Village Cinema
           - Palace Cinema

           Is this in anyway clear and is there any chance to get it to work?



        • 1. Re: Dynamic Menues with three levels (or four)?

               I have two video's that deal with something similar. But only on two levels. However if you understand the structure you can take this as far as you want.



               The simplest way to do this is create one table called "cinemas" and add all your cinema's in that table. Add an ID field and a field for country and city.


               ID - Auto Enter Serial Number

               Use dropdowns for the city and country so you don't make typos.

               One you have that use that table as a basis for your conditional value lists.

               Look at my video's to see how that works.


          • 2. Re: Dynamic Menues with three levels (or four)?

                 Thanks, I will look at the videos and try it out.


            • 3. Re: Dynamic Menues with three levels (or four)?

                   You may also find these links helpful. The final link is to a thread that discusses this issue:

                   Forum Tutorial: Custom Value List?

                   Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                   Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                   Hierarchical Conditional Value lists: Conditional Value List Question

                   Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

              • 4. Re: Dynamic Menues with three levels (or four)?

                     Hi again,

                     I have now looked at all the examples given and I do understand the basics in getting the relationship to work. Thanks for the help so far, but I still havn't solved my problem yet!

                     When I am trying to build up the structure, with the examples given as a base, to be in the order - Country - City - Cinema, I don't get it to work. I first want to choose Country and with a conditional value list sort out City based on Country, and last I want to choose Cinema based on the City.

                     I have fiddled around with the examples and tried to get it to work in that order, but I don't understand how to get the third condition to response. That is I can't seem to find the right relationship to get it to work. Country and City I get to work, or if I turned it around I get Cinema and City to work but the third option!

                     Since I am quite new at FP please help me! What am I missing here?





                • 5. Re: Dynamic Menues with three levels (or four)?

                       Have you read the very last link of those that I posted? It deals with setting up a hierarchical conditional value list--what you are trying to set up there. I need to know if you've read that link before I can provide more detail based on that foundation.

                  • 6. Re: Dynamic Menues with three levels (or four)?

                         Hi Phil,

                         I have gone through that thread now, http://forums.filemaker.com/posts/1ebc83718f, I do understand a bit more, but I still can't get my three levels to work.

                         I store the values in my Ticket table, where each post represent a seperate movie watched at a cinema (somewhere). In this table I would like to be able to enter Title, Date, Country, City and Cinema. Where the Country can be a regular dropdown menu, not conditional. But City should be a conditional dropdown based on which Country I have choosed, and last the Cinema conditional dropdown is based on the City!

                         Maybe this is not that difficult to manage, for more experienced users, but for me apparently there is!!!

                         I have a test database where I only have two tables:

                         Cinema table with the following field: (into this table I have imported the whole list from my regular database, so this table is already populated)
                         ID (autoserial)
                         Cinema# (number)
                         Cinema (text)
                         City (text)
                         Country (text)
                         First Visited (date)

                         Tickets table with the following fields: Here I would like to fill in every new movie I see and be able to choose the right Country, City (based on Country) and last Cinema (based on City).
                         ID (autoserial)
                         Ticket# (number)
                         Title (text)
                         Date (date)
                         Country (text)
                         City (text)
                         Cinema (text)

                         In your example the user asks for help with his particular tables. Maybe you can help me with the same, using my tables to help me?



                    • 7. Re: Dynamic Menues with three levels (or four)?

                           You'll need several Tutorial: What are Table Occurrences? that all refer to the Cinema table.

                           In Manage | Database | relationships, make a new table occurrence of Cinema by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be CinemaByCountry.

                           We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                           Add it to your relationships like this:

                           Tickets::Country = CinemaByCountry::Country

                           Repeat the process to get this relationship using yet another new table occurrence of Cinema:

                           Tickets::City = CinemaByCity::City

                           Then your first conditional value list should list values from CinemaByCountry::City. The second value list would list values from CinemaByCity::Cinema.

                           Both would specify "Include only related values starting from Tickets".

                           But I must point out a potential problem:

                           This will only work if you do not have two cities, from two different countries with the same name. You will also have trouble if you have two cinemas with the same name--a very likely possibility.

                           To avoid such mismatches, you should consider giving each city and each cinema unique ID's (serial number fields) and set up matching fields in Tickets for these ID numbers. Then you can set them up with your conditional value lists--which will then need to be changed to enter ID numbers and relationships that use these fields as match fields to show any needed data from related tables.

                      • 8. Re: Dynamic Menues with three levels (or four)?


                             Thanks, I got it to work, atleast halfway!

                             I will do as suggested and and number fields to the Cinema table, to use with the relations instead.

                             Maybe then I get it to work. Because now the only thing that works is the sorting on just one Country, that is both City and Cinema is sorting on just the one Country (I have six) and even if I change the Country it still sorts from the the one Country (the same all the time). Also I don't get the dropdown to work with the next posts, only the first post added!

                             But I will try the number field relations and see if that works better!






                        • 9. Re: Dynamic Menues with three levels (or four)?

                               What you describe should not be the case. Better check the details in how you set it up.

                               I strongly recommend against trying to make this work with IDs until you get them to work with the names data you already have in place.

                          • 10. Re: Dynamic Menues with three levels (or four)?

                                 Ok, so names instead of ID's. Now the Cinemas with the same name in different Cities are seperated with the city name in brackets Cinema Name (City Name). So I won't have any problems there.

                                 But what about not having the dropdowns to work at all?



                            • 11. Re: Dynamic Menues with three levels (or four)?

                                   Just now I got it to work!!!

                                   Thanks Phil, you're the man :)