How can I configure a View filter/index to go around the item threshold?

Question

I want to create a paginated view that shows me ALL the items, 30 items at a time. I have a list with 8000 items. The item limit is 5000 items, so I got this error on the "All Items" view:

This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.

I first created an index on the "Title" column. I set it as the first and only column in the view. I also set the item limit to 30 rows:

The next step is where I got stuck: Filtering. How am I meant to filter on the index? Title is not equal to NULL? I'm not sure how I can succesfully filter to prevent SharePoint from loading more than 30 rows at a time. I'm missing something here.

Related: Sharepoint list view threshold maximum limit? (Many more similar questions but I could not find the answer).

Answer 1

Basically, in order to get past the List View Threshold, you need to specify a filter on a column that is indexed (and returns less than 5000 items). If a column is not indexed you can choose to index a non-indexed column.

Excerpt from Office.com:

For a view to quickly filter through a large number of items, the first column that you specify in the filter must be indexed. Other columns you specify in the view filter may or may not be indexed, but the view does not use those indexes. You also need to make sure that the first column of the filter does not return more items than the List View Threshold, even if the final result of the filtered view returns less than the List View Threshold.

If the first column of the filter returns more items than the List View Threshold, you can use a filter with two or more columns. When you define a filtered view that uses two or more columns, using an AND operator will usually limit the total number of items returned. But even in this case, you still need to specify as the first column in the filter the column that most likely returns the lesser amount of data. Note that using an OR filter almost always increases the number of items returned and won't be effective under these circumstances.

To create an indexed column (Except here):

1. Navigate to the site that contains the list or library.
2. Click its name on the Quick Launch, or click on the Settings menu , click View All Site Content, and then click the name.
3. Click List or Library > List Settings or Library Settings.
4. Under the Columns section, click Indexed columns.
5. On the Indexed Columns page, click Create a new index.
6. Do one of the following: To create a simple index:
   1. In the Primary Column section, under Primary column for this index, select the column.
   2. Click Create. To create a compound index:
   3. In the Primary Column section, under Primary column for this index, select the column.
   4. In the Secondary Column section, under Secondary column for this index, select a different column.
   5. Click Create.

Answer 2

With SharePoint lists that exceed the list threshold, any use of item grouping or the use of field totals (sum, avg, count, etc...) will result in the "list view threshold" message if the data used by the group or total exceeds 5,000 records.

If there is a filter the restricts the number of records in the full query to less than 5,000 the use of groups or totals should continue to work as expected.