How to Use Not One Of and Not A Member Of Filter

Overview

When most users use a Not One Of or Not a Member Of filter, they really are trying to find both of the following scenarios:

  1. Records that have no value for that filter field
  2. And records where the filter field does have a value but it is not one of the values specified in the filter options.

For this reason, the following behaviors are exhibited in the report builder for these two filter types.

Without an Advanced Filter Expression

If you are not using an advanced filter expression, using a Not One Of or a Not A Member Of filter type will equate to a filter that is effectively:
[field] has no value OR [field] is not one of ([filterValue1], [filterValue2], etc.)

With an Advanced Filter Expression

If you are using an advanced filter expression, using a Not One Of or a Not A Member Of filter type will equate to a filter that is effectively:
[field] is not one of ([filterValue1], [filterValue2], etc.)

Note that it does not automatically put in the has no value check in this mode. The thought here was that with advanced expressions, you should have 100% control of the filtering and would need to put the has no value filters in yourself. That would be done by adding an additional filter option like [field] Has No Value and then updating your advanced filter expression to use this additional option with an OR operator.

If the report builder did not work this way in advanced filter expression mode, you would not actually be able to use Not One Of or Not A Member Of filtering to find only items where a value was present and also was not not the filter value(s) specified.

100% helpful - 1 review

Details

Article ID: 67920
Created
Thu 11/29/18 12:27 PM
Modified
Tue 6/23/20 9:59 AM