5 Replies Latest reply on Jul 2, 2013 10:00 AM by philmodjunk

    Drop Down Issue



      Drop Down Issue



           I have a small database for managing of property data in an organization.

           Three of the tables are:

           Contacts (what data base can live without one?) (supplies info to other tables – will include members, vendors, anyone)

           Property (a record is an item of property) (pulls “owner” of the item from Contacts,)

           Projects (a record is created when something needs fixing, or something done to it, or an action taken) (pulls info from Property and Contacts)

           All data pulls are through relationships of Foreign to Primary Keys, with Drop Down Lists in the Foreign Key fields of the related tables. The value list is set up to show values from another field, from the donor table, which displays the primary key and field desired (in the value list setup, “also display values from second field”). The value list is sorted by this second field. These second fields populate in their respective fields in the receiving table.

           The issue:

           Some of the fields I am pulling into related tables have more than one duplicate value, differentiated by another field in that table (eg – several “desks”, differentiated in separate records by different “locations”, a separate field.)

           When viewing the Drop Down List, these “duplicate” field values show only one record, or Primary Key (the first one), as if FMP stops at the first one, even though there are more, with different Primary Keys, in the donor table.

           How can I get all of them to show in the Drop Down?

        • 1. Re: Drop Down Issue

               Think about it from the user's perspective. If the user clicks on the value list and were able to see either:



               23 Desks
               234 Desks

               How would he determine which value is the Desk option he needs to select in a given situation?

               There are two possible approaches to consider:

               The simplest is to set up a text field with an auto-enter calculation that combines the field you are using currently as the "second field" with the other field or fields that contain the additional information. Then you can use this field as your second field so that the value list now has unique data for each option previously listed as duplicates and the user sees enough data to be able to make an informed choice.

               THe other option is to set up your value list so that only one value is possible in our "desk" example, but then a script triggered by the user selecting the value checks for the duplicates and presents the user with a list of those duplicates along with additional info so that the user can narrow the selection to the correct value. Typically a list view layout or a portal is used with a button for selecting the correct value from the list of duplicates. The script can change layouts or open a new window for making this selection.

          • 2. Re: Drop Down Issue

                 Forgot a detail: The reason that I am suggesting a text field with an auto-enter calculation is so that you can set a unique values validation setting on the field to make sure that you don't get two records with exactly the same text in this field.

            • 3. Re: Drop Down Issue

                   FMJ - as usual, you give clean and good advice. Funny thing here, I've done this calculated combination field before to solve similar unique/duplicate problems. It's perfect for this, and you are right about the user's perspective.

                   Thank you

              • 4. Re: Drop Down Issue

                     FMJ - just a follow up - I left something out of my original post; the location field that made the record differenciation (that you suggested combining in the calculation field) is pulled by relationship to another table (Locations). I didn't realize that this would affect your solution.

                     FMPro would not let me use a calculation field (pulling from another table) in the Drop Down because it "can't be indexed", if I understood correctly. So, I created another field in the Property table that looks up the location data from the Location table, then used that field in the calculation, which works fine.

                     Was there an easier way?

                • 5. Re: Drop Down Issue

                       Can't think of any easier way to do this. The field has to be indexed, but if you are using an auto-enter calculation instead of a field of type calculation, you don't need the separate field to look this up, the auto-enter calculation can pull in the related data.

                       But with either approach, be care of situations where you need to edit the data in the related table. Those changes won't update existing records in the table that is looking up the location data for use in this value list. Sometimes you need to set a script trigger on that field to perform a script to update values in the other table any time a value is changed.