6 Replies Latest reply on Apr 18, 2012 9:47 AM by philmodjunk

    Filtering a drop down list



      Filtering a drop down list


      Hi.  I am trying to filter a drop down list for an order confirmation database.  I have seen lots of posts relating to this but they seem to direct the users to format to a conditional value list which I am not sure is appropriate for this query (??).

      We have a long list of names to choose from ref our order confirmations and it is not practical to have a drop down menu with all the names on to choose when entering.  I would like to format a field so that a name can be typed in and the list reduce accordingly...

      For example, the user would like to automatically bring up the address information for a company called 'Smithland' where we have, for example, many branches of 'Smithland'.  So, when we press 'S' then all the companies starting with 'S' would show.  Then by pressing 'S-M-I-T......' etc would reduce the list to just Smithland options.

      Is this possible?  I am not sure of the terminology so would appreciate a pointer in the right direction as I am sure this must be possible with Filemaker Pro 11?

      I am fairly new to building relationships so would appreciate simple terminology to help me understand the function.  Thanks for any help possible!

        • 1. Re: Filtering a drop down list

          At first glance, there's a simple way to get what you want, but it has significant limitations that can be avoided with a script supported approach.

          There's an auto-complete option you can specify for the value list and then the list will automatically filter down as you type in a portion of the name. This works great, but now you are entering a name into the value list formatted field. Where this can be a problem is that such lists typically work from an ID number in field 1 and the name in field 2. Such value lists do not work with auto-complete. This appears to leave you with two less than perfect options: Use the value list to enter only a name--which can create problems when names are not unique, when names get changed and when data entry errors with the name need to be corrected, or do without the auto-complete feature--leaving you with a value list that is of unmanageable size.

          It is possible, with scripting, to set up an auto-complete enabled value list of names. A script then looks up and enters the ID number associated with the name selected. The script can detect cases where a duplicate name exists and display a list from which to choose the specific individual desired.

          Here's a demo file that illustrates that method along with several other ways to deal with long value lists in a more user friendly fashion: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

          • 2. Re: Filtering a drop down list

            Hi Phil

            Thank you for your clear and detailed reply to me - and for making the terms user friendly!

            Your options look very exciting.  The first option using Auto-Complete Drop Down Search I wasn't sure how to use (sorry).  I tried typing in the word 'screw' as directed and deleting all the text to the right but nothing happened upon clicking 'tab' or 'return'.  Am I doing something wrong with this?

            The Pattern Matching search was also a great option and very clear to the user.  Would you be able to provide more info on how to set this up?


            ps.  you may have seen my other post asking how to set up line items that increase as each line is entered.  I would love to know how to set this up!  This is as per your invoice list which includes Product ID/Description etc.  Is this a portal?

            • 3. Re: Filtering a drop down list

              I tested my personal copy of this file and then downloaded and tested the cop from the share site. Both worked as expected--deleting all but Screw from the field caused the script to pop up a list of two items that contain that word in the description field as a way to illustrate how a script can handle duplicate values in a more user friendly fashion than the options you'll encounter when you use the simpler ID number in field 1, name in field 2 type of value list.

              By any chance did you download this file previously? One of the reasons I downloaded and checked the file from the share site is that my notes indicated that a bug with the upper drop down value list field's script had been fixed a while back and I wanted to be sure that fix made it into the uploaded copy and it has.

              The info on how each of these is set up is available in the demo file. Go into layout mode and note the script trigger badges on the search fields. Right click them to find what triggers were set and what scripts are performed. You can then open the scripts and see how they work. Once you've done that, try setting up your own test file using the same method and feel free to post questions about if you encounter parts that you don't understand and/or can't get to work.

              you may have seen my other post asking how to set up line items that increase as each line is entered

              I've posted over 30,000 comments to this forum and I'm afraid I don't remember that one. You are welcome to post a link to it in your next post here so that I can find it and take a look at it.

              • 4. Re: Filtering a drop down list

                Hi Phil

                I have redownloaded the file and it works this time.  Thank you for that - I will look at the script triggers and take it from there.

                My link re the line items does not seem to have submitted.  Will create a fresh post re that.

                Thank you for your help - You are a Filemaker guru!!

                • 5. Re: Filtering a drop down list

                  Hi Phil

                  I have looked at the scripting and am not making it work at the moment - but this is down to my inexperience rather than any problem with your instructions.

                  I have looked closely at how you set up the first auto-complete option and have come unstuck.  When you enter a letter of the alphabet, automatically the first entry beginning with that letter is selected and inserted.  I am not sure what I have done wrong with this.

                  I have attached screengrabs of my scripting and relationship (I know the scripting is a bit awry but I am still working on this trying to adapt your script for the purpose of my database - but this depth of scripting is new to me so I am working on trial and error basis).

                  If you are able to pinpoint what i have done wrong with the auto-complete, that would be fantastic!

                  Thanks again for your help.

                  • 6. Re: Filtering a drop down list

                    What script trigger did you specify for the drop down field named "Find Supplier"? It sounds like you are using OnObjectModify instead of OnObjectSave. (With drop down lists, OnObjectModify will be tripped each time a keystroke modifies the content of the field...)

                    I also don't see This important script step:

                    Set variable [$SearchText ; value: Suppliers::media owner company]

                    (that doesn't really seem like the right field here. The name doesn't indicate that you are using a separate field with global storage here...)

                    Your Show Custom Dialog step has too many quotes and is quoting a global variable's name instead of its content.

                    Try using this expression inside the Show Custom Dialog Step:

                    "No Supplier found containing " & Quote ( $Searchtext ) & "was found."

                    Remove the back slashes and extra quotes. Put "Not Found" in the Title box instead of the Message box--also without the back slashes and extra quotes.