question

johnjohn-0472 avatar image
0 Votes"
johnjohn-0472 asked MatthewGudites-9579 commented

To overcome threshold error on lookup fields, is it recommended/supported if i only store the parent ID inside the child records inside numeric field

I have 2 lists Child and Parent inside a SharePoint online site, where the child list has a lookup field to the parent list. Currently the parent list has 8,000++ items as follow:-

119505-2-lisys.png

so now when i create a new child record the parent lookup field will show this error:-

This is a lookup column that displays data from another list that currently exceeds the List View Threshold defined by the administrator (5000).

as follow:-

119538-parent.png

so to overcome this error is it fine if i remove the parent lookup field from the child list, and just store the parent id inside a numeric field? i know this means more overhead when trying to get the child records for a parent record, but atleast it will allow us to maintain the relation between 2 lists which exceeds the threshold...

Any idea on this please?

thanks


office-sharepoint-online
2-lisys.png (4.6 KiB)
parent.png (18.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm dealing with the same problem now and I'm just stuck. I literally have no options here.

  1. The lookup is now broken. Indexing columns doesn't fix it.

  2. I can't use the "Move" or "Copy" features to simply move some of the 5,000+ documents to an identical document library, because I have field validations and you can't move/copy when you have those.

  3. Can't create a blank document library for copying and pasting some of these files to, because then I lose all of my column values.

Like, what year are we in?! A 5,000 limitation? Microsoft really can't figure out a way around this?




0 Votes 0 ·

1 Answer

CaseyYangMSFT-4714 avatar image
0 Votes"
CaseyYangMSFT-4714 answered CaseyYangMSFT-4714 commented

Hi @johnjohn-0472,

You could try to add index column as a workaround to solve this issue.

List settings > Columns > Indexed columns > Create a new index

Note: Automatic index creation when sorting in the modern experience is limited to lists and libraries with less than 20,000 items.

For Reference:
https://support.microsoft.com/en-us/office/add-an-index-to-a-list-or-library-column-f3f00554-b7dc-44d1-a2ed-d477eac463b0
https://www.softlanding.ca/blog/overcoming-5000-item-list-view-threshold-sharepoint-online/
Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.


If an Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@CaseyYangMSFT-4714 i already defined indexes inside the child list's lookup field + the Title column on the parent list and this did not fix the issue..

0 Votes 0 ·

Hi @johnjohn-0472

"When you create a filtered view, make sure the first indexed column in the filter expression does not exceed the List View Threshold. SharePoint selects the first indexed column in a query. Other columns you specify in the view filter may or may not be indexed, but the view does not use those indexes, even if the result of the filtered view returns less than the List View Threshold." It seems like with indexed columns can't overcome the list view threshold in SharePoint online.

0 Votes 0 ·