caml query won't return rows with null value in contains query

Question

I have written a CAML query using <Or> with <Contains>. the query looks similar to below:

<Where>
    <Or>
      <Or>
        <Contains><FieldRef Name="Field1" /><Value Type="Text">`
          +SearchTerms +`</Value>
        </Contains>
        <Contains><FieldRef Name="Field2" /><Value Type="Text">`
          +SearchTerms +`</Value>
        </Contains>
      </Or>
      <Or>
      <Contains><FieldRef Name="field3" /><Value Type="Lookup">`
      +SearchTerms +`</Value>
    </Contains>
        <Contains><FieldRef Name="field4" /><Value Type="Lookup">`
          +SearchTerms +`</Value>
        </Contains>
      </Or>
    </Or>
</Where>

Basically what I want to do is return all rows that contain search terms in one of those four fields(field1,field2,field3,field4). for example if I SearchTerms is 'abc' then all rows where any of the fields contain abc should be returned.
This works most of the time. It fails when there is an empty in field3 or field4.

Basically, if field1 and/or two meet the criteria, but field3 or field4 is empty it won't return that row. It still returns rows if there are multiple rows. I've tried rearranging things and it doesn't seem to help.

I'm currently developing this in spfx. When I check the json I am finding that the rows are not returned there. I belive it is definately my Caml query.
Any help would be greatly appreciated.

Answer 1

That could be because the field type is Lookup so it needs to find a matching value so it can return the information from the lookup list. You can try to use a default value for all those unmatching rows if it is really necessary to leave it blank.

Also make sure that you are getting the value correctly

 lookupValue = listItem["lookupColumn"] as FieldLookupValue;
       if (lookupValue != null)
          {
             foo.value = lookupValue.LookupValue;
          }

Answer 2

You might need to user <AND> at top of the query after <WHERE> like this.

<Query>
<Where>
    <AND>
      <Or>
        <Contains><FieldRef Name="Field1" /><Value Type="Text"> +SearchTerms +</Value>
        </Contains>
        <Contains><FieldRef Name="Field2" /><Value Type="Text"> +SearchTerms +</Value>
        </Contains>
      </Or>
      <Or>
      <Contains><FieldRef Name="field3" /><Value Type="Lookup"> +SearchTerms +</Value>
    </Contains>
        <Contains><FieldRef Name="field4" /><Value Type="Lookup"> +SearchTerms +</Value>
        </Contains>
      </Or>
    </AND>
  </Where>
</Query>

Answer 3

Try adding LookupId="TRUE" to the FieldRefs for the lookup fields, like:

<Where>
    <Or>
        <Or>
            <Contains><FieldRef Name="Field1" /><Value Type="Text">`
                + SearchTerms +`</Value>
            </Contains>
            <Contains><FieldRef Name="Field2" /><Value Type="Text">`
                + SearchTerms +`</Value>
            </Contains>
        </Or>
        <Or>
            <Eq><FieldRef Name="field3" LookupId=”TRUE” /><Value Type="Lookup">`
                + Id + </Value>
            </Eq>
            <Eq><FieldRef Name="field4"  LookupId=”TRUE” /><Value Type="Lookup">`
                + Id + </Value>
            </Eq>
        </Or>
    </Or>
</Where>

This would also require you're using the lookup IDs for the search terms of the lookup fields, and probably using Eq instead of Contains for those fields, which means you'd need an OR condition for each ID against each lookup. You might still be able to concat the IDs and use contains, but you'd definitely need to use Ids instead of searchTerms.

If that doesn't work, you could also add 2 additional OR tests, one for each lookup testing for IsNull, but I suspect the LookupId="TRUE" is the fix. Lookup fields used as filters have some strange behaviors when you don't specify LookupId="TRUE".