These tips apply whether you choose to use the Filter Builder or manually define a filter.
Contains vs. Is Equal To or =
Because SharePoint sometimes stores data differently than the way it is displayed, you may find that you get better results from your filters when you use the "Contains" operator instead of "Is Equal To" or "=".
For example, you may see the name "Bob Smith" in a list item that includes the Assigned To field. However, SharePoint stores this name with extra characters, so the actual stored value may be something like "3;#Bob Smith." Therefore, if you want to filter on records that were assigned to Bob, you get the desired results if you build the filter with the "Contains" operator.
Using Yes/No Columns vs. Choice Columns with Yes/No Options
It is important to understand the difference between Yes/No columns and Choice columns with Yes and No options.
When you use a Yes/No column, SharePoint stores a "True" value when the field is selected and leaves it blank when it is not selected. As a result, if you want to filter on a Yes/No column, you need to set the filter to look for the appropriate value:
•To filter on a selected (Yes) value, the filter would be
column is
equal to True (using the Filter Builder) OR
column=True (manually defining
the filter)
(replace "column" with the name of your column)
•To filter on a non-selected (No) value, the filter would be
column is
not equal to True (using the Filter Builder) OR
column<>True (manually
defining the filter)
When you use a Choice column with Yes and No options, the
filter should be defined as:
column is equal to value (using the Filter
Builder) OR
column=value (manually defining the filter)
Balancing Your Filters
The Filter Builder works as expected with one, two, or any even number of clauses. However, it needs help with three, five, or any other odd number of clauses. In these cases, the filter must be balanced. Rather than having an odd number of clauses, you will need to add a "dummy" query to create an even number. The dummy query can be an "is not Null" check and exists only to even out the query to be built by the Filter Builder.
When You DON’T Want to Use the Filter Builder
The Filter Builder cannot be used to build the equivalent of this expression:
(A AND B) OR (C AND D)
If you want to create this type of filter, the filter will need to be defined manually.
Also…
You will need to manually define a basic filter if you want the SharePoint Data Provider to include documents in multiple levels of folders. This is because Spreadsheet SharePoint Data Providers do not include any items in folders when a CAML-based filter is utilized (defined either manually or using the Filter Builder).