You can use ExecuteSQl's LIKE keyword to search by pattern and display the search list in a portal. FM13's invisibility option can easily do the trick to display the portal as a dropdown only when you type something in the search box. Trigger the script onObjectKeyStroke. Let me know you need more help with this.
See this older demo file: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7
If you are using FileMaker 12 or newer, you can use Open from FileMaker's File menu to open this file and produce a newer copy of this file.
For those interested, I am planning to include the examples from this demo file in an updated format in Adventures In FileMaking #2 -- Enhanced Value Selection, this will be the second in a series of "freeware" demo files that both show working examples of FileMaker features combined with extensive documentation and explanations of how to implement the illustrated technique in your own solutions.
thank you 2 for your answers.
ExecuteSQI's is unknown to me, I'm not too deep into Filemaker. Maybe you can point me to a tutorial?
PhilModJunk, thank you for your file. Like I said, I'm not too deep into filemaker so unfortunately I don't see how your search works. It does the job exactly as i would need it to do, but I don't see through your programming
It does the job exactly as i would need it to do, but I don't see through your programming
This is exactly why I have a new file under development to take its place. The new version will include detailed explanations of how the method was set up and how it works.
In the mean time, you are welcome to ask follow up questions about it and I will be happy to explain them. This will actually be a good "warm up" for me as I am just about to the section where I'll be setting up examples of those search portals in the new "Adventure" file.
Here are two files, showing the "old school" way of doing this. The basic method requires a global field, a calculation field (looking at the global field), and a Relationship. Calculations show what records match what you've typed.
This is for doing this for just one data field, with just one word (like "first name"):
This one can handle two data fields; like "first name" and/or "last name". It requires a bit more, especially because it lets you leave either blank and still work. Because it required more calculation fields, and is entirely based on global field types, I moved the "search" to another table; in this case a "global" table, with 1 (and only 1) record. It can still take you to the resulting record.
P.S. I can guess that SQL could do this with less fields. Though I imagine it would be doing much the same thing, as getting text that matches ≥ and < seems common.
The demo file I recommended also does not use SQL. But it does use a portal filter with partial pattern matching to determine which records appear in the portal. There's some special scripting involved, however to get the portals to respond to each keystroke.