Search multiple values in one foundset field

Classic Servoy
1

I have to find some strings within one field of an foundset (and operator).
The “p_attributes” field has values like “e1 g4 b1”.

Tried the following without result:

var vAttributesText1 = "e1";
var vAttributesText2 = "b1";

foundset.find() 
        if(vAttributesText1) {
		foundset.p_attributes =  "#%" + vAttributesText1 + "%";
	}

	if(vAttributesText2) {
		foundset.p_attributes = "#%" + vAttributesText2 + "%";
	}
foundset.search();

Looks like servoy uses only the second query:
select top 201 p_nr from testTable where upper(p_attributes) like upper(?)

How to handle this?

2

Hi,

you can create OR conditions by using foundset.newRecords() between your condition blocks.
Please note also that you should alwasy execute the foundset.find() within an if condition, since the foundset.find() may return false… on your next line you may change the assigned value of your selected record if foundset.find() return false.

var vAttributesText1 = "e1";
var vAttributesText1 = "b1";

if (foundset.find()) {
        if(vAttributesText1) {
      foundset.p_attributes =  "#%" + vAttributesText1 + "%";
   }
   foundset.newRecord();
   if(vAttributesText2) {
      foundset.p_attributes = "#%" + vAttributesText2 + "%";
   }
foundset.search();
}

You can also concatenate the multiple values using the ||

if (foundset.find()) {
   if (vAttributesText1 && vAttributesText2) {
      foundset.p_attributes =  "#%" + vAttributesText1 + "%||#%" +  vAttributesText2 + "%";
   }
   // ... TODO handle if only 1 text value is set
   foundset.search();
}

You can also use the Queri Builder to execute queries on your foundset and search for specific values. To know more about the query builder you can look at this wiki page https://wiki.servoy.com/display/SERV61/Query+builder

Regards,
Paolo

3

Hey,
thanks for your reply but I need an “and” operator and not an “or”.
Yes, I know about the query builder but in this case I have a really large find operation and it would be nice to prevent writing everything new with the query builder.

4

Hi Michael,

The default behavior of find mode is AND. The caveat is that you cannot do AND operations on the same dataprovider multiple times.
In your example, you are just re-assigning the next condition, overwriting the first.

Possibly you can us an IN condition here to get the same effect?
foundset.dataprovider = [value1,valueN]

However, that will not use the LIKE operator.

Another approach with the LIKE operator would be to use a pattern wildcard ‘__’
foundset.dataprovider = ‘#%value1__value2%’ // i.e. #%foo__bar% matches ‘FOOBAR’, ‘Food at the bar’, but not ‘foo’ or ‘bar’ by themselves

This ensures that you have a match on both values in a single string (caveat is that they must be in that order, but here, you could use OR in conjunction)

5

Hi,
yes, I had already thought that. Seems there is no good solution for my requirement because the strings can be in different order. I will rewrite the filter with queryBuilder to get this running.

6

It is possible to do this with find-search, by using the clearLastResults and reduceSearch parameters of the search function. With clearLastResults=false and reduceSearch=true, you are searching in your previous search results, creating an “and”-search.

For example:

	if (foundset.find()) {
		foundset.p_attributes = "#%" + vAttributesText1 + "%";
		foundset.search();
		
		foundset.find();
		foundset.p_attributes = "#%" + vAttributesText2 + "%";
		foundset.search(false,true); //clearLastResults=false; reduceSearch=true
	}

Note that for each search()-call it does a query, so it is not really efficient. But at least it is good to know that it is possible this way.