11 Replies Latest reply on Jul 29, 2013 11:17 AM by deathrobot

    Related field in pop-up or dropdown

    deathrobot

      Title

      Related field in pop-up or dropdown

      Post

           I have a field from a related table which I can put in either a popup or a dropdown menu. In either case, I am storing the foreign id but have the menu set to display only the second field. It appears Filemaker makes you choose between two evils:

           1) If I choose a popup menu, everything looks great but I can't access it by tabbing into the field

           2) If I choose a dropdown, I can tab into the field but the field displays the key, not the value. I know you can "fix" this by placing a non-enterable field over the dropdown, but when selecting from the dropdown one foreign id always shows at the top.

           Is there some trick to get the best of both worlds?

           Thanks,

           Michael

        • 1. Re: Related field in pop-up or dropdown
          philmodjunk
               

                    but when selecting from the dropdown one foreign id always shows at the top.

               Can you describe that in more detail? I don't see why that would be the case.

               Another option that can sometimes be used is to invert the whole setup and use the name field to look up the ID. This enables you to use auto-complete on the name and can gracefully handle situations where you have more than one record with exactly the same name--something that will result in an omitted value in your list with the standard ID in field 1, name in field 2 value list.

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

                

          • 2. Re: Related field in pop-up or dropdown
            deathrobot

                 Attached is an image to show what I mean. This is the People table which is a child of the Company table, related by primary and foreign key (company::id -< people::id_company). The dropdown/popup value list uses values from "first field" (id) but is set to only show values from "second field" (company name). The dropdown menu DOES show only the company name, except in the field itself, which shows the id.

                 In the meantime, I'll take a look at the demos you linked to.

                 Thank,

                 Michael

            • 3. Re: Related field in pop-up or dropdown
              philmodjunk

                   OK, hadn't noticed that little wrinkle before that a previously entered ID shows in the field.

                   I suppose you can use OnObjectEnter to perform a script that clears the field when you enter it, but then you have an issue when the user clicks in the field and then decides not to change the current value... That could also be handled with scripting, but I'm not sure that it's worth the effort.

              • 4. Re: Related field in pop-up or dropdown
                deathrobot

                     Yeah, I might just live with it. But setting a variable, clearing the field, and then resetting the field if empty seems like a viable solution if it remains too bothersome to see the id, especially becuase I'm now using UUIDs which are uglier than the 2-digit number in my example. I also downloaded your demo file, and will be looking at that soon.

                     Thanks!

                     Michael

                • 5. Re: Related field in pop-up or dropdown
                  philmodjunk

                       Let's say your field is called Invoices::ContactID. Set up an OnObjectEnter script trigger and enter Invoices::ContactID into the optional script parameter box. Then your script has these steps:

                       Set Variable [$$PrevValue ; value: Get ( ScriptParameter ) ]
                       Set Field [Invoices::ContactID ; "" ]

                       That saves the original value in a global field before clearing the field.

                       Then use this script with the OnObjectSave trigger:

                       If [ IsEmpty ( Invoices::ContactID ) ]
                          Set Field [ Invoices::ContactID ; $$PrevValue ]
                       End If

                  • 6. Re: Related field in pop-up or dropdown
                    deathrobot

                         Hi Phil,

                         I was playing with this very idea yesterday. The only problem is you are unable to CLEAR the company name field. I know could create a button whose sole purpose was to clear the field, but not sure if the clutter is worth it. Still thinking…

                         Michael

                    • 7. Re: Related field in pop-up or dropdown
                      philmodjunk

                           Perhaps it would be better not to use Get (UUID). The only reason to use it instead of an auto-entered serial number is when you are creating records in distributed copies of your database (such as several ipads/iphones) that are then merged back into a central file with the primary key values still unique.

                      • 8. Re: Related field in pop-up or dropdown
                        deathrobot

                             Okay, this is very close to being elegant…

                             For state "In Focus", make:
                             font size = 1
                             tyle = condensed and subscript
                             alignment = bottom
                             text color = 76% gray

                             This only affects the field font itself (my UUID); the font for the dropdown is not affected. Only weirdness is just the bottom strip of the field is highlighted, and there's a small black dot in the lower left of the field. I'll probably keep experimenting.

                             Michael

                        • 9. Re: Related field in pop-up or dropdown
                          deathrobot

                               We seem to have posted almost at the same time :) I decided to use UUID because I don't know if we will be using the database more remotely in the future; figured I'd rather start now than find I have to redo all the ID's in the future.

                               Thanks as usual for all your help in this forum!

                               Michael

                          • 10. Re: Related field in pop-up or dropdown
                            philmodjunk

                                 Hmmm, maybe you can add an extra field in here that's empty instead of the ID match field and format it with the value list. Use the OnObjectSave trigger to set the Match field to the value entered into this field, then clear that extra field...

                            • 11. Re: Related field in pop-up or dropdown
                              deathrobot

                                   That's a good idea. I'll try it.

                                   Michael