2

In CAML, if I have a managed metadata column with multiple values, you can determine if one of a list of values is in the column using the following query (where the order of the Values/FieldRef elements does not matter):

<Where>
    <In>
          <Values>
                  <Value Type="Integer">2</Value>
                  <Value Type="Integer">7</Value>
          </Values>
          <FieldRef LookupId="True" Name="Managed" />          
    </In>
</Where>

This will return true for a document which has terms 2 and 7 contained within it, because one of the values (2) is in the managed metadata column.

Is there a way to flip that around and say that all of the field values from the Managed Metadata column must be in the given list of values? i.e. if the Managed Metadata column has terms 2 and 7, something similar to the above would return false, because the list of values does not contain 2 AND 7. If the values were 2, 7, 10, that would return true, because the list of values does contain all of the terms in the column.

1

I have come up with one way to do what I want. If the managed terms have ids 1 through 5, and my list of values is 2, 3, then I can construct a query like:

<Where>
    <And>
        <Neq><FieldRef LookupId="True" Name="Managed"/><Value Type="Integer">1</Value></Neq>
        <And>
           <Neq><FieldRef LookupId="True" Name="Managed"/><Value Type="Integer">4</Value></Neq>
           <Neq><FieldRef LookupId="True" Name="Managed"/><Value Type="Integer">5</Value></Neq>
        </And>
    </And>
</Where>

This will get cumbersome when there's 50 terms and I want to query on 2 of them, so I'm still interested if there's a better way, although it does look like you can get into several hundred conditions before CAML starts having issues.

  • it is unfortunate you can't build calculated columns on lookup fields or managed metadata fields, because you could easily create a calculated column that includes "1,4,5,7,22,69" and returns true, and otherwise returns false. Then you could simply filter where calculated field is true. – CigarDoug Jan 29 '16 at 13:51
0

Not at a box but I think it is since what you are asking for is multiple subqueries, I would write it in SQL or LINQ and get the CAML that way. At a guess it will look something similar too:

<Where>
    <And>
      <In>
         ... Value 1 lookup
      </In>
      <In>
         ... Value 2 lookup
      </In>
    </And>
 </Where>
  • That's getting at a solution that is backwards of what I want - "Is every value in this list of values in the metadata column?", what I want is "Is every value in the metadata column in this list of values?" The following is one way of doing the first: <Where> <And> <Eq> <Value Type="Integer">2</Value> <FieldRef LookupId="True" Name="Managed" /> </Eq> <Eq> <Value Type="Integer">7</Value> <FieldRef LookupId="True" Name="Managed" /> </Eq> </And> </Where> – lgaud Aug 21 '12 at 20:16
0

This may be overkill for what you are trying to do, but if I really needed the ability to filter by specific values, I would add an event handler to generated a value in another field and filter on that field.

As I mentioned in my comment, you can't use calculated columns on lookup fields or managed metadata fields. If you could, you could easily create a calculated column that returns true where Managed includes "1,4,5,7,22,69" or false if it doesn't. Then you could simply filter where calculated field is true.

You could create an event handler for ItemAdding and ItemEditing that stores true or false based on the value in Managed, that would work the same as a calculated column. But adding custom code is a lot of overhead, I wouldn't recommend it if you weren't already using custom code on this particular list or site.

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.