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?

  • 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

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

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

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.

| 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

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.