SPFx – How to handle large list items?

Introduction

Happy new year to everyone…

Hi friends, its been a long since I posted an article. We will start this year with this article on how to handle the large list items using SharePoint Framework and PnP JS. Many of you aware of using PnPJS to read or write content to SharePoint and other services. What if there are lot of items (more than 5000) in a list which is beyond the threshold? what are all the different methods that are available so that you can handle those items without hitting the throttle error message. Let us see the methods and their usage.

Focus on the Code

I have created a sample list with more than 14K items with different field types like Single line of text, Muliple line of text, Choice, DateTime, Yes or No and Lookup.

Let us start by creating a new SPFx webpart project using SharePoint yeoman generator, before that create a folder where you would like to create the web part.

You can also follow the documentation from Microsoft to create the new extension project.

Build your first SharePoint client-side web part

Navigate to the folder and run the below command.

yo @microsoft/sharepoint

The generator will asks you couple of questions,

Enter the webpart name as your solution name, and then select Enter.
Select Create a subfolder with solution name for where to place the files.
Select N to allow the solution to be deployed to all sites immediately.
Select N on the question if solution contains unique permissions.
Enter the webpart name
Enter the webpart description
Choose the framework as ‘React‘

Note: I have used @microsoft/generator-sharepoint version 1.11.0. But you can also use other versions and make sure you install the pnp package supported by the spfx version.

Once the project is created, make sure the web part is running without any issue. Its better to actually run the vanilla web part once it is created. Following are the packages used

@pnp/sp
handlebars
html-decoder

Use the below imports to the webpart.tsx file

import * as React from 'react';
import { useEffect, FC } from 'react';
import styles from './LargeListItems.module.scss';
import { sp } from '@pnp/sp';
import '@pnp/sp/webs';
import '@pnp/sp/lists';
import '@pnp/sp/items/list';
import { IItem, Items, PagedItemCollection } from '@pnp/sp/items';
import * as HTMLDecoder from 'html-decoder';
import * as Handlebars from "handlebars";
import { PrimaryButton } from 'office-ui-fabric-react/lib/Button';
import { Stack } from 'office-ui-fabric-react/lib/Stack';
import { IStackTokens } from 'office-ui-fabric-react';
import { qry_bizSegment, qry_cifnoSearch, qry_segmentSearch } from './CamlQuery';

Modify the render method to modify the default vanilla design. It has various buttons with different actions which will be easy to understand and can be used as a future reference too.

<div className={styles.largeListItems}>
            <div className={styles.container}>
                <div className={styles.row}>
                    <div className={styles.column}>
                        <Stack horizontal horizontalAlign={'start'} tokens={containerStackTokens} wrap>
                            <Stack.Item>
                                <PrimaryButton text="Get All Items" onClick={_allItemsClick} />
                            </Stack.Item>
                            <Stack.Item>
                                <PrimaryButton text="PnP Paged All Items" onClick={_pnpPagedSearchClick} />
                            </Stack.Item>
                            <Stack.Item>
                                <PrimaryButton text="PnP Paged Search with Segment" onClick={_pnpPagedSearchSegmentClick} />
                            </Stack.Item>
                            <Stack.Item>
                                <PrimaryButton text="PnP Filter with CIF No" onClick={_normalSearchClick} />
                            </Stack.Item>
                            <Stack.Item>
                                <PrimaryButton text="CAML Query filter with CIF No" onClick={_camlQuerySearchCIFNo} />
                            </Stack.Item>
                            <Stack.Item>
                                <PrimaryButton text="PnP Filter with Segment" onClick={_normalSegmentSearchClick} />
                            </Stack.Item>
                            <Stack.Item>
                                <PrimaryButton text="Caml Query filter with Segment" onClick={_camlQuerySegmentSearchClick} />
                            </Stack.Item>
                            <Stack.Item>
                                <PrimaryButton text="Normalized Caml Query filter with Segment" onClick={_normCamlQuerySegmentSearchClick} />
                            </Stack.Item>
                        </Stack>
                    </div>
                </div>
            </div>
        </div>

Below are the few of the constants that is used in this web part.

const containerStackTokens: IStackTokens = { childrenGap: 20 };
const pageSize: number = 2000;
const targetList: string = '<Large list name>';

Replace the <Large list name> with the actual list title. The containerStackTokens is the spacing for the items in the Stack container. pageSize will be used in the later part of the code.

Create a file named CamlQuery.ts and paste the below code. You can change the field name based on your list structure.

