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've got a SP2007 list (List A) with two reference-fiels (Column 1; Column 2) that reference items from a different list (List B). For some Opalis automation I need a CAML Query that gives me the results of where Column 1 = Column 2.

Any Ideas?

share|improve this question
    
didn't you succeed by setting the query to <Eq><FieldRef name='column1' /><FieldRef name='column2' /></Eq> ? –  Steve B Jun 27 '11 at 8:00
    
yeah, but that doesn't work :/ Detail: Exception occurred. (Exception from HRESULT: 0x80020009 (DISP_E_EXCEPTION))0x80020009 –  Christoph Voigt Jun 27 '11 at 9:09
    
and what about <Eq><FieldRef name='column1' LookupId="TRUE" /><Value type='Integer'><FieldRef name='column2' LookupId="TRUE" /></Value></Eq> ? –  Steve B Jun 27 '11 at 9:26
    
U2U CAML Query Builder throws an error "One of more field types are not installed properly. Go to the list settings page to delete these fields.0x81020014". Same message when I change the type of column1 to "Lookup". Opalis just ignores the CAML and returns all rows :| –  Christoph Voigt Jun 27 '11 at 9:56
add comment

1 Answer

I'm assuming you are using a Lookup choice field. You'll need a query to run against List A like:

<Where>
   <And>
      <Eq><FieldRef Name="ColumnOne" LookupId="TRUE" /><Value Type="Integer">3</Value></Eq>
      <Eq><FieldRef Name="ColumnTwo" LookupId="TRUE" /><Value Type="Integer">3</Value></Eq>
   </And>
</Where>

Where the integer value of 3 is the ID of the choice in List B.

If you don't know the ID of the choice in list B, you'll have to look it up manually or with another query.

share|improve this answer
    
Thanks for the idea, but it doesn't quite work for this problem. ColumnOne and ColumnTwo from List B are Lookup fields to List A. And I need the items from List B where ColumnOne is the same value as Column B. Can I somehow query the value of ColumnTwo and pass that value as a replacement for the Value 3 in the ColumnOne within one CAML Query? –  Christoph Voigt Jul 4 '11 at 8:55
1  
Yes, you'll need to have two queries. One to find the value of ColumnTwo, then one to find where ColumnOne and ColmumnTwo both equal that value. –  Kit Menke Jul 4 '11 at 14:44
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.