Multiple Find on Multiple Fields

Classic Servoy
1

I found this article on Servoy Magazine on how to do a Multiple Find:
http://www.servoymagazine.com/home/2004 … multi.html

  • but it’s only on one field

How does one modify this method do a search on another field?

For example, User types in two words in the global search variable to find a record for JOHN SMITH.

So a search on two fields: first_name and last_name

I know I can create a Calculation and create a field in the database to store it, but i want a way to be able to do these types of searchs for more then just a full name.

controller.find()

var search = globals.Search_Employee.split (" ") //variable to hold search

for (var i=0; i<=search.length ; i++) // start counter
{
		first_name = "#%" + search[i] + "%" 

		//unless this is a the last request create a new search record
			if (i!=search.length)
			{
				controller.newRecord();
			}
		

}
controller.search()

elements.Search_Employee.RequestFocus()
2

Hi David,

You will have to create stored calculated fields to do this all inside Servoy.

But the great thing about Servoy is that you always have many choices how to implement things. You can choose to code everything inside Servoy without touching the database directly, or, if you have any knowledge of SQL, and are not too worried about creating dependencies to a particular database back-end, you can code the entire search in SQL.

Our databases typically have less than 100,000 records in a table, so , we don’t have to worry about any speed hits of using techniques like regEx.

We use regEx in SQL to simulate FileMaker’s search behaviour, search for “JO SM” and you’ll find “JOHN SMITH” etc. Depending on your database backend, you can take this further and also create searches that will find similar sounding names.

Also, if you are new to SQL, Sybase is a very good database, but it may not be the most approachable in form of documentation. Many Servoy developers also use mySQL and PostgreSQL since they have extensive online communites and you there are good books/blogs etc to learn from.

In short - servoy gives you choice! You will never be stuck.

3

Thank you Christian for your reply.

Do you have an example of how to implement this in servoy?

For example, what is the RegEx SQL syntax you use for such a search? And how do you link that with your servoy solution.

4

got this to work with lots of help from Robert

var search = globals.Search_Employee.split (" ")
var maxrows = 1000 
var query = 'SELECT employee_mass_spec.emplid FROM employee_mass_spec WHERE ';
var lookup = "";

for (var i=0; i<search.length ; i++) 
{ 
   lookup = "'%" + search[i] + "%'" 
   if (i == 0) 
   { 
      query += 'employee_mass_spec.first_name LIKE ' + lookup; 
   } else { 
      query += 'AND employee_mass_spec.last_name LIKE ' + lookup; 
   }       
} 
query += ' ORDER BY employee_mass_spec.emplid;'; 
controller.loadRecords(query) 
forms.Customers.controller.show()