13 Replies Latest reply on Sep 24, 2013 9:21 AM by Stephen Huston

    Issue with value lists

    coherentkris

      A coworker and I discovered an issue with value lists and popups.

       

      if you have table 1 that has:

      pk recid value

      1 2 abe

      2 4 abe

      3 1 beth

      4 5 joe

      5 6 mary

       

      and table 2

      pk fk

      1 1

      2 2

      3 4

      4 5

       

      and a pop up control on a layout based on table 2 with the values from table 1 pk and value list, show only value

       

      the records for fk show

      abe

      2

      joe

      mary.

       

      It's easier to explain if you look at the attached file.

      any suggestions on how to deal with this issue?

       

      Thx,

      Kris

        • 1. Re: Issue with value lists
          erolst

          This is not a bug; this is the proper behaviour for a value list, since it is basically an index, i.e. a list of unique values.

           

          In your sample file, you have 6 primary keys, but only 3 distinct names; so when you create a value list with both fields, the shorter index "wins", i. e. the value list has only 3 entries, not 6, because that is enough to show all unique occurrences (otherwise FM would have to create artificial index entries). You can see this by deselecting the “Show only values from second field” option.

           

          So when you select Abe, you actually enter the ID 3, and 6 isn't in the value list – and that is why FM can't use the value list to resolve the ID 6 into the corresponding name.

           

          The solution? Give Abe and the others unique names, e.g. use a fullName field (concatenation of firstname/surname); FileMaker can't tell those guys apart, how do you do it? If that doesn't produce unique strings either, or for other reasons is not an option, think about using a portal as selection tool.

           

          Look here:

          • 2. Re: Issue with value lists
            coherentkris

            Bug or not bug is irrelevant... regretted it after the click to post.

            Whatever you want to call it i need to have the popup control display the value not the fk number.

            pk 1 abe and pk 3 abe are valid entries in the fk field.

            • 3. Re: Issue with value lists
              erolst

              coherentkris wrote:

               

              Bug or not bug is irrelevant... regretted it after the click to post.

              Whatever you want to call it i need to have the popup control display the value not the fk number.

              pk 1 abe and pk 3 abe are valid entries in the fk field.

               

              The validity of the entry has nothing to do with the mechanism used to put it in, i.e. the way a value list works. Either make sure that the values are unique, or use another entry tool.

               

              btw, if this would work as you wish, how would the user know which of those Abes he is actually selecting when there is nothing to differentiate them?

               

              And, of course, you're welcome.

              • 4. Re: Issue with value lists
                coherentkris

                erolst,

                In the solution at hand the popup control is not used to actually perform the selection of an employee.

                Selection is handled thru a person selector routine that spawns a new window with a list of employees... you get the idea.

                The selector window list is only those ACTIVE employees hence the problem.1 abe active and 2 abe inactive are valid selections in history.

                The same employee selector routine also functions in find mode so....the popup control should show the selected employee while in find mode.

                These design choices are being driven by customer requirements.

                But i digress....

                 

                Still need a viable solution..      

                 

                http://www.huffingtonpost.com/lisa-mirza-grotts/minding-other-peoples-man_b_498674.html

                • 5. Re: Issue with value lists
                  Mike_Mitchell

                  I suggest you construct your value list based on a relationship, rather than all records. Base it on a constant in the current table (such as the word "active") tied to the status in the target table (which would be "active" in the case of active employees). In this way, the value list is filtered to show only those employees who meet your criteria.

                   

                  Of course, you can adjust for your actual situation. But you get the idea: If the value list is filtered to show only the valid choices, then the invalid duplicates will fall out.

                   

                  HTH

                   

                  Mike

                  • 6. Re: Issue with value lists
                    coherentkris

                    Mike,

                    I guess I don't undeerstand how your proposed solution helps me here.. I need the poup control to show an employee name regardless of status.

                    In a perfect world the employee records would have only one value active or inactive and each employee would only have one record but that is not the data case here as we are tied to external data structures.

                    • 7. Re: Issue with value lists
                      erolst

                      http://www.huffingtonpost.com/lisa-mirza-grotts/minding-other-peoples-man_b_498674.html

                       

                      Interesting that you feel the need to quote a self-styled etiquette expert, instead of simply taking a page from her book; not to mention that you're probably oblivious to the paradox therein …

                      • 8. Re: Issue with value lists
                        Stephen Huston

                        Using "related" values list to show existing content while in Find Mode is problematic. There is no current record while in Find Mode, so there are no related records to show. I can think of about 3 possible work-arounds, depending on your needs:

                        1. build Value List based on existing record values (without limiting to related values only) to show all valid indexed values for a search;
                        2. use multiple find fields with the first one extablishing the relationship filled in  to get the related values in the second;
                        3. capture the related values in a variable from an existing record before entering find mode, and load/set that captured data into a global field which could  serve as the source field for a Value List which you could then access in Find Mode.

                        I hope one of those helps you solve it.

                        • 9. Re: Issue with value lists
                          coherentkris

                          erolst,

                          I thought it was a funny observation....lets not go any further with the manners thing.

                          • 10. Re: Issue with value lists
                            coherentkris

                            Steven...interesting proposals. I have some trial and error work to do.

                             

                            ps i understand that this is problematic at best and that the solution is a workaround for Filemaker Inc's intended behavior in the product.

                            • 11. Re: Issue with value lists
                              Mike_Mitchell

                              I guess I saw this:

                               

                              "The selector window list is only those ACTIVE employees hence the problem.1 abe active and 2 abe inactive are valid selections in history."

                               

                              and assumed you wanted to filter the value list. My apologies.

                               

                              Nevertheless, Stephen is right. A filtered value list is problematic in Find mode.

                               

                              But erolst is also right. Your fundamental issue is that you have two identical values in the list that correspond to two distinctly different records. In order to solve that, you'll need to come up with distinctly different values in the list to distinguish them - such as a first name + last name, or concatenate the key onto them.

                               

                              Another question I would have is, can you base the value list on something other than the history table? Or is the problem just that you have two people named "Abe"?

                               

                              Or, use Stephen's script-based method to filter the list prior to entering Find mode.

                               

                              HTH

                               

                              Mike

                              • 12. Re: Issue with value lists
                                coherentkris

                                we have a winner!!!

                                item 1 from Steven did the trick...

                                • 13. Re: Issue with value lists
                                  Stephen Huston

                                  Glad to hear you got it resolved.