5 Replies Latest reply on Oct 27, 2016 8:43 AM by philmodjunk

    Auto Complete on second field - AGAIN!


      Firstly, I appologise for the following question, as I realise it has been asked many times before!


      I am not a developer and I have no formal training with databases, I only know what I have learned myself using Lotus Approach, so I would ask you to keep it as simple as possible for me ........Please!


      I am building a database for my Nursery (Plants). It is for keeping track of seed sowing/bought in plants etc. I have a table containing plant varieties and associated data, this table is called "Plant Details". The other table is to create a new sowing or delivery of a plant variety, this is called "Plant Scheduler" "Plant Details" is the parent and "Plant Scheduler" the child.


      I have a "Plant ID" field I use to pull the information from the "Plant Detail" table into the "Plant Scheduler" table. As has been noted many times, auto complete is unable to work on the second field of the value list. This is a huge problem, as I have over 2000 plant varieties in my table, and need to populate over 2500 new records in my "Scheduler" table. A drop down list or Pop Up is simply too slow and renders my creation unworkable in its current form. (Even Lotus Approach had this capability!)


      I did wonder if I could, somehow, use a conditional value list ie Type in 'Begonia' (with the aid of auto complete!) and in the variety field then select the appropriate variety ie 'Nonstop Deep Pink' (also with auto complete) That said, I am really not sure how this would pull the information across, or indeed, if it is possible!


      If Not


      How can I make my ID field auto complete with my Plant Name AND Variety!


      Thank you for your time, and I really appreciate your efforts in keeping it simple!





        • 1. Re: Auto Complete on second field - AGAIN!

          Yes, entering a category in one field can be used as a "filter" to control what values appear in the value list of a different field. This is called a conditional value list.


          And while you can't auto-complete on the second field of a "use values from field" value list, you can invert this approach and set up an auto-complete value list on a text field with a value list that uses that name field as the only field in it's value list, then use a script or relationship to find and copy over the ID.


          A typical conditional value list uses a relationship to filter the values.


          Make a new table occurrence (box in the relationship graph) for your Plant Details table. Define a "Category" field in both tables and link Scheduler to the new occurrence of Plant Details by these category fields (and only those fields).


          Define your use values from field value list as you have before to use on your ID field, but specify that the values come from this new table occurrence rather than the original table occurrence and then select the: "include only related values starting from "Scheduler" option. Now you can select or enter a value in Scheduler::Category to limit what values appear in your value list.


          For more working examples of different types of conditional value lists, see:

          Adventures in FileMaking #1 - Conditional Value Lists


          For a working example of an auto-complete value list of names that is used to look up IDs, see:

          Adventures in FileMaking #2 - Enhanced Value Selection

          • 2. Re: Auto Complete on second field - AGAIN!

            Hi Phil


            Thank you for your reply, your solution seems perfect for my requirements! The download file is excellent, and is a great  reference tool, although for my tiny/inexperienced mind, is taking some digesting!


            I have created the necessary relationships and now have a field in my plant scheduler that replaces your Customer Full Name field, mine is the full Plant and Variety name from my Plant Details table.


            I suppose my question is....... Now What? I am seeing no other details in my field, ie PlantID, Plant Name, Plant Variety and Plant Colour. I am assuming that this is where the scripts come in!


            For further guidance


            Is it as simple as copying your scripts and replacing the appropriate field values with my own?

            Which individual scripts do I need to replicate from your file?

            What is/are the script triggers? or are the scripts themselves the triggers?

            Am I missing anything?


            and how much do you charge / hour! :-)


            I appreciate your time





            • 3. Re: Auto Complete on second field - AGAIN!

              One idea that I had after creating that file was to define the name field (used with the auto-complete value list) as a global text field. SInce it is only used as a temporary place for selecting a value, It doesn't have to store a different value in every record.


              The key to this method is that you have TWO relationships from your layout's table to the table to which you want to link that record. One matches by name and is used only to look up the ID value used in the second relationship. Once that is working, you can add fields from the second table occurrence to show information from the selected record.


              The example that you should be following, BTW, is called Autocomplete value lists II.

              • 4. Re: Auto Complete on second field - AGAIN!

                Thank you Phil


                I have set up the correct relationship now. My fields now work as I need. I have not used any of the scripts as it was a step too far at the moment! I am now a very happy bunny!


                Thank you again for your time



                • 5. Re: Auto Complete on second field - AGAIN!

                  The scripts may or my not be needed. The extend the capabilities of the technique by allowing for two situations:


                  1) a user enters a name not currently present in the table of values and thus needs to add it right on the spot.

                  2) there are more than two records with the same name in your table of values. In this second case, the absence of a script means that you always get the ID of the oldest record and the other record is ignored.


                  If you have unique plant names and enforce unique names only, the second issue is avoided so it depends on the data in your table whether you really need the added scripting.


                  One of the issues that I was addressing in the original example is the fact that when you use the standard "two field" based value list with a name field as field 2 and you either sort or only show that second field, you are likewise prevented from seeing let alone selecting additional instances of the same name in that second field. This makes sense for a standard value list as there's no way to tell the difference between identical instances of the same name, but with this scripted approach, you can have the script pop up a list of the duplicate names with additional fields providing extra info about the listed records so that a user can then make the correct selection.