# Find two values

**URL:** <https://forum.servoy.com/t/find-two-values/18657>\
**Category:** Classic Servoy\
**Created:** [December 9, 2015, 3:28pm UTC](https://forum.servoy.com/t/find-two-values/18657 "2015-12-09T15:28:18Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![dfernandez](https://avatars.discourse-cdn.com/v4/letter/d/da6949/32.png) [@dfernandez](https://forum.servoy.com/u/dfernandez)\
**Post date:** [December 9, 2015, 3:28pm UTC](https://forum.servoy.com/t/find-two-values/18657/1 "2015-12-09T15:28:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Peter\_de\_Groot](https://avatars.discourse-cdn.com/v4/letter/p/b4bc9f/32.png) [@Peter\_de\_Groot](https://forum.servoy.com/u/Peter_de_Groot)\
**Post date:** [December 11, 2015, 2:38pm UTC](https://forum.servoy.com/t/find-two-values/18657/2 "2015-12-11T14:38:38Z")

</div>

> 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?

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

```

---

<div class="post-metadata">

**Author:** ![Bernd.N](https://avatars.discourse-cdn.com/v4/letter/b/7feea3/32.png) [@Bernd.N](https://forum.servoy.com/u/Bernd.N)\
**Post date:** [December 12, 2015, 12:37am UTC](https://forum.servoy.com/t/find-two-values/18657/3 "2015-12-12T00:37:44Z")

</div>

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](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:

```auto
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);

```

---

<div class="post-metadata">

**Author:** ![patrick](https://avatars.discourse-cdn.com/v4/letter/p/b2d939/32.png) [@patrick](https://forum.servoy.com/u/patrick)\
**Post date:** [December 12, 2015, 11:09am UTC](https://forum.servoy.com/t/find-two-values/18657/4 "2015-12-12T11:09:15Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![Bernd.N](https://avatars.discourse-cdn.com/v4/letter/b/7feea3/32.png) [@Bernd.N](https://forum.servoy.com/u/Bernd.N)\
**Post date:** [December 12, 2015, 1:28pm UTC](https://forum.servoy.com/t/find-two-values/18657/5 "2015-12-12T13:28:02Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [December 12, 2015, 5:58pm UTC](https://forum.servoy.com/t/find-two-values/18657/6 "2015-12-12T17:58:07Z")

</div>

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:

```auto
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:

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

```

Hope this helps.

---

<div class="post-metadata">

**Author:** ![dfernandez](https://avatars.discourse-cdn.com/v4/letter/d/da6949/32.png) [@dfernandez](https://forum.servoy.com/u/dfernandez)\
**Post date:** [December 22, 2015, 8:43pm UTC](https://forum.servoy.com/t/find-two-values/18657/7 "2015-12-22T20:43:12Z")

</div>

Great ideas and solutions.

Thank you so much!