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?
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)
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>" +
"</Join>";
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);
SupportPackTemplateLookup field? Thanks
SupportPack.SupportPackTemplateLookup. Is this implicitly defined, otherwise how does the CAML query know what field to Join on?
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.
