Is Null predicate not working with TargetAudience
Assume you have enabled Target Audience on your list. While adding list items there would be scenarios where you would not add any value to that TargetAudience column.
While trying to Query this List Item through Object Model using (FullText Sql Query) IsNull predicate, the query will not return expected results.
For Ex; UserA is added to TargetAudience1 and TargetAudience1 is enabled on specific set of lists, they would like to return results accordingly.
FullText Search Query which they would want to execute is
SELECT TargetAudience,Title, Path FROM portal..scope()
WHERE CONTAINS ('"staff*"') AND ( ("SCOPE" = 'All Sites') )
AND (
CONTAINS("TargetAudience",'"ac533252-9ac6-4f75-93df-ce87ffb27782"')
OR CONTAINS("TargetAudience",'"6df05c7b-2567-41a0-80c8-ff0511ceeaaa"')
OR TARGETAUDIENCE IS NULL
)
ORDER BY "Rank" DESC
This Query is not returning expected results. The query is not returning results where TARGETAUDIENCE column is null.
The reason for this behavior being
Properties which don’t have a value are not stored in DB of Sharepoint to minimize the storage.
WORKAROUND
=============
One of the workarounds is to use a constant string value to represent NULL in the Target Audience column. This can be done by creating a dummy SharePoint group which must be used when there is no other entry in target audience column. In order to ensure that the target audience column always has an entry, you must make sure that target audience column always requires a value.
For e.g. Site can use NONE as the name of the entry that represents no other
entry in the target audience column and then at query time use
CONTAINS("TargetAudience",'"NONE"') as the restriction.
