6 Replies Latest reply on Aug 27, 2014 9:55 AM by philmodjunk

    Search/Lookup Layout



      Search/Lookup Layout




           Is it possible in filemaker, to have a search lookup set up? 


           What I mean with search lookup: I have 3 clients , Smith; Savage;Salomon

           In my search layout I want to have a field, where if I enter "S" it shows me the 3 names and if I continue "Sa" it shows me only Savage and Salomon and if i continue "Sav" it shows me only one. 

           I've been looking around, but i don't know how this is called and with "filemaker search" i only get things about the normal search option



        • 1. Re: Search/Lookup Layout

               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.



          • 2. Re: Search/Lookup Layout

                 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.

            Caulkins Consulting, Home of Adventures In FileMaking

            • 3. Re: Search/Lookup Layout

                   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

              • 4. Re: Search/Lookup Layout

                          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.

                • 5. Re: Search/Lookup Layout


                       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.

                  • 6. Re: Search/Lookup Layout

                         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.