Say I have two lists:

Support Pack

ID : 1, SupportPackTemplateLookup:(ID#OtherValue), IsActive : True

Support Pack Template:

ID : 1, OtherValue: x, SuppType: 10 hr support pack
ID : 2, OtherValue: y, SuppType: 20 hr support pack

I need to do the following query:

Get **SuppType** for all Support Packs
Where IsActive == true

How do I achieve this without modifying the lookup column?

  • Use Joins and Projected Fields to get the IsActive column – Arsalan Adam Khatri Sep 14 '15 at 9:58

When working with Lookup-Column use LookupId="TRUE" in your caml query.

Have a look at this blog post: https://abstractspaces.wordpress.com/2008/05/05/caml-query-lookup-field-by-id-not-by-value/

These projects are really helpful, when working with caml: https://spcamlqueryhelper.codeplex.com/ http://www.u2u.be/Software (choose one)

| improve this answer | |
  • Hi, sorry but AFAIK using LookupId will simply return #OtherValue. If I'm wrong please supply an example. – David Sep 14 '15 at 8:45
  • Also, U2U does not help with Joins. I have it open right now – David Sep 14 '15 at 8:45
  • I see, so your problem was to get that additional column from the other list. Since Joins are not supported by either product, maybe you have a look at: camljs.codeplex.com - this could help you come up with the right query. – Nils Sep 14 '15 at 9:05

Use Joins and Projected Fields to get the additional columns.. Example below

SPQuery query = new SPQuery();

query.Joins = "<Join Type='INNER' ListAlias='Support Pack'>" +
                "<Eq>" +
                    "<FieldRef Name='SuppType' RefType='Id'/>" +
                    "<FieldRef List='Support Pack' Name='ID'/>" +
                "</Eq>" +

query.ProjectedFields =
    "<Field Name='SuppIsActive' Type='Boolean' " +
            "List='Support Pack' ShowField='IsActive'/>";

query.ViewFields = "<FieldRef Name='Title'/><FieldRef Name='SuppIsActive'/><FieldRef Name='SuppType' />";

SPList suppPacks = web.Lists["Support Pack Template"];
SPListItemCollection items = suppPacks.GetItems(query);
| improve this answer | |
  • Where do I specify the SupportPackTemplateLookup field? Thanks – David Sep 14 '15 at 10:27
  • Which one are you referring to ? – Arsalan Adam Khatri Sep 14 '15 at 11:03
  • SupportPack.SupportPackTemplateLookup. Is this implicitly defined, otherwise how does the CAML query know what field to Join on? – David Sep 14 '15 at 11:12
  • I think I have misunderstood your entire setup, can you tell me which one is the Parent List? And what fields do you want? – Arsalan Adam Khatri Sep 14 '15 at 11:48

You can use Joins to get your data. Refer to the MSDN article List Joins and Projections. You can also refer this question of SO with some examples to guide you on how Joins work.

| improve this answer | |

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.