3

I wrote some CAML Queries to get data from a List that has lookups on another List. All is working fine as long as I do not reference a Lookup Field which points to a Calculated Field.

I tried different types in the value field, but it just doesn't work. When I replace the FieldRef with another field from the list, it works as excpected. Why can't I use a lookup to a calculated Field in a Query?

Definition of the to-be-queried Lookup Field in List A

<Field Name="Mitarbeiter" ShowField="Search" ID="{...}" DisplayName="Mitarbeiter" Type="Lookup" List="Lists/Mitarbeiter" />

The referenced calculated Field in List B (basically displays "Lastname, Firstname" in this example)

<Field Name="Search" ID="{...}" Hidden="FALSE" DisplayName="Search" Type="Calculated" ResultType="Text" ReadOnly="TRUE">
   <FieldRefs>
     <FieldRef Name="Title" />
     <FieldRef Name="Vorname" />
   </FieldRefs>
   <Formula>=Title &amp; ", " &amp; Vorname</Formula>
</Field>

and the CAML Query (Field Mitarbeiter Points to a calculated Field in another List)

 <Where>
  <Contains>
     <FieldRef Name="Mitarbeiter" />
     <Value Type="Text">Name of person</Value>
  </Contains>
 </Where>

The Request crashes and I find several entries in the SP Logs

System.Data.SqlClient.SqlException: Argument data type sql_variant is invalid for argument 1 of like function.

Unknown SQL Exception 8116 occurred.  Argument data type sql_variant is invalid for argument 1 of like function.function.'
4
  • I am facing exactly the same issue. Did you find a fix for this?
    – user19057
    Aug 21, 2013 at 13:34
  • No nothing so far. I had to solve my problem differently. But I still hope someone has an answer for me.
    – Kirschi
    Aug 22, 2013 at 8:19
  • Have you seen this? Looks like similar problem: sharepoint.stackexchange.com/questions/41435/…
    – luccio
    Oct 14, 2013 at 9:54
  • Thanks, but no its not the same problem but it has something to do with it. And I already tried different (all possible) value types for the return value. The normal Calculated Column does work, the problem is if I have a Lookup on this Calculated Field and want to query that Lookup Field.
    – Kirschi
    Oct 14, 2013 at 10:17

1 Answer 1

1

try this:

 <Where>
  <Contains>
     <FieldRef Name="Mitarbeiter" />
     <Value Type="lookup">ID of person</Value>
  </Contains>
 </Where>

or

     <Where>
      <Contains>
         <FieldRef Name="Mitarbeiter" />
         <Value Type="lookupId">ID of person</Value>
      </Contains>
     </Where>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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