Omit with loadRecords

[irenem]

Hi,

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

Code: Select all

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.

[ROCLASI]

Hi Irene,

What is the actual SQL that it produces ?

[irenem]

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?

[ROCLASI]

Hi Irene,

"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:

Code: Select all

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

Hope this helps.

[irenem]

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.

[rgansevles]

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:

Code: Select all

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

[irenem]

Hi Rob,

That was just what I needed! Thank you.
I overlooked this little '!'.
Sometimes life can be so simple