9 Replies Latest reply on Nov 29, 2011 1:34 PM by philmodjunk

    Multi tiered conditional lists



      Multi tiered conditional lists


      First, I'm using FileMaker because it's a lot more user friendly than access and I'm not a DB guy. so I may be thinking about this all wrong.

      I'm making a software inventory db for my department. it will have all the standard pertinent Software Fields in one Table, and then where we have installed the software, and the "where it's installed" is where I'm having problems.

      I want to have a drop down menu from a "building" table (I did this part)
      then have a second drop down menu with "Room Numbers"
      and then have a check box list of the "client"

      Lastly, I'm going to need multiple copies (I'm thinking 4 will do it) because the same software entry will be installed in different rooms and possibly buildings.

      I fully aware that I may not be articulating everything perfectly so I've linked to the db with enough information in the the related tables. 


      Thank you for any help you can give me.

        • 1. Re: Multi tiered conditional lists

          There's a number of problems with your current file, not just the relationships. What you have makes intuitive sense when you first set up the relationships between the tables, but isn't what you need for your chain of conditional value lists.

          Instead of:  Software----<building----<rooms---<clients

          You need the relationships for your conditional value list to all be set up to link to the same layout's table occurrence instead of in a chain like this. FileMaker does not limit you to one relationship between a given pair of tables so you do not have to change your existing relationships in order to get your conditional value lists to work, but I recommend you make a number of additional changes as well.

          You have a many to many relationship. A given software record can be installed on any number of clients and any given client computer can have any number of different software apps installed on it.

          For documenting the software installation, I suggest these relationships:


          Software::SoftwareID = Software_Client::SoftwareID
          Client::ClientID = Software__Client::ClientID

          With this setup, you can use a portal to Software_client on a software layout to list all clients where that software is installed. On a client layout, a portal to Software_client will list all software installed on that client.

          Here's a demo file you can download and examine that demonstrates such a many to many relationship and then offers some alternative user interface options to consider:  http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

          I'll start a new post on your chain of conditional value lists...

          • 2. Re: Multi tiered conditional lists

            Starting with the last post's relationship: Software----<Software_Client>------Client

            You can document the location of a client, (Not the software as it exists in multiple locations), with fields in client that identify the room and building:



            If I understand your original post, we need to set things up so that you can select a building to get a list of rooms, then select a room to get a list of clients. This means that our fields and their value lists will be a part of Software_Client so that they can be used in a portal to the that table.

            Software_Client::BuildingID = SC_Rooms::BuildingID

            Software_Client::RoomID = SC_ClientsByRoom::RoomID

            Set up a field, Software_client::BuildingID with a value list. Use the Specify Field option to list all BuildingID values from the Building table. You can include a name or address as a second field in this value list if it helps the user select the correct building.

            Set up Software_client::RoomID with a value list that lists all RoomID's from SC_Rooms, include a second field with a room number if needed. And specify the Include only related values starting from Software_client option.

            Set up Client ID from SC_ClientsByRoom; Include only related values starting from Software_client.

            SC_Rooms and SC_clientsByRoom are new table occurrences of Rooms and Clients. You create a new occurrence of Rooms by selecting it in Manage | database | Relationships and then clicking the duplicate button (has two green plus signs). You can change the name of an occurrence by double clicking it.

            Here are some links on conditional value lists that you may find helpful:

            Forum Tutorial: Custom Value List?

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

            Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

            • 3. Re: Multi tiered conditional lists

              Ok, I have it set up so that you can select the building by and room by pop up and then a checkbox appears and you can check the clients. there is only one "building" "room" and "client" box, but I think that's fine as the checkmarks stay when changing building and room.

              Now, how can I make a report that will be printed out that shows where "Software A" is installed? I've tried making a new layout "report" but it just shows the last room I had selected and all of the clients in that room, not just the checked ones.

              • 4. Re: Multi tiered conditional lists

                Make a list view type report based on the Software_client layout. You can perform a find for a specific software item if you only want to see all the clients that have that item installed.

                If you haven't created summary reports before, you might want to check out this tutorial: Creating Filemaker Pro summary reports--Tutorial

                (This is a pretty old thread. New posts to this tutorial will not pop it back into Recent Items, so it will be better to post any questions about it hat you might have here instead of to the tutorial thread.)

                • 5. Re: Multi tiered conditional lists

                  Once again, thank you Phil, you're a life saver. if you live in the St. Louis area, I owe you a beer.


                  I followed all of your guides and I think I have everything right, the only problem I have now (I think) is in the report:

                  Is there a way to change the way the field is displayed so that it isn't just one item per line, or it creates multiple colums? I have tried showing as check box, but that shows all of them for the room not just the ones checked. Most rooms have 25 clients, a few have 35, one has 50, and then our large lab has 88, so having the space for 88 machines creates a LOT of white space 99% of the time. I tried "sliding" things up, but don't see how to slide the "body" of the layout.

                  When making the report I had it create a ClientIDCount field which I thought would count the number of clients I've installed, but either it's not working or I'm wrong. Once again, because I could be using the wrong verbage, I've uploaded my database.

                  Updated Database

                  • 6. Re: Multi tiered conditional lists

                    but don't see how to slide the "body" of the layout.

                    Sliding can be tricky until you figure out all the requirements needed to get it to work. Make sure that "resize enclosing part" is selected to remove the unused white space in your report. See the bottom of this post for more details.

                    I don't see why sliding, or the number of clients, 25, 35, 50, ect. should create any need for sliding here.

                    A report structured like this:

                    Client 1
                       Software item 1
                       Software item 2
                       software item 3

                    Client 2
                       Software item1
                       Software item 2

                    and so forth is the easiest format to set up.

                    It takes more work using one row portals placed side by side, but it is possible to produce this format:

                    Client 1 | Software Item 1 | Software Item 2 | Software Item 3
                    Client 2 | Software Item 1 | Software Item 2 |

                    You also lose flexibility as this puts a maximum limit on the number of software items you can list without making design changes to the layout in order to add more columns, but it can be done if you care to invest the effort.

                    Base your report on the client table and use portals to the Join table to list the columns of software titles. The first portal would be set to display Initial row: 1, number of rows 1. The second would be set to Initial Row: 2, number of rows one and so forth. You can set up the portal for the first column and then copy it and it's fields multiple times, just chaning this one setting to specify different portal rows. This trick is called a "horizontal portal".

                    • 7. Re: Multi tiered conditional lists

                      Key facts about sliding layout objects:

                      1. It's only visible in preview mode and when you print/save as PDF...
                      2. All layout objects below and in the same layout part as the slide/resize field need to also be set to slide up and resize.
                      3. Objects in headers and footers will not slide.
                      4. Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
                      5. Container fields will slide only if Top, Left alignment is specified for it in the data formatting section of the Inspector's data tab.
                      6. Consistent side borders are difficult to achieve with sliding fields.
                      • 8. Re: Multi tiered conditional lists

                        Thanks phil, I'll start working on that. the area where would I need the list to be horizontal is for the software focused printout.

                        Software Title 

                        Room: Client 1

                           Client 2

                           Client 3


                        • 9. Re: Multi tiered conditional lists

                          Same method works, but you'd reference different table occurrences for you layout and portals.

                          Squeezing 50 or more columns of data into a single report may be a bit difficult to do.