11 Replies Latest reply on Feb 7, 2016 2:47 PM by Fahri Akar

    Drop down box with SQL Server

    katieandemil

      Hi,

       

      I have recently started using File maker 14 Pro and I use it with SQL Server (back end).

       

      One of my basic needs is to have a drop down box.

       

      I have created a drop down box, however the field that holds values shows ID instead friendly name that makes sense to users.

      My questions is, is there a way to display friendly name instead of ID (Value behind the scenes, but display description from look up table), so like a normal drop down box on website and everywhere else (without workarounds or using pop up menu).

       

      Many thanks in advance

      Emil

        • 1. Re: Drop down box with SQL Server
          beverly

          without workarounds or using pop up menu

           

          Sorry, those are your options, AFAIK, Emil.

           

          beverly

          • 2. Re: Drop down box with SQL Server
            katieandemil

            That is what I was afraid of, It sounds like the drop down box is a half-finished feature when used with SQL Server? Anyone know if there are any plans to make it work properly with SQL Server (external sources)?

            • 3. Re: Drop down box with SQL Server
              beverly

              I don't believe SQL server has anything to do with the feature. If the tables were "pure FM", would they behave differently?

              beverly

              • 4. Re: Drop down box with SQL Server
                katieandemil

                Not sure, I've never tried. In that case it's overall half-finished, not just for SQL Server

                • 5. Re: Drop down box with SQL Server
                  Mike_Mitchell

                  You can use a popover containing a portal. Populate the portal using a Virtual List technique. Then script the selection to insert the value when the user clicks.

                   

                  Another option is to layer the two fields.

                   

                  Yes, these are "workarounds", but it's nonetheless very possible.

                   

                  And Beverly is right: The "limitation" is just the way dry-down lists work, ESS or not.

                  • 6. Re: Drop down box with SQL Server
                    Mike_Mitchell

                    katieandemil wrote:

                     

                    Not sure, I've never tried. In that case it's overall half-finished, not just for SQL Server

                     

                     

                    I wouldn't say that. By definition, a drop-down list allows the user to insert a different value (one that doesn't appear in the list). In order to do that, it has to be the real field (i.e., the ID). It's not meant to be used to insert a hidden ID; that's what pop-up menus are for.

                    • 7. Re: Drop down box with SQL Server
                      katieandemil

                      Thanks Mike, overlap is what I used and is ok,

                       

                      By half-finished I mean a "drop box box" (in my task) which allows to select a "friendly" value (not id) and displays the selected value for the user after it's selected.

                       

                      From usability point of view IDs should be hidden. I've used drop down boxes in different tools (MS Access, .net, reporting tools) and all of them seem to do it properly, hence I called it half finished (the only tool that I've come across that requires workarounds). There should be an option to for Value (ID) and label (display to users), this is already in the drop down box itself so I don't understand why it is not used to display it for user (instead of IDs).

                       

                      Anyway, I'll ask my file maker developer to go with workarounds.

                       

                      Emil

                      • 8. Re: Drop down box with SQL Server
                        Mike_Mitchell

                        You are correct that IDs should not be shown to the user. There is another option that does that. It's called a pop-up menu.

                         

                        I've already explained why it works the way it does. You have to allow for non-list values somehow. FileMaker doesn't have a "combo box" like some of the Microsoft products. It just has fields with interface objects attached.

                         

                        I don't agree that the feature is "half-finished" or that the Microsoft products do it "properly". Drop-down lists perform a different function than a combo box. Frankly, I'd rather have both options.

                        • 9. Re: Drop down box with SQL Server
                          katieandemil

                          We've tried pop up menus, but for longer lists it was easy to accidentally edit value and not know what was the previous value so we decided it's too risky to use it.

                           

                          All that is missing is a simple additional option to display as name (using drop down box name that was selected, and disallow manual edit).

                           

                          Proper, half-finished, missing, doesn't matter to me what we call it. The fact is, it would make my life easier if it was there... and probably a few other peoples's life

                           

                          Take care

                          Emil

                          • 10. Re: Drop down box with SQL Server
                            beverly

                            You have answered your own question. ( a "work-around" ) Use the Drop-down List, overlay it with a merge field (based on the relationship to get the value you need), format the merge field to appear as other fields.

                             

                            • if you tab into the drop-down version, it should drop-down and allow selection

                            • if you turn the merge object into a button to go to field (the drop-down formatted field), it will function correctly

                            • the merge field will not be available for finds (it's not an enterable field), so If you need that - use the related field for the overlay (not as a merge field) and do not allow entry except for find mode.

                             

                            You are trying to put a pretty face on SQL using FileMaker. They do what the do...

                            beverly

                            • 11. Re: Drop down box with SQL Server
                              Fahri Akar

                              If you use two field in a value list "Show values only from second field" option on or off, "Auto-complete using value list" option disables on drop down lists. This is another issue.

                               

                              To bypass showing id in the field and to enable auto-complete option, I use two fields. But the values must be unique in that case. Creating a relationship based on this unique value and insert the id with auto-lookup to the hidden id field. Then using id relationship.

                               

                              As katieandemil pointed, if we could have a chance in inspector  "Show only the second field's value" and auto complete options, it would be much more usefull. But it is problematic for the programmers. This means every field will have two value area. One is to show and the other real hidden value. It is related to the software's structure. Hope to have an option in future releases.

                               

                              Fahri