Servoy Forum

when you work with foundsets you often deal with find().
According to the documentation one can use an array of values to use the 'IN' statement.
Like:
foundset.user_id = [222,534,464]
works fine.

The opposite, i.e. 'NOT IN' is not possible, or I can't find the right syntax for it?

foundset.user_id = ![545,687,876] -> does not work

So I use a query -> loadrecords(query) instead. Or is there another way?

Hi Jos,

I was in the same confusion few days back, on which i had two options to achieve. I am sharing those below. I used the second one because first one might take a bit more time as it had to do a search and then invert the foundset.

You can do this to achieve the result

Code: Select all

if(foundset.find()) {

     foundset.user_id = [545,687,876];
     foundset.search();
     foundset.invertRecords();
}


Or you can use the query object as

Code: Select all

      // Create the query
      /**@type {QBSelect<db:/server/users>}*/
      var QBSelectObj = databaseManager.createSelect('db:/server/users');
      
      // Build the filtration statement
      QBSelectObj.where
         .add(QBSelectObj
            .not(QBSelectObj.columns.user_id.isin([545,687,876])));
      
      // Load the foundset
      foundset.loadRecords(QBSelectObj);


I am also not sure if these is the best way or not. If some better ways are available please do share.

Hi Sovan,

I did not think about the first option. but it might work, but when there are more then one search criteria it gets complicated (-:

I use indeed the second one like you (with query's)