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 8.3.1 Running on Windows 2008R2 DataCenter - MSSQL2008
User avatar
briese-it
 
Posts: 128
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: 112
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 8.3.1 Running on Windows 2008R2 DataCenter - MSSQL2008
User avatar
briese-it
 
Posts: 128
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: 114
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 8.3.1 Running on Windows 2008R2 DataCenter - MSSQL2008
User avatar
briese-it
 
Posts: 128
Joined: Mon Jun 20, 2011 1:50 pm
Location: Leer, Germany


Return to Methods

Who is online

Users browsing this forum: No registered users and 4 guests

cron