Omit with loadRecords

Classic Servoy
1

Hi,

i try to omit records from a foundset that match a certain word. This ia a onSearch method.

var name = arguments[2].getSource().getName()
	var form = controller.getName()
	var frm = solutionModel.getForm(form)
	var field = frm.getField(name)
	var dataProvider = field.dataProviderID
	var data = forms[form].controller.getDataProviderValue(dataProvider)
	var searchData = utils.stringReplace('#%' + data + '%',"\n","")
	var jstable = databaseManager.getTable(controller.getDataSource());
	var tableSQLName = jstable.getSQLName();
	var columnNamesArray = jstable.getColumnNames();
	var firstColumnName = columnNamesArray[0];
	var jscolumn = jstable.getColumn(firstColumnName);
	var columnSQLName = jscolumn.getSQLName();
	var isPrimaryKey = jscolumn.isRowIdentifier();

	controller.loadAllRecords()
	var query = "SELECT " + firstColumnName + " FROM " + tableSQLName + " WHERE " + dataProvider + " NOT LIKE '" + searchData +"'"
	foundset.loadRecords(query)

But it doesn’t work. It finds that record instead of omit it. I also tried wit the != operator with the same result.

2

Hi Irene,

What is the actual SQL that it produces ?

3

Hi Robert,

Thanks for helping. This is the query:

“SELECT proj_id FROM projects WHERE proj_customer_ref NOT LIKE ‘#%hello%’”

There is a record in the database with ‘hello folks’ in that field and when the method is fired it finds that record and omit all others.

What am I doing wrong?

4

Hi Irene,

irenem:
“SELECT proj_id FROM projects WHERE proj_customer_ref NOT LIKE ‘#%hello%’”

The # character is a Servoy control character to make the search case-insensitive. It’s however not a SQL control character.
Your query should work with the following syntax:

SELECT proj_id FROM projects WHERE upper(proj_customer_ref) NOT LIKE upper('%hello%')

Hope this helps.

5

Hi Robert,

It still doesn’t work. Now it can’t find anything.

Maybe I should use an omit-loop, but I’m afraid that it will be very time expensive with the real data. There are 15000 records in it.

6

Irene,

In your code you assume that the first column is the pk.
Even if you use only single-pk tables in your solutions, it is not guaranteed that table.getColumnNames() returns the pk first (it returns the columns in the order as reported by the db).

You could also try a simple find.search:

var name = arguments[2].getSource().getName()
var form = controller.getName()
var frm = solutionModel.getForm(form)
var field = frm.getField(name)
var dataProvider = field.dataProviderID
var data = forms[form].controller.getDataProviderValue(dataProvider)
var searchData = utils.stringReplace('#!%' + data + '%',"\n","")
if (foundset.find())
{
      foundset[dataProvider] = searchData
      foundset.search()
}

Rob

7

Hi Rob,

That was just what I needed! Thank you.
I overlooked this little ‘!’.
Sometimes life can be so simple :lol: