Search multiple values in one foundset field

Questions, tips and tricks and techniques for scripting in Servoy

Search multiple values in one foundset field

Postby briese-it » Wed Oct 24, 2018 4:21 pm

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:
Code: Select all
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?
Michael Harms
Briese Schiffahrts GmbH & Co.KG, Germany
- Servoy 2020.3.3.3565_LTS Running on Windows 2019 DataCenter - MSSQL2017 & PostGreSQL
User avatar
briese-it
 
Posts: 171
Joined: Mon Jun 20, 2011 1:50 pm
Location: Leer, Germany

Re: Search multiple values in one foundset field

Postby paronne » Wed Oct 24, 2018 4:43 pm

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.

Code: Select all
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 ||

Code: Select all
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
paronne
 
Posts: 202
Joined: Fri Nov 02, 2012 3:21 pm

Re: Search multiple values in one foundset field

Postby briese-it » Wed Oct 24, 2018 4:52 pm

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.
Michael Harms
Briese Schiffahrts GmbH & Co.KG, Germany
- Servoy 2020.3.3.3565_LTS Running on Windows 2019 DataCenter - MSSQL2017 & PostGreSQL
User avatar
briese-it
 
Posts: 171
Joined: Mon Jun 20, 2011 1:50 pm
Location: Leer, Germany

Re: Search multiple values in one foundset field

Postby sean » Wed Oct 24, 2018 5:12 pm

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)
Software Engineer
Servoy USA
sean
 
Posts: 370
Joined: Mon May 21, 2007 6:26 pm
Location: USA

Re: Search multiple values in one foundset field

Postby briese-it » Thu Oct 25, 2018 8:07 am

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.
Michael Harms
Briese Schiffahrts GmbH & Co.KG, Germany
- Servoy 2020.3.3.3565_LTS Running on Windows 2019 DataCenter - MSSQL2017 & PostGreSQL
User avatar
briese-it
 
Posts: 171
Joined: Mon Jun 20, 2011 1:50 pm
Location: Leer, Germany

Re: Search multiple values in one foundset field

Postby Joas » Thu Dec 13, 2018 8:59 pm

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:
Code: Select all
   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.
Joas de Haan
Yield Software Development
Need help on your project? yieldsd.com
User avatar
Joas
Site Admin
 
Posts: 842
Joined: Mon Mar 20, 2006 4:07 pm
Location: Leusden, NL


Return to Methods

Who is online

Users browsing this forum: No registered users and 4 guests