12 Replies Latest reply on Apr 16, 2017 3:31 PM by unclemito

    To have a Value List affect another Value List

    unclemito

      Dear Community.

       

      I have two fields that is populated by their respective pop up drop down of a value list.

      Status Field and it's Status Value List

      Need Field and it's Need Value List

       

      The Data Entry person will update the Status Field which in turn affects the Need Field; 95% of the time, the Status Value List has a corresponding outcome on the Need Value List.

       

      What I want is for FM Pro to automatically update the Need Field when the Status Field is updated but with the further option to manually change the Need Field after the automatic update if the Data Entry needs to do so.

       

      If anybody can point in the right direction. That will be great.

       

      Regards

        • 1. Re: To have a Value List affect another Value List
          philmodjunk

          The Data Entry person will update the Status Field which in turn affects the Need Field; 95% of the time, the Status Value List has a corresponding outcome on the Need Value List.

           

          What I want is for FM Pro to automatically update the Need Field when the Status Field is updated but with the further option to manually change the Need Field after the automatic update if the Data Entry needs to do so.

           

          I'm having trouble picturing what exactly you mean by that. I can't tell if selecting a status changes the list of values that appear in the Need field or if selecting a status should simply enter a value into the Need field.

           

          Both are quite possible, but implementation is quite different for those two different results. Can you explain further or provide an example of what you need to have happen here?

          • 2. Re: To have a Value List affect another Value List
            unclemito

            The latter.

             

            As of right now, both the Status Field and the Need Field have their own respective value lists; Each field has only a single value list assigned to that field.

             

            A Value chosen in the Status Field's Value List usually indicates (Most of the time) a specific resulting Value in the Need Field's Value List.

             

            At the moment, the data entry person that enters or updates a Value in the Status Field has to update the Value in the Need Field manually.

             

            So, I want FM Pro to automatically populate the Need Field  based upon the value chosen by the Data Entry person in the Status Field. Then I would like the Data Entry person to be able to override what FM Pro automatically populated in a Need Field if the circumstances dictate that.

             

            Hope this clear things up.

            • 3. Re: To have a Value List affect another Value List
              philmodjunk

              I'm sorry but you just repeated your first post. I can read what you describe two ways.

               

              Say that you have two fields, Category and Value. Category has the value list: Animal, Vegetable, Mineral

               

              Example 1:

              If you select "animal" in Category, The "value" field now has the value list: "Cat, dog, horse". If you select "vegetable", the value list changes to be "tree", "Carrot", "broccoli".

               

              Example 2:

              If you select animal in Category, the value "horse" appears in the value field. If you select "vegetable", the value "Carrot" appears in the field.

               

              Example 1 is a conditional value list

               

              Example 2: is an auto-entry field option--either a looked up value or an auto-entered calculation.

              • 5. Re: To have a Value List affect another Value List
                philmodjunk

                Then set up a table with at least two fields, Status and Needs. In this table, status should be a unique value. For each status, place the need that you want to appear in your original Need field in the need field.

                 

                Define this relationship:

                 

                OriginalTable::Status = DefaultNeeds::Status

                 

                Set up OriginalTable::Need to auto-enter or look up (both work) the value of DefaultNeeds::Need

                 

                OriginalTable::Need remains editable so if the typical (Need) for the selected status is not the value that you need for the current record, you can still click or tab into the field and select from the value list.

                • 6. Re: To have a Value List affect another Value List
                  unclemito

                  philmodjunk wrote:

                   

                  Then set up a table with at least two fields, Status and Needs. In this table, status should be a unique value. For each status, place the need that you want to appear in your original Need field in the need field.

                   

                  Define this relationship:

                   

                  OriginalTable::Status = DefaultNeeds::Status

                   

                  Set up OriginalTable::Need to auto-enter or look up (both work) the value of DefaultNeeds::Need

                   

                  OriginalTable::Need remains editable so if the typical (Need) for the selected status is not the value that you need for the current record, you can still click or tab into the field and select from the value list.

                  "For each status, place the need that you want to appear in your original Need field in the need field."

                   

                  Not sure what to do here; Do I place the formula in the data area of the Options for Field "Status" and callout the relationship between each Status and its associated Need?

                  • 7. Re: To have a Value List affect another Value List
                    philmodjunk

                    No formula, just values and just one relationship assuming that you have only one field in your records where you specify the status.

                     

                    Using my last example, you would have values of :

                     

                    Animal      |    Horse

                    Vegetable |    Carrot

                    Mineral     |    Iron

                     

                    When you select "Animal" in the category field, "horse" is copied into the "value" field. If you select Vegetable, Carrot is looked up instead.

                    • 8. Re: To have a Value List affect another Value List
                      unclemito

                      Thanks for the clarification.

                       

                      Yes, the one relationship as you described.

                      Do I place these values in the field option's data section of the 1st field?

                      • 9. Re: To have a Value List affect another Value List
                        philmodjunk

                        Do I place these values in the field option's data section of the 1st field?

                        Only if you want that value to appear in that field automatically in every new record. That might even preclude the need for the look up table--depending one whether you want only a single value to always be set to the second field or if there is a set of values you want to appear in the second field as determined by what value you enter/select in the first field.

                        • 10. Re: To have a Value List affect another Value List
                          unclemito

                          Ok, so where would one define the relationship.

                          • 11. Re: To have a Value List affect another Value List
                            philmodjunk

                            Where you define any FileMaker Relationship outside of an SQL query:

                             

                            Manage Database | Relationships

                            • 12. Re: To have a Value List affect another Value List
                              unclemito

                              Ok, I haven't looked at this area of FM Pro since I was learning primary keys.

                              Have to review on Lynda.com

                               

                              Thanks