2 Replies Latest reply on Oct 25, 2011 5:54 PM by JoelMG

    Issue with Value Look-Up Lists with Second Field

    JoelMG

      Title

      Issue with Value Look-Up Lists with Second Field

      Post

      Here is a weird one that got me into trouble.  If anyone has an explanation, it would help in the future.

      I set up a pop-up text field in a layout from which the user selects from a value list of two fields (School Name and School ID) from a related table.  Someone had mistakenly duplicated a school record, so that there were now two records in the related table with the same School Name but two different School IDs (autoentered, serial).  The Value List only showed one School Name and one of the IDs and ignored the other.  That was a small glitch that really didn't matter much to me.

      However, when the user selected in the pop-up list a school below (alphabetically) this school, the School Name shown upon mouse release was the selected one but the School ID was now off-register by one record.  The School ID directly below the problem school actually listed the second School ID of the problem school, and every school afterwards had an ID coresponding to the school above it. 

      I am surprised that the Value List did not list both records, with the duplicated School Name and the two School IDs.  That would correspond to the records upon which the value list was based.  Is there any way of avoiding this problem if the user inadvertantly duplicates a record again?  If not, I should eliminate this Value List to avoid problems in the future.  I tried Validation options, but that didn't help.

      Thanks.

      Joel Goodman

        • 1. Re: Issue with Value Look-Up Lists with Second Field
          philmodjunk

          I am surprised that the Value List did not list both records, with the duplicated School Name and the two School IDs.

          This is known and expected behavior for a 2 field value list where you either hide the values from the first field or sort on the values from the second field due to the way FileMaker draws these values from the fields's indexes rather than the actual fields.

          Think about it. If you saw this data in your list:

          2 Jefferson High School
          45 Jefferson High School

          How does your user know which one to select? Enforcing a unique values validation rule on the Name field is a good way to ensure that you don't get two records with the same name in your schools table. If duplicate names are possible, it's a good idea to include data from more than just the one name field. in this school name example, you could define a calculation field that combines school name and street address in the same field and then you'd use that field as your second field so your users can see enough info to select between schools of the same name.

          The other issue sounds a bit like a bug previously reported click the link below and see if you agree. If you think it is different, please post an issue report in the Report an Issue section of this forum.

          For More Information see:     Pop-up field displays incorrect value

          This is one of many acknowledged bugs that can be found in the Known Bug List here in the Report an Issue section of the forum.

          It can also be downloaded as a database file from:    http://www.4shared.com/file/8orL8apk/FMP_Bugs.html

          • 2. Re: Issue with Value Look-Up Lists with Second Field
            JoelMG

            Yes, this makes so much sense!  I'll solve the issue with a concatenation field.  And a unique values validation rule is also a great idea!!

            And it seems that bug you suggested is alive and well in FMP v11!

            Thank you.

             

            Joel