10 Replies Latest reply on Feb 1, 2011 9:17 AM by philmodjunk

    Cascading Selection Issues

    BradenDouglass

      Title

      Cascading Selection Issues

      Post

      Ok guys and gals. I have been using this form of cascading selections on multiple areas in an existing database and for some reason this specific one is giving me a ton of issues. If you jump to the estimates layout and click on the first dropdown, select material category, then you can select the material...nothing. In the value list it says by related values only...but nothing. Nothing ever appears in the related drop down list. Any help would be awesome on this one. The link below is from getcloudapp.com for everyone who is leary of url shorteners.

      http://cl.ly/4JGQ

        • 1. Re: Cascading Selection Issues
          philmodjunk

          The relationship should be linked this way:

          Estimate::MaterialCategory = MaterialSelect::FK_materialCatID

          The 2nd value list should then refer to materialCategory in both columns not just the first and the starting from field should be estimates, not material category.

          Custom Value List?

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

          • 2. Re: Cascading Selection Issues
            BradenDouglass

            Hmm Phil, thank you soo much for getting back to me quickly. Unlike you simple link to the knowledge base I am using a join table...hence where life is going tragically awry.  Are you saying break my one to many relationship betwin estimate and matSelect in favor of what you have written>?  K Thanks!

            Oh and in addition Phil, do you know of an active filemaker irc channel?

            • 3. Re: Cascading Selection Issues
              philmodjunk

              All I am recommending is a relationship and value list definition that will work for you in terms of your value list's behavior. If changes to the relationship have undesirable effects on other parts of your database, then you would need to set up a separate relationship just for your value list.

              • 4. Re: Cascading Selection Issues
                BradenDouglass

                Phil, dude you have answered many of my questions before and you are so super quick to respond, I love it. But these two responses are absolute trash. Thanks bud, way to be an awesome stewart for FileMaker

                • 5. Re: Cascading Selection Issues
                  philmodjunk

                  It's your database project, my suggested solution was made after opening and examining your uploaded file. I've set up many conditional value lists and helped many people get it working in their systems.

                  If the suggestions I made don't work for you, feel free to explain how/why it doesn't work and I'll take another swing at it to help you out.

                  • 6. Re: Cascading Selection Issues
                    philmodjunk

                    I've been taking a closer look at your file and one aspect of your current data is confusing.

                    In Material categories, MaterialCatID contains a serial number as I would expect it to, but in Materials, FK_materialCatID contains text such as Paper, PET, SYN which corresponds to values in the text field, MaterialCategory. Thus, since your first value list enters the ID number MaterialCatID into Estimates, there's no matching value to make the relationship needed for the conditional value list to work.

                    If I change the first value list to simply enter MaterialCategory as column 1 into Estimates::MaterialCategory, then I can get my suggested solution to work.

                    However, in the long run, If I were you, I'd prefer to update the records in Materials to link to Material Categories by the serial number in place of the name so that I can use a relationship based on the Category serial numbers in place of the names. That change would make it possible to update category names without breaking the relationship needed for your conditional value list.

                    If you'd like, I can update a demo copy of your file with these corrections in place.

                    • 7. Re: Cascading Selection Issues
                      BradenDouglass

                      Phil, thank you for your help yesterday, sorry if I seemed rather brash.  I realized the mistake with the Foreign Key ID being replaced with a text selection and fixed it (the key foreign key value isn't displayed on the test layout) however, things still aren't working and I am equally baffled.  Here is an updated copy, and I do want the primary key / foreign key values to be what links the material from the matcategory.

                      http://cl.ly/4K5i

                      • 8. Re: Cascading Selection Issues
                        philmodjunk

                        You don't have any values in Material::_kf_materialCatID and these fields are set to look up MaterialCategory::MaterialCategory--which won't work here as the look up changes the value in this field and breaks the relationship. Once I disabled the look up and entered numbers of 10 or 11 into this field for every record in material, the conditional value list worked as you have defined it.

                        Material::MaterialCategoy, BTW, is not a field you actually need in your material table. The corresponding text field in MaterialCategory is sufficient.

                        • 9. Re: Cascading Selection Issues
                          BradenDouglass

                          Thanks Phil, I should have gone back through and turned off that specific lookup field. What would be the most efficient way to place those numbers into the fk field on record creation? I was mistaken in thinking that field automatically gets updated, but I guess I was wrong.

                          • 10. Re: Cascading Selection Issues
                            philmodjunk

                            If it were automatcially updated, how would your system know to what category to assign it?

                            The basic two options are to either use a portal to materials on a materialCategory layout with the allow creation of records via this relationship enabled so that entering new record automatically assigns it to the category of the current MaterialCategory record or you format this field with a pop up menu or drop down list of categories so that the user assigns the category by selecting it from the value list.