0

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.

  • I modified the question. How do I go about getting the hold status removed? – LoganReed Dec 31 '18 at 17:41
0

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;
          }
  • Thanks, Bailbrera, I'm new to querying using Caml. I'm not sure why the first thing you mention would be the case. The contains are within an OR. For item 2 I have viewed the JSON and unfortunately the rows just aren't returned. – LoganReed Dec 31 '18 at 17:41
0

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>
  • Thanks, unfortunately that just doesn't logically make sense. It really need to return the row if any of fields have the value. An and seems to restrict. – LoganReed Jan 3 at 17:26
0

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".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.