Dynamic Typeahead field search?

Classic Servoy
1

I have a dynamic typeahead valuelist that i set up through an SQL Query and the application.setValueListItems() method. works beautifully now, however, I was wondering if there is a way that have the Typeahead search for anything. For example, my valuelist displays the persons last name, first name and university like this..

Smith, John (UNC - Chapel Hill)

what i was wondering is if theres a way to search for any instances of that string being typed in the textfield… so if i typed “bob” i could get back

Bobbins, Curtis (Univ. of Maryland)
Weston, Bobby (Texas A&M)
Jones, Thomas (Bob Jones University)

from my experiences the normal valuelist seems to work that way, doing text searches against the fields being displayed but not too sure how the dynamic valuelist would interpret that.

thanks for any feedback in advance.

2

Just put a method on the onDataChange event on the field.

As long as you’re not using separate return values and display values - it’s very straight forward:

Take the input from the field - and re-issue your sql query used to populate the valuelist with a WHERE clause: WHERE lower(myField) like ‘%bob%’

3

So I should run an SQL Query and bring back to the data for everytime the text field is changed? I was thinking that would be the way to go but i was wondering if theres a way to do it without pounding the database with queries?

4

If you want an “automatic” search - that’s the way to do it.

TIP: If you’re not already - use prepared statements rather than “hard-coded” queries.

For example:

SELECT * FROM customers WHERE id = ?

Then pass the ID in as an array in the “args” section of the databaseManager call. By using prepared statements (like Servoy does internally), the execution time is cut WAY back and the server will “cache” the query execution plan - so there is very little overhead.

5

thanks for your help so far bob, the issue im running into now is that the onDataChange only executes the required method after the user has changed the valuelist value to something else… not just as they’re typing. I’m trying to make it so as the users typing, im updating my query to grab everything where last_name like ‘%?%’ or first_name like ‘%?%’. onAction and onDataChange dont execute in between each keystroke which is what i need for this valuelist.

any thoughts?

6

AH! Sorry, I misuderstood!

There’s no way to capture the keystroke, other than set the display type as “typeahead”. BUT typeahead will only do a “starts with” match - not an “anywhere” match.

7

Gotcha, ok that makes sense with the kind of results I was getting. Thanks, I thought I was going crazy for a minute there… I think I can live with the “starts with… typeahead” match.

thanks again bob.