6 Replies Latest reply on Dec 2, 2009 12:13 PM by kirvis

    conditional value lists do not work

    kirvis

      Title

      conditional value lists do not work

      Post

      Hi all,

       

      I have a short question about a problem I have with the use of conditional value lists.

       

      I have a database with products, and those products can be divided into categories, sub categories, and sub-sub categories. What I would like to be able to do is create a layout where I can add new products, after first selecting the category, then the sub category, and then the sub-sub category. The problem is that eventually the list with sub-sub categories will be huge, so it would be nice to have a conditional value list based on the other tables.

       

      I however do not seem to be able to make it work and I can not figure out what I am doing wrong. I made a functional conditional value list based on the instructions in the FM help section, but this one does not work.

       

      The fields I have in the different tables are the following:

       

      table: categories

       

      •  ID
      • category name
       
      table: sub categories 
      •  ID
      • sub category name
      • categoryIDfk
       
      table: sub-sub categories
      •  ID
      • sub-sub category name
      • sub categoryIDfk
       
      table: products
      • ID
      • product name
      • sub-sub categoryIDfk
       
      I have defined three value lists based on the ID's and the second field (name). For the value list "sub categories", I have selected "Include only related values starting from product categories". 
       
      When I want to select a product category when I want to add a new product, FM does not let me. It says: "This field can not be modified until "sub_sub_categoryIDfk" is given a valid value". It also does not display the conditional value list for the sub categories and sub-sub categories.
       
      I have placed the file on my MobileMe account, since I think that that will make things much clearer. You can find it here.
       
      I hope that someone can lay his or her finger behind the mistake I am currently making, because being able to use conditional value lists would make working with the database much easier. 
       
       

       

        • 1. Re: conditional value lists do not work
          philmodjunk
            

          Conditional Value Lists have to be structured carefully.

           

          Once I took your file and linked them differently, adding two new fields to your Products table, they worked. I also had to change the text color of several of your fields from white to black so that I could see the related name fields appear.

           

          You need two additional fields in your Products table. With your current set up, picking a value from the combo box attempts to assign the ID number to the related table instead of your products table and that's why you see an error message.

           

          Define two new number fields in Products: CatID and SubCatID.

          Now you have to change the links in your relationship graph.

           

          Change them so that you have:

          Products::CatID = Product Categories:: Product Category ID

          Product Categories:: Product Category ID = Product Sub_categories:: Product categoryIDfk

          Products::SubCatID = Product Sub_Sub_categories:: Product Sub_categoryIDfk

           

          That makes your first conditional value list work. Now Open Manage | Value Lists... and edit Product sub sub categories so that the "starting from" table occurrence is Product Categories.

           

          Tables, and Table Occurrences can become confusing terms in Filemaker Land. See this link for more on the terminology and how it gets to be confusing:

           

          Table vs. Table Occurrence (Tutorial)

          • 2. Re: conditional value lists do not work
            kirvis
              

            Hi PhilModJunk,

             

            Thank you for your reply! The conditional value lists indeed work with your approach, I see now that I connected the TO's in the wrong direction.

             

            I do however have two slight other problems with the adapted file:

             

            On the layout for entering new products, I put the following fields (per line):

             

             

            • product categories::catID and  product categories::cat name
            • product sub categories:: subcatID and  product sub categories::subcat name
            • product sub-sub categories:: sub-subcatID and product sub-sub categories::sub-subcat name
            • product name

             

             

            I assigned the value lists to the ID fields, and had to tick all the boxes saying "allow creation of records via this relationship" in the "edit relationship" window to be able to select the IDs via the value lists. 

             

            My first problem is that when I select a ID via the value list (e.g. for product category), I would like the "name" field to immediately pull the category name for me, so that I am not looking at a number when selecting the subcategory. It does not do that currently, although I have had it functional in other files.

             

            The second problem is that FM does not seem to remember the connected categories and sub(sub) categories when I commit a new product record. It just messes up what I entered, and even automatically enters sub(sub) category values when I commit the record before finishing filling in all the blanks.

             

            When I enter a new product, I would like to be able to define the category first, then (conditionally) the sub category, then (conditionally) the sub-sub category, and finally the name of the product. This data then must be stored somewhere so that I will be able to sort products on all three kinds of categories.

             

            Any idea what might be the problem here?

             

            I put an updated version of the file online here. It is possible for other to upload files to the account as well.

             

            • 3. Re: conditional value lists do not work
              philmodjunk
                

              All of this works for me and I don't have to enable create records on the relationships either.

               

              I left this part out, come to think of it...

               

              For each of your combo box fields, you have to replace them with fields that refer to CatID and SubCatID instead fields in the other related tables.

               

              That's what's messing things up for you, I believe.

               

              I did have to change the text color of the fields to the right of the combo boxes to black instead of white.

              • 4. Re: conditional value lists do not work
                kirvis
                  

                I do not understand..

                 

                If I do as you suggested; putting the catID and subcatID fields from the products table on the layout and assigning the value lists to it, FM seems to partly disregard the conditionality of the lists. If I select a category, FM automatically enters the sub(sub) categories as well (at random so it seems), and I can not change that afterwards. 

                 

                If I look later at an added product, there are (sub(sub))categories linked to the product that should not even be linked to each other, let alone to the product.

                 

                Could there be something wrong with my file? The latest version is available online.

                • 5. Re: conditional value lists do not work
                  philmodjunk
                    

                  You don't have your relationships defined as I described.

                   

                  I used:

                  Products::CatID = Product Categories:: Product Category ID

                  Product Categories:: Product Category ID = Product Sub_categories:: Product categoryIDfk

                  Products::SubCatID = Product Sub_Sub_categories:: Product Sub_categoryIDfk

                   

                  Your latest file has:

                  Products::categoryIDfk = Product categories:: Product categoryID

                  Product categories:: Product categoryID = Product Sub_cateogories:: Product CategoryIDfk

                  product sub_sub_categories:: Product Sub_categoryIDfk = Product Sub_cateogories:: Product Sub_categoryID

                   

                  Note the difference shown in blue. You need the link from the Products table directly to Product Sub_Sub_categories in order for your conditional value lists to work during data entry. You will likely need the relationships you've set up for Reporting purposes, but you need the relationships I've described for Data Entry/Editing tasks. Fortunately, you can have both by using the button with two green plus signs in the Relationship graph to create multiple Table Occurrence boxes that all refer to the same data source table. This way you can have one set of relationships for reporting and one for data entry.

                   

                  For more on tables and table occurrences, read this thread:

                   

                  Table vs. Table Occurrence (Tutorial)

                  • 6. Re: conditional value lists do not work
                    kirvis
                      

                    Ok, it seems that I have finally succeeded in making it work. Take a look at this screenshot to see what I did:

                     

                    conditional value list TO's

                     

                    The conditional value list sub-categories is based on the blue table occurrences and relations and the conditional value list sub-sub-categories is based on the green table occurrences and relations. For the first value list I have selected "Include only related values starting from products", for the second one I have selected "Include only related values starting from products 2". 

                     

                    Furthermore, I had to connect all the (sub(sub))category ID's directly to a table occurrence from products, to be able to display the correct (sub(sub))category name. This is depicted in the red table occurrences and relations.

                     

                    Another problem I had was that the relations within the records were messed up and that there was one sub-sub-categoryID number that had two values assigned to it. I fixed that all and made all the ID fields non editable, but things like that can cause a real headache.

                     

                    I still do not understand what I did to make it work and why this has to be so difficult, but I guess that it does not matter anymore. I have the feeling that the fact that there are three levels of conditioning makes it much more complicated than just two levels (like categories and sub-categories). (Or could it be that what I did now is too complicated and is there a simpler way?)

                     

                    Thank you Phil for your help! With the small amount of information on relational value lists that is currently available, I certainly would not have managed. Neither on the Filemaker website, nor in "The Missing Manual" I could find sufficient information to tackle this problem.

                     

                    For those who are interested, I have placed the final file here