none
CAML Join Against LookupMulti -- Supported?

    Question

  • Dear Microsoft,

    After perusing the SDK documentation on List Joins and Projections, experimenting with various CAML queries based on working Joins, and utilizing my Googlefu, I am still unable to determine if it is possible (or not) to perform a Join using a LookupMulti field.  It is not explicitly excluded in the documentation ("The field in the primary list must be a Lookup type field and it must lookup to the field in the foreign list"), but it doesn't seem to work right in my hours of experimentation.

    My gut says this does not work, but the documentation isn't explicit about this and I can't be sure that I'm not screwing up the CAML somewhere along the way.

    Please advise!

    Your's Truly,

    ~CharlieDigital


    zaanglabs.com | charliedigital.com | linkedin.com/in/charlescchen
    Saturday, August 27, 2011 9:45 PM

Answers

  • Looked into this a bit today and from what I can tell, the CAML List Join in SharePoint 2010 is designed to work with Lookup type fields only, not LookupMulti. This is a limitation of the current design.


    Patrick Cole - MSFT
    • Marked as answer by CharlieDigital Thursday, September 22, 2011 7:19 AM
    Wednesday, September 21, 2011 10:39 PM

All replies

  • Hi ,

     

    As far as I know ,it is possible to Join using  a LookupMulti field in CAML . When working with relational lists in SharePoint 2010, you have the option to use LINQ or CAML to join those lists to pull data out.

     

    The site listed below shows how to achieve this ,you can have a look at :

    SP 2010 - LINQ versus CAML Joins and the Nuances of Projected Fields: http://sharepointlearningcurve.blogspot.com/2010/05/sp-2010-linq-versus-caml-joins-and.html

     

    Thanks,


    Entan Ming
    Monday, August 29, 2011 2:06 AM
  • This looks promising!

    I will give it a try in the morning and report back.

    Thanks!

    ~CharlieDigital


    zaanglabs.com | charliedigital.com | linkedin.com/in/charlescchen
    Monday, August 29, 2011 3:13 AM
  • Hmm doesn't seem to work.

    Here is a sample CAML query that works:

    <View>
     <Query>
     <Where>
     <Eq>
     <FieldRef Name='Title' />
     <Value Type='Text'>Adam Peterson</Value>
     </Eq>
     </Where>
     <OrderBy Override='TRUE' />
     </Query>
     <ViewFields>
     <FieldRef Name='IC_Inv_Facilities' LookupId='TRUE' />
     <FieldRef Name='IC_Inv_ExpProfile' LookupId='TRUE' />
     <FieldRef Name='IC_Inv_SiteInfo' LookupId='TRUE' />
     <FieldRef Name='IC_Inv_Name_Prefix' />
     <FieldRef Name='IC_Inv_FirstName' />
     <FieldRef Name='IC_Inv_MiddleInitial' />
     <FieldRef Name='IC_Inv_LastName' />
     <FieldRef Name='IC_Inv_Suffix' />
     <FieldRef Name='IC_Inv_ProfessionalTitle' />
     <FieldRef Name='Title' />
     </ViewFields>
     <ProjectedFields>
     </ProjectedFields>
     <Joins>
     <Join Type='INNER' ListAlias='IC_Inv_SiteInfo'>
     <!--List Name: IC Site Information-->
     <Eq>
     <FieldRef Name='IC_Inv_SiteInfo' RefType='ID' />
     <FieldRef List='IC_Inv_SiteInfo' Name='ID' />
     </Eq>
     </Join>
     </Joins>
     <RowLimit Paged='TRUE'>10</RowLimit>
    </View>
    

    Notice that this only specifies the Join, but does NOT project any fields or use any of the fields from the second list in the view.  I've purposefully simplified this to ensure that there were no errors in the projection step.  This works fine; no errors.  The field IC_Inv_SiteInfo is a single-valued lookup.

    Now I change the Join to use the field IC_Inv_Facilities, which is a multi-valued lookup (LookupMulti):

    <View>
     <Query>
     <Where>
     <Eq>
     <FieldRef Name='Title' />
     <Value Type='Text'>Adam Peterson</Value>
     </Eq>
     </Where>
     <OrderBy Override='TRUE' />
     </Query>
     <ViewFields>
     <FieldRef Name='IC_Inv_Facilities' LookupId='TRUE' />
     <FieldRef Name='IC_Inv_ExpProfile' LookupId='TRUE' />
     <FieldRef Name='IC_Inv_SiteInfo' LookupId='TRUE' />
     <FieldRef Name='IC_Inv_Name_Prefix' />
     <FieldRef Name='IC_Inv_FirstName' />
     <FieldRef Name='IC_Inv_MiddleInitial' />
     <FieldRef Name='IC_Inv_LastName' />
     <FieldRef Name='IC_Inv_Suffix' />
     <FieldRef Name='IC_Inv_ProfessionalTitle' />
     <FieldRef Name='Title' />
     </ViewFields>
     <ProjectedFields>
     </ProjectedFields>
     <Joins>
     <Join Type='INNER' ListAlias='IC_Inv_Facilities'>
     <!--List Name: IC Site Information-->
     <Eq>
     <FieldRef Name='IC_Inv_Facilities' RefType='ID' />
     <FieldRef List='IC_Inv_Facilities' Name='ID' />
     </Eq>
     </Join>
     </Joins>
     <RowLimit Paged='TRUE'>10</RowLimit>
    </View>
    

    This does NOT work and I get an error "Value does not fall within the expected range" if I try to call items.Count from the command window in debug mode:

    System.ArgumentException was unhandled
     Message=Value does not fall within the expected range.
     Source=Microsoft.SharePoint.Library
     StackTrace:
     at Microsoft.SharePoint.Library.SPRequestInternalClass.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)
     at Microsoft.SharePoint.Library.SPRequest.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)
     at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()
     at Microsoft.SharePoint.SPListItemCollection.get_Count()
     InnerException: 
    

    There are other single-valued lookups and other multi-valued lookups in the content type and I am observing the same pattern: Join to single-valued lookups work fine, but Join to multi-valued lookups fail with the ArgumentException with the only change between the queries being the field being Joined on.


    zaanglabs.com | charliedigital.com | linkedin.com/in/charlescchen





    Monday, August 29, 2011 3:13 PM
  • Looked into this a bit today and from what I can tell, the CAML List Join in SharePoint 2010 is designed to work with Lookup type fields only, not LookupMulti. This is a limitation of the current design.


    Patrick Cole - MSFT
    • Marked as answer by CharlieDigital Thursday, September 22, 2011 7:19 AM
    Wednesday, September 21, 2011 10:39 PM
  • Patrick, this seems to confirm my observations, so thanks for the followup; it's appreciated.


    zaanglabs.com | charliedigital.com | linkedin.com/in/charlescchen
    Thursday, September 22, 2011 7:19 AM