7 Replies Latest reply on Jul 24, 2013 9:26 AM by HenryMeadors

    Associate a field with a drop-down list



      Associate a field with a drop-down list


           In the form I'm creating, I have a drop-down list where the user can select from a predefined list of species. Next to that, I have a field called "SP. CODE" which is a number associated with the species name. What I want is for that field to automatically display the species number when you select a species name from the drop-down, if that makes any sense at all.


           I already have a defined value list with the species codes listed, I just need to figure out how to make the SP CODE field associate those numbers with the species names from the drop-down, and display them automatically (and, if possibly, prevent the user from being able to edit those displayed values - so as long as a species name is chosen, there MUST be a species code displayed...like maybe "grey out" the field?)

           So, in a nutshell, if SPECIES X is sp. code 24, then when you select SPECIES X from the SPECIES drop-down, I want the field SP. CODE to automatically display a "greyed-out" (uneditable) "24".


           There's a screenshot attached so you can see how my form is constructed, and see where the drop-down and code field are located. I would appreciate any help!


        • 1. Re: Associate a field with a drop-down list

               What you need is a related table where you store the species name in one field and the species code in another field.

               This is actually so simple you will be hitting yourself.

               Then you need to create a relationship between the species field in this table and the species name in the "Species" table.

               When you select a species from the dropdown the Species Number field from the "Species" table can be put on the layout and it will display the species number from the related species record.

               The source for your species dropdown list should be the Species Name field in your species table.

               Like in this example:


               However with this technique you can run into trouble if you ever mistype a species name. It's better to use the Unique Serial number ID in the relationship like so:


               Do keep in mind you will need to use a little trick to create a nice dropdown:

               I made you two example layouts in that second file and the second layout might really be the best way to go.

               There you have the ability to create new species directly from within the layout and you have the ability to add as many as you like.

               This is the most flexible way to go.




          • 2. Re: Associate a field with a drop-down list

                 If you don't want your users to mess around with the species codes you can just restrict the adding of species to the species table itself and set the species code field to not allow field entry in browse mode.

                 That way you can't edit the species code field.

            • 3. Re: Associate a field with a drop-down list

                   Set up a related table of species names and codes. Selecting the species name from a value list set up with the species code as field 1 and the species name as field 2 will allow the user to select a species by name and yet the value list enters the code. You can then add the name field from this related table to automatically display the name in addition to the code entered by selecting that name from the value list.

                   And if your list of species is long enough that such a value list is not as easy to work with as you'd like, you might consider this approach:

                   FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                   Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

                   This demo works with a contact name and contact ID, but that's just a case of renaming thsoe two fields. WIth this demo file, the name drop down is auto-complete enabled and scripting is included to handle duplicate names. WIth your value list, you aren't likely to have to deal with duplicate names, but the ability to auto-complete on the value list may help if your list of names is fairly long.

              • 4. Re: Associate a field with a drop-down list

                     Thanks, guys, I'll try this stuff out tomorrow when I get back to work. I appreciate the help :)

                • 5. Re: Associate a field with a drop-down list

                       Okay, I got this working - DaSaint's method worked best for me, thanks a ton :)


                       Now I have a new question, if you don't mind me just adding on to this post instead of making a whole new one:

                       I added a new field called "ROWID" which acts sort of like a serial number for each individual record (it'll be the row's unique identifier once the data from this form is exported into an Excel sheet). Currently, I have the "Auto-Enter" set to increment the number by one each time a new record is created, which is perfectly fine and straighforward.

                       The thing I run into is if I delete a record, that number doesn't go back down. So if records 1 through 5 had the respective ROWIDs 1-5, and I delete record 5, then create a new record, NOW record 5 has ROWID 6.

                       How can I make it assign a serial (or, ROWID) which will correspond to its actual record number?


                       EDIT: Nevermind, I figured it out -   Get (RecordNumber) was what I wanted :)

                  • 6. Re: Associate a field with a drop-down list

                         Get ( RecordNumber) might easily be the wrong option here. This function returns the position of a record in the current found set. Perform a find, or sort the records into a different order and the value returned by Get ( RecordNumber ) changes.

                    • 7. Re: Associate a field with a drop-down list

                           That's okay, though, because the plan is to have all the records spit out into an Excel sheet once the field people are done entering them. To be honest, it wasn't completely crucial that I solve the ROWID problem - its entire purpose was really for Excel. Really, I just needed the field to exist, and if the numbers get jumbled when I sort the data later, it's not that big of a deal - I can always just clear the column in Excel once it's sorted how I want, and then re-enter the count from the top - it'd take like five seconds to fix. 

                           Having said that, if you have an idea for how I can do it properly, and not make that little bit of extra work for myself later, I'm all ears :)