12 Replies Latest reply on Mar 30, 2017 7:55 AM by philmodjunk

    Value List Second Field!


      Hello Everybody!


      I have a value list based on: 1. First Field - a primary key of a related table and 2. Second Field - a calculation field (First & last name) or a text field on the same related table with the "Show only values from second field" checked. Ta list works fine when it clicked and displays the Names correctly exept when you choose the desired name and close the list, it displays the primary key (which is the one actually saved).


      Is there any other way to display ONLY the Name (but save the id for relational purposes) ?. And by other, i mean the old trick of hiding the value list in back and align a new field the the name (not editable in browse mode) on top of the value list?


      I'm daring to ask since i think it is old any maybe someone has build another workaround about it.


      Thank you very much in advance.



        • 1. Re: Value List Second Field!

          There are two basic methods:


          1) Use a pop-up menu instead of a drop-down list.


          2) Use a popover or separate window that shows all the options, and script the insertion of the primary key when the user clicks on one.


          Option 1 is really easy, but pop-up menus can be cumbersome with large data sets. Option 2 is more work, but it's a lot more flexible (you can completely control the list of options via a Virtual List and portal or a List view and a Find).





          • 2. Re: Value List Second Field!

            I'm already using the second option (since i hate!! pop-ups) in all the another tables  but i wondering if could spare the scripting! It seems i don't... :-((

            This is something that FM inc should (-Finally-) "fix" at some point!


            But, anyway thanx (again!) for the immediate response!



            • 3. Re: Value List Second Field!

              What I typically do is have a single set of scripts that I control with parameters to populate the portal in a popover. That way, you can just copy the object to wherever you need it, update the context and set the parameters. Then it just works.

              • 4. Re: Value List Second Field!

                Can you send some kind of sample? 'Cause i'm using UTL tables and Custom ( ), especially when i want to filter the value list. (e.g. to exclude the already selected and related records) in an effort to any avoid refresh issues, either with portals or not.

                • 5. Re: Value List Second Field!

                  In other words, i'm always (almost) set a calculation field as a basis for a value list (conditional or not).

                  • 6. Re: Value List Second Field!

                    NickChristie wrote:


                    In other words, i'm always (almost) set a calculation field as a basis for a value list (conditional or not).



                    I wouldn't do that. It locks your schema in and, if you ever have to change something, you have to change the database structure itself. Plus, it's not very flexible; you have to recode it for every value list.


                    What I'm talking about is using a Virtual List technique in a portal, where you populate one or more global variables with the list values. You then have a series of calculation fields (unstored) in the table that do something like:


                         GetValue ( $$list ; row )


                    where row is a serial number. So you might have on variable that holds the names, and looks like this:






                    and another list that holds the IDs, like






                    When you populate the portal, you just show the field corresponding to the name.


                    I don't have a convenient sample I can post. Everything I have is built into existing solutions, and it's pretty involved (because I'm using ExecuteSQL to create the lists, it's dependent on a certain Relationships Graph model, and there are some custom functions involved). But it's really not that hard to figure out; just write some scripting that fetches the right values, puts them in a list, and populates the table. There are a lot of resources online about the Virtual List technique (not to mention it's covered in the FileMaker Training Series Advanced). Just remember that the general principle is to use a single set of scripts and pass parameters in so you don't have to recode the thing to use it in a new context.


                    If I have time, I might try to create a demo file for situations like this. In the meantime, play with it!



                    • 7. Re: Value List Second Field!

                      I just came up with another workaround - much simpler: put two fields on your form

                      • first one is the related Calculation field - so you see the actual nice value
                      • second one is the list as composed with the 2 fields, "Show only Second" enabled, as a pop-up menu

                      But make the second input field so narrow, that only the pop-up opener is visible. Put second field just to the right of the first field, as if the second field IS the pop-up opener.


                      Bonus points if you make the first field read-only, to make sure the user only uses the pop-up menu to alter values.


                      Filemaker example.png

                      In my example:

                      • ::Codenaam is the name of teacher
                      • the little box next to it: the value list pop-up menu, now happily NOT showing its boring ID value


                      Net end result:

                      • you see the comprehensible value displayed
                      • when changing value, you open up the pop-up menu - showing also comprehensible values


                      No scripting, fly-overs or other hero-stuff needed ;-)

                      • 8. Re: Value List Second Field!

                        You don't have to make the field narrow. You can place the drop down field behind the "display" field as long as this isn't web direct. This gives the user a larger target to click and keeps the field behavior the same as other value list formatted fields. 

                        • 9. Re: Value List Second Field!


                          • looks fine initially: text visible, pop-up opener next to it
                          • popup shows in same place as text field - nice
                          • but after choosing a value, the pop-up field remains in front (being the active field), thus hiding the text-field and showing the ID...


                          You have to select another field to hide the ID field again.

                          So no-go for your enhancement...

                          • 10. Re: Value List Second Field!

                            I've used this method for years without any such issue.

                            • 11. Re: Value List Second Field!

                              Hi phil, could it be you use a dropdown menu, not a pop-up menu?

                              Looks better indeed with dropdown menu.

                              • 12. Re: Value List Second Field!

                                You would only use this with a drop down list. You would not use it with a pop up menu as there's no need. This is a trick to get pop up menu behavior with a drop down list.


                                It does make a difference if there is no other object on the field in the tab order. The act of selecting a value is supposed to move the focus to the next object in the tab order, but this does not happen if there is no other object on the layout in the tab order. In that case, you have to click outside the drop down before the drop down list retracts and drops back behind the name field.


                                You also will see any previously selected ID value in the field while selecting the value. This can be hidden by setting very large left or right padding on that drop down list field--rgordon shared that trick with me some time ago.