4 Replies Latest reply on Feb 11, 2014 10:16 AM by BMyers

    Auto Complete in Related Field Pop-Up Menu

    BMyers

      Hello, I'm new to FMP. I'm not a developer, but I've been using databases for a while and I understand the concepts pretty well. There is a workflow that I would expect in FMP but I cannot find how to do it.

       

      Short description: In a layout, when a related field appears in a pop-up menu using a related-field value list, then FMP display the entire list in the pop-up menu. FMP allows the list to be sorted by the second field. What I need is to be able to type the text of what I want to find in the second field and have the list go to that portion of the alphabet.

       

      Long description: Imagine a DB with two tables, Meeting and Project, with a one-to-many relationship (many Meetings for one Project). With the primary key and foreign key fields defined, the next step is to put the related field on my layout. Working from my Meetings layout, I define a related field specify that it will display the Project foreign key. In the Inspector, I choose a pop-up menu using a value list. I define the value list by the Project table primary key plus the Project table ProjectName field, as sorted on the ProjectName field.

       

      So far so good, the field appears on the layout, and when I click on the field a list appears with the PK on the left and ProjectName on the right, and it is sorted by ProjectName. This would be great if I had only a few projects. Once that list gets very long I would like to be able to simply begin typing the name of the project and have the highlighted item skip down the list to the appropriate place, auto-complete style. How do I get the system to do this action? I have been through the Help pdf, the 1300 page User Guide, the FMP training series, and even the Lynda series looking for what I would think would be easy and standard. I must be missing something pretty obvious because I can't find it anywhere.

       

      Question #2: the layout field described above displays both the PK and the Project Name. I don't need the layout to show the PK; it's just a unique number. I would prefer the field to display only the ProjectName. The only trick I've been able to find is to define a second related field on the Meetings layout, and then pick the ProjectName from the Projects Table. Make it a plain Edit Box, and don't allow field entry in Edit or Browse modes. Slide this second field over the first field, and leave only the pop-up arrow showing from the first field. To enter data: since the second field doesn't allow data entry, click on the arrow, and the first field will cover the second (come to the front) and the pop-up value list will appear. After selection, tab or click to go to any other field and the second field will then re-cover the first and display only the ProjectName. Nice trick, but do we really have to hack it?

       

       

      Thanks!

        • 1. Re: Auto Complete in Related Field Pop-Up Menu
          erolst

          Use a combination of a minified drop-down and the related field. See attached file.

          BMyers wrote:

          Question #2: the layout field described above displays both the PK and the Project Name.  I don't need the layout to show the PK; it's just a unique number.  I would prefer the field to display only the ProjectName.

          There's actually a setting in the Edit Value List dialog for that: 'Show values only from second field'.

          • 2. Re: Auto Complete in Related Field Pop-Up Menu
            BMyers

            Yep, something obvious and easy.  I am officially the biggest idiot on the planet.  That gave me a Monday morning laugh.

             

            Follow up question: Is there a way to filter the pop-up list?  There is a checkbox for current projects, but it would be optimal to be able to show only the active ones.

             

            Also, small but handy: When filling out a record, FMP allows the user to tab between fields for fast data entry.  When tabbing to this related field, is there a keystroke that activates the pop-up menu?  It is a small thing but it is a bit of a hassle to move the cursor to the related field and click.

             

            Thanks again.

            • 3. Re: Auto Complete in Related Field Pop-Up Menu
              erolst

              BMyers wrote:

              Follow up question: Is there a way to filter the pop-up list?  There is a checkbox for current projects, but it would be optimal to be able to show only the active ones.

              You can filter the value list, and there must be a gazillion of other threads floating in this (and other) forums dealing with that topic; but find attached another implementation with minimal overhead. (btw, is there a difference between active and current …?)

               

              Note that for this to work, the active field in your projects table must no be empty, but set to 0 to denote the inactive status. If you also want to only see the inactive projects, you need a slightly different approach.

              BMyers wrote:

              When tabbing to this related field, is there a keystroke that activates the pop-up menu?  It is a small thing but it is a bit of a hassle to move the cursor to the related field and click.

              It's actually a drop-down, and this is why, if you simply add it to the tab order, it will open when you navigate to it (which is actually what the field button does). This doesn't work with a popup (and my original filename was misleading in that regard, since there is no popup … just a dropdown and the related field from Projects).

              • 4. Re: Auto Complete in Related Field Pop-Up Menu
                BMyers

                Thank you very much for taking the time to create the file.  I'll study it to see how it works.