Creating/Updating Sharepoint List with Over 5,000 Items

(0) Share

Report

Posted on by Nick_Lee

Hi All,

Current Project:

SharePoint has a running list of patient ID's and their lab values. Every month, I receive an Excel containing patient ID's who have updated lab values or are brand new patients. I have a flow that compares the patients' medical record number on both Share Point and the monthly Excel to determine whether it is a new patient (create a new item) or an existing patient (update an item). The idea is to having a growing list of patients and simultaneously updating their values monthly.

Issue:

My Share Point patient list has grown over 5,000 items and am now running into the limit. The last flow created duplicate patients, which I assume is because my Get Items is only looking at the first 5,000 Share Point IDs, and has not happened in the past.

What is the best way to solve this problem? I've seen on some forums that Pagination is limited to 5,000 regardless of license, even though it looks like premium licenses may be able to go over that said limit. I've also read that using a HTTP request may also bypass that 5,000 item limit, but not sure how I would build it out.

Categories:

Using flows

I have the same question (0)

All responses (6)

Answers (1)

Sort by

David_MA 14,649 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

I can't really tell what you are doing from what you have provided so far. What column in your SharePoint list is storing the patient ID? Is it the Title column? And in the spreadsheet, is the patient ID the mrn column? If so, have you indexed the Title column in your SharePoint list? If not, try doing that to see if it resolves the problem.

Was this reply helpful? Yes No
Nick_Lee 10 on at

Like (0)

Report
Copy link

Link copied!

Hi David,

Correct. The title column is storing MRN and is indexed on the SharePoint list.

Was this reply helpful? Yes No
David_MA 14,649 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

I tried what you are doing on a list I have, which contains over 30,000 items and had no problems. This is the basic outline of my flow:

The Get items action has the following filter query:

In my list, Account represents your patient ID and is a number value, and is indexed in SharePoint. The Select action isn't really needed, I added it so I can easily compare the data from the successful run to what is created in the list.

Maybe this makes a difference, I am not sure. The default view in my list is sorted using an indexed column. If your default view is not using an indexed column for the sort order, you may want to try that.

Was this reply helpful? Yes No
Nick_Lee 10 on at

Like (0)

Report
Copy link

Link copied!

The default view is a filtered view based on Tracking Status of the patient. Ie, it only shows patients that still need to be assigned a priority for review. Therefore, it does have a limited item list, but I can change the default view to show all patients if needed.

I also stand corrected, the MRN column was not indexed. I've created the index column based on MRN just now, but in terms on the Power Automate logic, do I need to change any fields to reflect the indexed version?

Was this reply helpful? Yes No
Verified answer

David_MA 14,649 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

I think this is why your flow is not finding all of items since your default view is filtered. The get items action will use the default view unless you to tell it to use a different view. I would leave the view you have, and create a new view that is not filtered and is sorted by the ID column (this is automatically indexed by SharePoint). Then in the get items action, specify it to use this new view.

With regard to having to do anything now that you indexed the MRN column, I don't think you'll need to. Just note, if you think you will need to do something like this on another column in your list in the future, you need to make sure any columns you use in the filter query are indexed, and this needs to be done before your list reaches 20,000 items. Unless something has changed, you cannot index additional columns once it reaches 20,000 items without deleting items to get it under that number.

Was this reply helpful? Yes No
Nick_Lee 10 on at

Like (0)

Report
Copy link

Link copied!

David it worked like a charm, appreciate the help.

Side note, had a hiccup with one of the run instances with it taking 8 hours and the others taking 40 min - 1 hour, but as long as it works I'm happy for now.

Was this reply helpful? Yes No