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:
For a working example of an auto-complete value list of names that is used to look up IDs, see:
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
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.
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
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.