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

john john 946 Reputation points
2021-07-30T21:23:24.323+00:00

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

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,757 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CaseyYang-MSFT 10,321 Reputation points
    2021-08-02T09:41:21.403+00:00

    Hi @john john ,

    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.