Maximum lookup columns - the magic number 13

Ovidiu Becheş-Puia
  • Maximum lookup columns - the magic number 13 Ovidiu Becheş-Puia

    So I did create on an SPWeb 20 custom lists and then a tasks list. For each of those 20 lists I've created 20 site lookup columns. I then created 20 Content Types based on Task content type. Then I've added each content type to the Tasks list. After that I've added each lookup site column to the corresponding list content type. So far so good.

    I've built a custom WebPart that displays task items using ListViewByQuery control. Here is how an SPQuery looks:

    <Query>    
        <Where>
            <Eq>
                <FieldRef Name='Relaterat_x0020_till_x0020_avtal' LookupId='TRUE' />
                <Value Type='Lookup'>1</Value>
            </Eq>    
        </Where>
    </Query>
    

    The problem is that this SPQuery returns results only for the first 13 lookup columns. If I put in the FieldRef the internal name for lookup column no 14 or above, it is not working anymore. Why?

    If I don't use LookupId='TRUE' and do this: (use the value of the lookup and not the id)

    <Query>    
        <Where>
            <Eq>
                <FieldRef Name='Relaterat_x0020_till_x0020_avtal' />
                <Value Type='Lookup'>Test avtal</Value>
            </Eq>    
        </Where>
    </Query>
    

    then it is working.

    Here is the code:

    // create listViewByQuery
    ListViewByQuery listViewByQuery = new ListViewByQuery();
    listViewByQuery.List = list;
    SPQuery query = new SPQuery(view);
    
    System.Text.StringBuilder sbViewFields = new System.Text.StringBuilder();
    
    // all fields from view
    foreach (string field in view.ViewFields)
    {
        sbViewFields.Append("<FieldRef Name='" + field + "'/>");
    }
    
    string lookupID = HttpContext.Current.Request.QueryString["ID"].ToString();
    query.Query = "<Where><Eq><FieldRef Name='" + lookupInternalName + "' LookupId=\"TRUE\" /><Value Type='Lookup'>" + lookupID + "</Value></Eq></Where>";
    
    listViewByQuery.Query = query;
    this.Controls.Add(listViewByQuery);
    

    Update:

    I've built a C# console app which creates all lists / lookup columns / content types and add the lookup column to corresponding content type. Result: same thing, same magic number 13. Anyone interested in seeing and running the script send me an email at: ovulex [ at ] yahoo.com.

    I will report this to Microsoft.

    ScreenShot http://img63.imageshack.us/img63/6412/magicnumber13.png

  • I've not experienced this myself, but my guess is that it has something todo with SQL limitations. I would turn on the SQL profiler and look at the actual query that is executed in SQL and analyze that. No answer, but an idea on to how to figure it out...

  • I'm wondering if it would make any difference if you would explicitly reference each field within the ViewFields element.