export const qry_bizSegment: string = `
<View>
    <Query>
        <Where>
            <And>
                <And>
                    <Geq>
                        <FieldRef Name="ID"/>
                        <Value Type="Number">{{minid}}</Value>
                    </Geq>
                    <Lt>
                        <FieldRef Name="ID"/>
                        <Value Type="Number">{{maxid}}</Value>
                    </Lt>
                </And>
                <Eq>
                    <FieldRef Name="BizSegment"/>
                    <Value Type="Text">{{searchKey}}</Value>
                </Eq>
            </And>
        </Where>
        <OrderBy>
            <FieldRef Name="ID" Ascending='FALSE'/>
        </OrderBy>
    </Query>
</View>
`;
export const qry_segmentSearch: string = `
<View>
    <Query>
        <Where>            
            <Eq>
                <FieldRef Name="BizSegment"/>
                <Value Type="Choice">{{searchKey}}</Value>
            </Eq>
        </Where>
    </Query>
</View>
`;
export const qry_cifnoSearch: string = `
<View>
    <Query>
        <Where>            
            <Eq>
                <FieldRef Name="CIFNo"/>
                <Value Type="Text">{{cifno}}</Value>
            </Eq>
        </Where>
    </Query>
</View>
`;

Now let us add different methods to the webpart.tsx file

const _getTemplateValue = (template: string, value: any): string => {
    const hTemplate = Handlebars.compile(HTMLDecoder.decode(template));
    return HTMLDecoder.decode(hTemplate(value));
};

Above is a generic method to pass the caml query template and the values so that the values are replaced in the template and the final query is passed to the actual method.

Get All Items

const _allItemsClick = async () => {
    let filItems = await sp.web.lists.getByTitle(targetList).items.getAll();
    console.log("Get All Items: ", filItems);
}

The above code use the getAll method from pnp package to get all the items from the list. The result is added to the browser console. Below is the screenshot of the console and network tab. It returns all the items but in the network tab the single query is passed as mutliple query automatically by the pnp.

PnP Paged All Items

const _pnpPagedSearchClick = async () => {
    let finalItems: any[] = [];
    let items: PagedItemCollection<any[]> = undefined;
    do {
        if (!items) items = await sp.web.lists.getByTitle(targetList).items.top(2000).getPaged();
        else items = await items.getNext();
        if (items.results.length > 0) {
            finalItems = finalItems.concat(items.results);
        }
    } while (items.hasNext);
    console.log("PnP Paged All Items: ", finalItems);
};

The above code does the same as the previous, but here we are sending the query manually and by using the next link we can generate the next batch query. We can use the above method incase if there is a need to get a set of items and not the all but the items exceed the threshod limit and we can also do some other business logic using the above method.

PnP Paged Search with Segment

const _pnpPagedSearchSegmentClick = async () => {
    let finalItems: any[] = [];
    let items: PagedItemCollection<any[]> = undefined;
    do {
        if (!items) items = await sp.web.lists.getByTitle(targetList).items.top(2000).filter(`BizSegment eq 'GC'`).getPaged();
        else items = await items.getNext();
        if (items.results.length > 0) {
            finalItems = finalItems.concat(items.results);
        }
    } while (items.hasNext);
    console.log("PnP Paged Search with Segment: ", finalItems);
};

The above code just add the filter on the choice field and try to get the paged items. In my list the BizSegment field has only 2 choices and each will return more than 5K items. When we execute the code will get the list view threshold exception. We will see how we can resolve this issue with the filter in the later methods.

PnP Filter with CIF No

const _normalSearchClick = async () => {
    let filItems = await await sp.web.lists.getByTitle(targetList).items.select('ID')
        .filter(`CIFNo eq '11606111'`).get();
    console.log("PnP Filter with CIF No: ", filItems);
};

The above code use the filter to search for one item. In my list the CIF No field is unique Single line of text field. Though the list has more than 14K items, using the filter with unique values will return the data without any throttle issue.

CAML Query filter with CIF No

export const qry_cifnoSearch: string = `
<View>
    <Query>
        <Where>            
            <Eq>
                <FieldRef Name="CIFNo"/>
                <Value Type="Text">{{cifno}}</Value>
            </Eq>
        </Where>
    </Query>
</View>
`;

const _camlQuerySearchCIFNo = async () => {
    let camlQuery: string = _getTemplateValue(qry_cifnoSearch, { cifno: '11606111' });
    let filItems = await sp.web.lists.getByTitle(targetList).getItemsByCAMLQuery({
        ViewXml: camlQuery
    });
    console.log("CAML Query filter with CIF No: ", filItems);
};

The above code use the CAML query to search for the CIF No. Here we have used the handlebar to replace the template with the dynamic values.

PnP Filter with Segment

const _normalSegmentSearchClick = async () => {
    let filItems = await sp.web.lists.getByTitle(targetList).items.select('ID')
        .filter(`BizSegment eq 'GC'`).get();
    console.log("PnP Filter with Segment: ", filItems);
};

The above code is similar to the code mentioned above where we filter the same BizSegment field using paged method. Here a normal method using the same filter and now also we hit the list view threshold exception.

