9 Replies Latest reply on Aug 28, 2015 1:46 AM by rrrichie

    Conditional Value list dependent ontwo fields

    evaqa

      Hi everyone,

      I just started using Filemaker 14 and I have an issue I just can't figure out.

      I have 3 product levels that are based on each other using conditional value lists. The final amount of products is about 200 so they are divided into 3 categories and about 15 subcategories.

      There is also a field for the failure key. This is also a conditional value list based on the product.

      I now want to make it possible to have the failure key depend on the subcategory in some cases and on the product in other cases, so basically the failure key has to depend on two different fields that are both conditional value lists themselves.

      I have tried figuring it out but I can't figure out what the relationships should be like and what should the table for the failure key look like.

      I hope someone can help me.

        • 1. Re: Conditional Value list dependent ontwo fields
          rrrichie

          You might want to start looking into valuelist filled with SQL.

           

          You attach a script trigger to the onObjectEnter and a parameter you set the ValueList (if you set it in the script, it's too late for the field to use :-))

           

          You base the value list on a global.

           

          It's one of those FileMaker tricks you will learn to make FileMaker do stuff it normally can't do.

           

          http://filemakerhacks.com/2012/07/25/magic-value-lists/

           

          I also suggest submitting a feature request.  SQL based valuelist...

           

          http://www.filemaker.com/company/contact/feature_request.html

           

          Happy Coding!

           

          Ramon

          • 2. Re: Conditional Value list dependent ontwo fields
            evaqa

            Thank you for your answer!

            This seems to be in the direction of what I need, but...

            What I want to achieve is this:

             

            One of my subcategories has about 60 products. Then there is the list with failure keys that depends on the product for most subcategories. Except for this one and another one. I want to save some time by not having to type every product with its ten characteristics in the table.

            Also because I am creating this database for someone else and I don't want them to have to go to the trouble of checking all 60 products if they want to add a characteristic to that subcategory.

             

            With the value list based on SQL you still have to make a table with all the combinations, don't I?

            • 3. Re: Conditional Value list dependent ontwo fields
              user19752

              I'm not sure what "failure keys" mean, but you can use 2 value lists apply to 2 copy of a field, then set "Hide object when" expression for "condition" and place them same position.

              • 4. Re: Conditional Value list dependent ontwo fields
                rrrichie

                Well you need to get the failure keys from somewhere... If you look into the SQL value lists, you don't really have to use SQL to fill the list.  The SQL result is put in a global, that global is used as a basis for the list.

                 

                So you can just say LET ($$z_valuelist = list ("key1" ; "key2"  etc )

                 

                Where are the failure keys and subcategories now?  If they are in a table, you can just query that...

                • 5. Re: Conditional Value list dependent ontwo fields
                  guessmaster

                  Could you expand a bit what do you mean by "Failure Keys" ? I imagine you could have a drill down scheme with a script trigger that identify the Failure Keys

                  • 6. Re: Conditional Value list dependent ontwo fields
                    evaqa

                    The Failure Keys are basically ways in which a product can fail. Every product has different ways to fail and sometimes the options depend on the category (for example a table can fail in the same ways independent of its size) and sometimes they are dependent on the product itself. The user has to be able to select multiple keys.

                    • 7. Re: Conditional Value list dependent ontwo fields
                      guessmaster

                      I am very sorry but I still don't understand what fails and what action happens if the product fails.

                      • 8. Re: Conditional Value list dependent ontwo fields
                        evaqa

                        The database is meant to register when a certain product fails. Therefore the name of the product has to be included. To facilitate the selection of the product I have made a conditional value list. The way it failed (or the reason for the failure) has to be included as well. To be able to perform find functions in a usefull way the user cannot invent their own failure keys but they have to be selected (otherwise you get variations on the same words and you wont be able to perform useful find operations).

                         

                        So the thing that happens if a product fails is that the failure is put into the database.

                        • 9. Re: Conditional Value list dependent ontwo fields
                          rrrichie

                          I would add a table with category / failure_key.   And add an interface so you can maintain the failure keys.  Seems there will be new failure keys in the future.

                           

                          I also make things for repair companies and there we name it "known_issues"  :-)

                           

                          Each know_issues is specific to a phone brand/model.  Of course there are issues that occur in all, but I just duplicated the issue in the known issues table.  The know_issues are "validated" by the part suplliers/manufacturers so the do not change that much.

                           

                          The advantage of the separate table is, that I don't have to maintain it.  Someone else with no programming skills does :-)

                           

                          If you do use the value lists based on globals you can build that list in two or more parts ....

                           

                          when using SQL for example you can do something like.

                           

                          SELECT failure_keys FROM failure_key_table WHERE failure_key_table.category = ? ; product::category

                           

                          UNION

                           

                          SELECT failure_keys FROM failure_key_table WHERE failure_key_table.category = 'GENERIC'

                           

                           

                           

                          Happy Coding!