CNFS IQ - Find All Results That Do Not Match a Condition

A common use of IQ is to look for all results that do not meet a certain condition. Many users write these queries using the "Not Equal To" condition, but that will not return correct results. Instead, you need to use a sub-query.

"Is Not Equal" Does Not Match Blank Fields

As an example, let's say you are looking to send a letter to all the families who are not in a family workgroup called Servers. You write a query like the one below:

This query will return a list of families, but you will find that the list is incomplete--some families who are not in the Servers workgroup are not in the list:

The results are incomplete because the “is not equal to” condition does not match empty fields. The query is looking for families that have a family workgroup which is not equal to Servers. The list you have includes all families who are families who are part of a Family Workgroup that is not Servers. It does not include families who have no workgroups at all.

To include blank results, use the sub-query option instead.

Use a Sub Query to Find all Results That Do Not Match a Condition

If you need an introduction to sub queries, see this knowledge base article:

https://support.parishsoft.com/hc/en-us/articles/217876028-ConnectNow-IQ-Training-How-to-create-sub-queries-in-IQ

We will be using the "Not in Sub Query" condition. In this case, we are looking for all families who are not found in workgroup servers. The condition will be the field that is guaranteed to be unique for each family, (Family DUID).

You should use the DUID for the category you are searching: Member DUID for members, Contribution DUID for contributions, and so on. 

Then, select the "not in sub query" condition:

To define the sub-query, click on the "edit sub-query" link.

In this Edit sub-query window, define a condition that you want to exclude. Because the condition is "not in sub query" the sub query needs to match the people you do NOT want in your list. In our example, that is the members of the Servers family workgroup. So we write the following condition:

This query will give you what you are looking for: a list of every single family who is not part of the Servers family workgroup.

Even though the query condition is only finding Family DUIDs, you will be able to include all family information in the result columns. IQ will return results for the records that match the Family DUIDs returned in the result.

Summary

To find all results that do not match a condition

1) Create a sub query that will match the request you want to exclude.

2) In the main query, search for DUIDs with the condition "not in sub query"

3) Enter whatever columns you need for result columns.

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.