Caml Query filter with Segment

export const qry_segmentSearch: string = `
<View>
    <Query>
        <Where>            
            <Eq>
                <FieldRef Name="BizSegment"/>
                <Value Type="Choice">{{searchKey}}</Value>
            </Eq>
        </Where>
    </Query>
</View>
`;

const _camlQuerySegmentSearchClick = async () => {
    let camlQuery: string = _getTemplateValue(qry_segmentSearch, { searchKey: 'GC' });
    let filItems = await sp.web.lists.getByTitle(targetList).getItemsByCAMLQuery({
        ViewXml: camlQuery
    });
    console.log("Caml Query filter with Segment: ", filItems);
};

The above code tries to filter the BizSegment field using CAML query and it also return the threshold exception. The next method will solve all our problems and you can use that method as a default for all the lists even with or without large number of items.

Normalized Caml Query filter with Segment

export const qry_bizSegment: string = `
<View>
    <Query>
        <Where>
            <And>
                <And>
                    <Geq>
                        <FieldRef Name="ID"/>
                        <Value Type="Number">{{minid}}</Value>
                    </Geq>
                    <Lt>
                        <FieldRef Name="ID"/>
                        <Value Type="Number">{{maxid}}</Value>
                    </Lt>
                </And>
                <Eq>
                    <FieldRef Name="BizSegment"/>
                    <Value Type="Text">{{searchKey}}</Value>
                </Eq>
            </And>
        </Where>
        <OrderBy>
            <FieldRef Name="ID" Ascending='FALSE'/>
        </OrderBy>
    </Query>
</View>
`;

const _getMaxIdForList = async (listname: string): Promise<number> => {
    let maxItems: any[] = await sp.web.lists.getByTitle(listname).items
        .select('ID')
        .orderBy('ID', false)
        .top(1)
        .get();
    if (maxItems.length > 0) return maxItems[0].ID;
    else return 0;
};

const _searchLargeList = async (itemsQuery: string, searchKey: string): Promise<IItem[]> => {
    let minid: number;
    let maxid: number;
    let listmaxid: number = await _getMaxIdForList(targetList);
    let maxPage: number = Math.ceil(listmaxid / pageSize);
    let returnItems = [];
    for (var i = 0; i < maxPage; i++) {
        minid = i * pageSize + 1;
        maxid = (i + 1) * pageSize;
        console.log(`Min id: ${minid.toString()} - Max Id: ${maxid.toString()}`);
        let camlQuery: string = _getTemplateValue(itemsQuery, { searchKey: searchKey, minid: minid, maxid: maxid });
        let retitems: IItem[] = await sp.web.lists.getByTitle(targetList).getItemsByCAMLQuery({
            ViewXml: camlQuery
        });
        if (retitems.length > 0) {
            returnItems = returnItems.concat(retitems);
        }
        if (i >= maxPage - 1) return returnItems;
    }
    return returnItems;
};

const _normCamlQuerySegmentSearchClick = async () => {
    let filItems = await _searchLargeList(qry_bizSegment, 'GC');
    console.log("Normalized Caml Query filter with Segment: ", filItems);
};

In this method we are using the CAML query but with additional params. The method _normCamlQuerySegmentSearchClick is associated with the button. It calls the _searchLargeList method with caml query and value as the props.

The _searchLargeList method will do the following to get the items without throttle issue.

Get the max id of the target list
Based on the pageSize const we define the maximum pages in the list to loop. For 140000 items with 2000 items per page the max page will be 7.
Loop the pages and for each page do the following
- Get the minimum id
- Get the maximum id
- Pass the min id, max id and segment value to the caml query
- Collect the array items and concatenate the array of items to the final array.
- End the loop when the max page is greater than the max page defined above.

Using the above logic, we can loop through all the items and filter the content withou hitting the throttle issue. You will also see the min and max id since I had added that values to the browser console.

In the above screenshot we didnt get the whole items, we had used the filter and returned only 10000+ items without hitting the throttle issue.

Conclussion

I hope you had learned some new ways of filtering the data from the large lists without worrying about the throttle issue. You can also found the link to the code repo. Not only this code but you can also found some other useful code.

SPFx-Demos

Happy Sharing…

3 thoughts on “SPFx – How to handle large list items?”

Ofer Gal

January 18, 2022 at 12:55 AM

Do you have a working example to add a file to a library that contains already files more than the list view threshold?
I have few sites with libraries that tend to get big fast. and using any programmatic upload fails with the ist view threshold error.
Thanks

LikeLike

- Sudharsan K...
  
  January 18, 2022 at 10:42 AM
  
  Hi, let me try from my end and will let you know the solution. Give me sometime and I will update you asap.
  
  LikeLike
  
  - Ofer Gal
    
    January 18, 2022 at 9:19 PM
    
    Thank you
    
    LikeLike