Related questions and answers
  • . Scoped to Site. Registration: contains a ListTemplate and a ListInstance that just modifies a basic Custom List to enable content types and add my custom content type. Scoped to Web. Here's what's happening. When I activate ContentTypes, the content type and field are deployed as expected into my site collection. When I activate Registration, the list is provisioned and the content type is added as expected. When I try to add a new item to the list, the list form only shows the Title field from Item. Now, here's the weird part. If I add a second content type to the list (through the UI

  • ; &lt;FieldRef Name="ID" /&gt; &lt;!-- Field which do not return values --&gt; &lt;FieldRef Name="Responsible" Nullable="TRUE" Type="UserMulti" /&gt; &lt;FieldRef Name="Accountable" Nullable="TRUE" Type="UserMulti" /&gt; &lt;!-- // Field which do not return values --&gt; &lt;ProjectProperty Name="Title" /&gt; &lt;/ViewFields> &lt;/View> &lt;Query>... Nullable="TRUE" and Type="UserMulti". This works, in the query now returns the correct number of records, however the value in my 2 multi user fields is empty. &lt;View> &lt;Webs Scope="Recursive

  • I have successfully created a DVWP which queries all lists in my subsites and returns the correct columns using the following selectcommand: &lt;View> &lt;Webs Scope="Recursive"&gt;&lt...; &lt;FieldRef Name="ID" /&gt; &lt;FieldRef Name="StreamName" /&gt; &lt;/ViewFields> &lt;/View> &lt;Query>; &lt;OrderBy> &lt;FieldRef Name="StreamName" Ascending="TRUE" /&gt; &lt;FieldRef Name="Title" Ascending="TRUE" /&gt; &lt;/OrderBy> &lt;/Query>; &lt;/View> One of the things i would now

  • :EcmaScriptEncode($ListName),'};'))}"&gt;&lt;/input>&lt;/td> I need to add javascript within the DataFormWebPart so I can update another form field when the user clicks the 'Complete Task' button. I..." __designer:bind="{ddwrt:DataBind('u',concat('ff2',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@Comments1')}"/&gt; &lt;SharePoint:FieldDescription runat... type="button" name="btnMarkSubmited" value="Complete Task" onclick="javascript: {ddwrt:GenFireServerEvent(concat('__update;__workflowTaskComplete={',ddwrt:EcmaScriptEncode(string($Id)),'*',ddwrt

  • Has anybody upgraded an old custom field type to use the new XSL transform in SharePoint 2010? I have a multi-column field type, and want the Display pattern to render the 2nd "column", which contains a hyperlink. My CAML render pattern looks like: &lt;RenderPattern Name="DisplayPattern"&gt; &lt;Switch> &lt;Expr> &lt;Column /&gt; &lt;/Expr> &lt;Case Value...;/RenderPattern> In WSS 3.0, this would render the 2nd column in the field as a hyperlink in the LVWP, which is what I want. In SharePoint 2010, I tried adding TRUE to my field type definition

  • I've customized alert notification template for Announcements list. Here is custom code: &lt;Immediate> &lt;Subject> &lt;Switch> &lt;Expr> &lt;GetVar Name="EventType" /&gt; &lt;/Expr> &lt;Case Value = "1"&gt; &lt;HTML>New Content on Latest Events</HTML> &lt;/Case> &lt;Case Value = "2"&gt...; &lt;Case Value = "1"&gt; &lt;HTML> &lt;![CDATA[A new content has been added to Latest Events. To view, please visit: &lt;a href

  • " type="string" /&gt; &lt;property name="ContentTypeBeginsWithId" type="string" /&gt; Does anyone know if these can take multiple values and if so what is the format? Are there any other...Is it possible to use the Content Query web part to filter on multiple specific content types that do not have a parent/child relationship? I have a requirement to show Events (from a calendar) and Announcements (from an Announcement list) aggregated in the same area but as these come from two different branches in the content type tree, I can't do this via configuration of the web part. Would

  • "/&gt; &lt;xsl:value-of select="sum($Rows/@Distance[.!=''])" /&gt; &lt;/xsl:template> &lt;/xsl:stylesheet> The result is: "NaN", so I figure that my XSLT is wrong. Question...-type" select="$attr-type"/&gt; &lt;xsl:with-param name="res" select="$res + number(translate($currnode/@*[name() = $attr-type],',','.'))"/&gt; &lt;/xsl:call-template> &lt;/xsl:when> &lt;xsl:otherwise> &lt;xsl:value-of select="format-number($res + number(translate($currnode/@*[name() = $attr-type

  • on the built-in Custom List template) which references the custom site columns, and applies the custom content type. We recently changed the schema of one of the custom site columns. Specifically..." ShowInFileDlg="FALSE" DisplayName="Current Status" Description="The current status."&gt; &lt;/Field> And here is the updated schema with the new 'Note' type: &lt;Field ID...I am having a problem with a custom site column in SP2007, whose field type recently changed. I have defined features for the following artifacts, which are deployed at the site collection scope