7 Replies Latest reply on Sep 24, 2013 10:11 AM by yetanotherwebaccount

    Conditional value lists



      Conditional value lists


           Hi - I'm stepping up from Bento, and climbing Filemaker Pro's learning curve. 

           I have a relatively simple database: a series of linked one-to-many tables to implement GTD. At the top, a table of my areas of responsibility, which links to a table of vendors, which links to projects, which links to to-dos and which links, separately, to notes (notes and to-dos are both children of projects). A "files" table is another child off the vendor table. 

           As I create a new to do or note, for example, I want the fields representing the area, vendor, and project to reflect what has been setup in those respective tables. So I setup value lists with pop-up menus, with the value lists pulling up the names from the other tables (actually, it keys on the ID and name/label and is set to only show the name/label, the second field). That way the ID number is hidden and I just work with names. 

           So far so good but since the project names are rather generic and therefore the same name is used for different projects for different vendors, it's impossible to tell just from the project name what vendor it's tied to. 

           For example, if my tables were instead states, cities and streets, when creating a "repair" record, selecting "main st." would hardly distinguish it from main st. in every other city. 

           So, I want to implement conditional values - selecting the area would limit the vendor list, which in turn would limit the project list. 

           In the value list setup, I changed it from all values to only values of the table one up on the stack, but it's not working. That is, in the vendor value list, I limit it to related values from the area table (one up the stack). In a new record, I get a <no values defined> message in the field one level down (e.g., after selecting an area, the vendor field gives that message). In other instances, changing the area does not change the available choices in the options in the field one level down (changing from area a to b doesn't change the list of vendors). 

           Any suggestions?

        • 1. Re: Conditional value lists

               To start, check out these links:

               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.

               The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

               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.

          • 2. Re: Conditional value lists


                 Thanks. I had already tried following the knowledgebase article. I'll try that again. Would "use values from first field" (ID) combined with "also display values from second field" (name) complicate this? 

                 To make sure I have this right, if my vendor value list should list the vendors for whom there are already records, the "use values from" should refer to the vendor table and then the "include only related values from" should refer to the parent table (areas, in my case), correct?

            • 3. Re: Conditional value lists

                        Would "use values from first field" (ID) combined with "also display values from second field" (name) complicate this?

                   It shouldn't, but make sure that the first field value (ID) is what you use in your relationship to match records. Usually, the challenge is in correctly selecting the "Starting from" table occurrence as the link between the starting from table occurrence and the table occurrence from which you are listing values is what defines the relationship that then controls what values appear in the value list.

                   You may want to examine the demo file and note how it uses a portal to illustrate how the same relationship that displays records in a portal can show the same data in a conditional value list.

              • 4. Re: Conditional value lists

                     I still can't get this to work. 

                     If table "areas" has fields ID and label, and

                     Table "vendors" has fields ID, AreaID, label, and

                     Table "projects" has fields ID, AreaID, vendorID, label, and

                     Value list "areas" is drawn from the area records and is set to all, and

                     Value list "vendors" is drawn from the vendor records and is set to related starting from areas, then

                     In a new project record, I should be able to select an area under the AreaID field and get just its related vendors in the pop up field for vendorID. 

                     But it doesn't work. I'm so frustrated with my inability to figure this out. 

                     I notice that in the examples, such as in the knowledgebase article, all the value data was contained in one table (e.g., meals and food). In my case, I have separate tables for such data (like have a meals table and food table). Should that make a difference?

                • 5. Re: Conditional value lists

                       Hi, it is sounds to me like your problem lies in the Table Occurrence a very important part of getting this to work. The moderator (I believe he/she is) has an excellent tutorial on table occurrences.

                  Tutorial: What are Table Occurrences?

                       This video is quick and shows exactly how table occurrences are use explicitly in conditional value lists


                  • 6. Re: Conditional value lists

                         Some Screen shots of your relationship graph and the dialogs used to set up your value lists could also make it easier to identify (or rule out) some common mistakes made with conditional value lists.

                    • 7. Re: Conditional value lists

                           I'll try to get around to doing that but I decided to use Bento. Bento is just so easy, so intuitive, I was able to whip up a database in minutes. Sure, sure, it doesn't have portals (only manually linking), actions buttons, and more, but I'm able to do what I need and do it easily. 

                           If it turns out Bento is not compatible with Mavericks, I'll not upgrade or I'll keep an older Mac around just for Bento. It's that good (for my needs).