Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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.'
share|improve this question
    
I am facing exactly the same issue. Did you find a fix for this? –  user19057 Aug 21 '13 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 '13 at 8:19
    
Have you seen this? Looks like similar problem: sharepoint.stackexchange.com/questions/41435/… –  luccio Oct 14 '13 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 '13 at 10:17
add comment

1 Answer 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>
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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