Lookup column, list threshold

rss

Posted by Ali - Mar 8 ’16 at 11:16

Hi

I am using cross site lookup column in sharepoint online, I am getting error because list has more than 500 items.

I am looking for following options:

lookup column populates value from the list when user clicks in the textbox, Is there any option to prevent sending queries without typing anything in the looklup column text box?

I want to send query to the list when user types at least 3 characters, can i do that?

 

  • Dmitry Kozlov
    Dmitry Kozlov
    Admin
    Member for: 8 years 4 months 2 days
    #1 by Dmitry Kozlov Mar 9 ’16 at 02:40

    Hello Ali,

    First, you need to create indices for the columns by which Cross-site Lookup filters and sorts the source list: ‘Created’ and the display column:
    List settings -> Columns -> Indexed columns.

    If it does not help, split the source list into multiple ones and create a lookup column for each of these lists. Next, display a particular lookup on a form conditionally based on some pivot field, say Type or Category.

  • Ali
    Member
    Member for: 5 years 10 months 21 days
    #2 by Ali Mar 9 ’16 at 05:47

    thank you for your reply but I am looking for different options, if you could please let me know that following options are available or not.

     

    lookup column populates value from the list when user clicks in the textbox, Is there any option to prevent sending queries without typing anything in the looklup column text box?

    I want to send query to the list when user types at least 3 characters, can i do that?

  • rostislav
    rostislav
    Moderator
    Member for: 6 years 8 months 17 days
    #3 by rostislav Mar 9 ’16 at 09:26

    [#2]:

    Ali,

    This isn't an issue with the length of the filter term, the issue has to do with the limit Microsoft imposes on the size of a list in SharePoint Online. Filtering a list with more that 5k items is not allowed unless you do the following things:

    1. Index the columns that you use to filter the list items by: https://support.office.com/en-us/article/Manage-lists-and-libraries-with-many-items-fba0c8b7-79bb-4ea4-9aff-bf145a689e8e#bm2

    2. orderby will still cause an error if the result set is bigger than 5k, so what we can do is remove orderby from the first query (that gives all items), but leave it in for the second query e.g.:

    function (term, page) {
      if (!term || term.length == 0) {
        return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$top=10";
      }
      return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby={LookupField}&$filter=startswith({LookupField}, '" + encodeURIComponent(term) + "')&$top=10";
    }
    
Displaying 1 to 3 of 3 messages