Find two values

Classic Servoy
1

Hi,

Is it possible to use Find & Search to filter all the data but not those rows that contain two different values in a column?

The sample code for find do not have an option for the and operator, just the or.

What we need is to find and return all records but not those for countries 14 and 32

Regards

2

dfernandez:
Hi,

Is it possible to use Find & Search to filter all the data but not those rows that contain two different values in a column?

The sample code for find do not have an option for the and operator, just the or.

What we need is to find and return all records but not those for countries 14 and 32

Regards

Something like this?

if(foundset.find()){
   foundset.country = '!=14';
   foundset.newRecord();
   foundset.country = '!=32';
   foundset.search();
}
3

Peter, the newRecord() creates a logical OR link. The problem with that is that every record will be true to either !14 OR !32.
https://wiki.servoy.com/display/public/DOCS/Find+Mode#FindMode-MultipleFindRecordsforLogicalOR

What is needed is “All records where country <> 14 AND country <> 32”
I tried on my own table
foundset.bp_country_id = ‘!=DEU||!=BEL’;
but that eliminated only DEU, not BEL.

And when we do a simple
country = “!=14”
country = “!=32”
then the second condition is overwriting the first.

In case nobody comes up with a find()-solution, this seems to be one of the cases where good old SQL should be used.
The code pattern for that:

var
	/***@type {{sql:String, args:Array, server:String, maxRows:Number, table:String}}*/
	oSQL = { },
	/***@type{JSFoundSet} */
	fsData;
	
	oSQL.sql   = "SELECT record_id FROM tablename WHERE country <> ? AND country <> ? ";
	oSQL.args  = [14,32];
	oSQL.table = "tablename";

	fsData = scopes.utils.sqlFoundset(oSQL);

	forms['frm_formname_tbl'].foundset.loadRecords(fsData);
4

Another way to express an OR would be to use an array such as

find()
country = [14, 32]

This will lead to an IN (where country IN (14,32)). The problem is that as far as I see this cannot be negated.

5

Well, negation could be done by that way when all other values are known, just by listing them.
But that depends too much on a complete list of those values and is therefore not really ironclad.

6

It looks like that using the Servoy way you can’t do it with 1 single search. You need to do it with 2 like so:

if(foundset.find()){
   foundset.country = '!14';
   foundset.search(); // search while clearing any previous results

   // Now search within the result set
   if(foundset.find()){
      foundset.country = '!32';
      foundset.search(false, true); // foundset.search(clearLastResults,reduceSearch)
   }
}

But true, it’s easier (and totally cross platform so no worries about portability) to use SQL like so:

foundset.loadRecords("SELECT pkColumn FROM tableName WHERE country <> ? AND country <> ?",[14,32]);

Hope this helps.

7

Great ideas and solutions.

Thank you so much!