KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: Query for Records "Not IN" in Collection
PRODUCT: 4D | VERSION: 17 | PLATFORM: Mac & Win
Published On: December 18, 2019

The IN operator is a useful way to query for records with parameters that match the values in a collection. However, searching for records with parameters that are not in a collection set is just as useful too. The code below gives an example of how this can be done. The example below queries for customers that do not belong to the states in the collection (California, New York, and Washington).

It does this by looping through the collection, analyzing each element, and querying for records that do not match.

C_COLLECTION($state_col)
$state_col:=New collection("California";"New York";"Washington")
$ent:=ds.customer_table.all()

For ($i;0;$state_col.length-1)
  $ent:=$ent.query("states # :1";$state_col[$i])
End for