Web Part that Displays Over 5000 Items

Business Use Case: I want to create a web part that shows data from a list that contains more than 5000 items. Most SQL database folks would say, “What’s the big deal? That should be easy”. In SQL server, 5000 rows is nothing — and SharePoint uses SQL server as its data layer.

It turns out that in SharePoint Online, it is a big deal. That is because SPO sets a limit on the number of list items that can be returned in one REST call at 5000 (which is also the maximum number of items you can have in a view.) In SharePoint Online, you can have a list with hundreds of thousands (even millions) of items. If you are creating a client-side web part, your only choice is to use a REST call or CSOM. So how can we show more than 5000 items in a web part?

Your best bet would be to use the search API in your web part. But let’s pretend that you need to load all of the items in the list. In my case I have a list that shows all ‘overshared’ content in our Office 365 tenant. We want to have a drop down to chose the site owner. And then another drop down to choose the site. And finally another drop down to choose a library in a site. When a library is selected, all ‘overshared’ content in that library will be displayed.

I want to give a caveat at the beginning, this is not a strategy for a list that contains millions of records. In fact, I wouldn’t use this strategy on a list with over a hundred thousand items — and even I would use caution.

Here are the steps

  1. Setup the API call by creating the endpoint url in the componentWillMount() method. This is the endpoint for the REST call to get the first 5000 items.
 public componentWillMount() {
    let siteUrl = "https://yourdomain.sharepoint.com/sites/SharedContent";
    let listName = "OverSharedList";
    let fieldsToReturn = "$select=Title,SiteUrl,SiteName,Library,FileName_1";
    let filter = "";
    let top = "&$top=5000";
    let order = "&$orderby=ID";
    let endpoint = siteUrl + "/_api/web/lists/GetByTitle('" + listName + "')/items?" 
    + fieldsToReturn + filter + top + order;

    this._getItemsForUser(endpoint);
  }

2. Call the method to get the items. I called the _getItemsForUser() method.

3. This method should be able to be called recursively. Recursion is the key to this tricky operation. I will explain the method as we implemented it.

The only parameter we pass to this method is the endpoint.

Notice that I set the state “showLoading”. In React, setState() enqueues changes to the component state and tells React that this component and its children need to be re-rendered with the updated state. In this case, this is a nifty way to let the user know that you are loading data.

Next, I will call our data service (you will need to build this.) Ours is called “SharedServices”. We are passing the SPHTTPContext and the endpoint.

That last line is the key to getting the next endpoint. It is something that is built into SharePoint OData REST calls.

If the nextEndpoint variable contains data, then there is more data to load, so call the method recursively. If it is blank, then you have all the data and can continue processing.

Here is the entire recursive method:

 
private _getItemsForUser(endpoint:string){    
    this.setState({showLoading:true});
    SharedServices.getListDataRecursive(this.props.context.spHttpClient, endpoint)
    .then((result:any)=> {
      let data = result.value;
      this.allItems = this.allItems.concat(data);
      let nextEndpoint = result["@odata.nextLink"];
      if(nextEndpoint && nextEndpoint != "")
      {
        this._getItemsForUser(nextEndpoint);
      }
      else{
        if(!this.props.ProductionMode)
        {
          this.filterOutData(this.props.context.pageContext.user.email);
        }
        else{
          this.getSiteOwners();
        }
        
      }      
    });
  }

After you have processed the records, you can display them in your view. If you are using React, you can use the render() method.

How Can We Help? Find out how Lone Peak Software can help you with SharePoint development and configuration.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close