cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Read values from SharePoint list with more than 10000 items

Hi,

 

I need to fetch distinct values from a column in SharePoint list containing more than 10000 items and bind it to a drop down list.

Would like to avoid hard coding these values in the app as these are dynamic. 

One way I can think of implementing is to maintain distinct values in a different list using a flow.

Looking for better ways to address this requirement.

 

Thanks,

SV

8 REPLIES 8
Dual Super User
Dual Super User

@sunithav 

 

In general, I avoid using Choice and Look Up fields in the SharePoint list when I use Power Apps as UI. Always, create a separate SharePoint list for look ups and feed them to the drop downs in the Power App by connecting the respective data sources. While storing the drop down value into SharePoint list, I store the reference item value and ID to bind it back in the drop down.

 

In your case, the list has 10000+ items. 

Are the values you want to fetch for drop down from the SharePoint list column added dynamically by the user? If not, create a separate list and store them manually.

If the values are added dynamically,

Write a Power Automate (one time use) to pull all the distinct values from the specific column and dump into the a list. Connect drop down to this list. Also, create another flow to run on a new list item creation to check if the specific value exists or not. If it does not exist, write into the list.

 

Regards

Krishna Rachakonda

If this reply helped you to solve the issue, please mark the post as Accepted SolutionMarking this post as Accepted Solution, will help many other users to use this post to solve same or similar issue without re-posting the issue in the group. Saves a lot of time for everyone.

 

 

Super User
Super User

Because your SharePoint list has 10000 items this is going to be a problem.    Please read this post as it goes into all the gotchas for what you are trying to do.

 

https://powerusers.microsoft.com/t5/Building-Power-Apps/Distinct-values-on-SharePoint-list/td-p/1342...



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

@sunithav ,

Please have look at the part of this blog of mine on Delegation that refers to "over 4,000 items" and see if this suits your model.

Also is it likely that the Distinct items are all in the newest 2,000 records. If so, please refer to another blog on the With() statement and this will allow you to extract them.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

@rsaikrishna, Thanks for the inputs. I have this already implemented as I import data into my SharePoint list on a daily basis. Was looking for ways to avoid multiple lists to capture the same data.

 

Thanks,

SV

@JR-BejeweledOne, Thanks for the link. I have read this and similar posts. Changing data row limit to 2000 did not help as my list grows beyond 10k items and distinct value can be after this number.

 

Thanks,

SV

@WarrenBelz, Thanks for the links. I did take a quick look and havent worked on "with".Its interesting. I will give this a try and let you know how it goes.

 

List has close to 11k records and new records are added on a daily basis. And yes distinct value can be in the newest records.

 

Thanks,

SV

[Deleted]



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

Thanks @sunithav ,

If all the distinct records are in the newest 2,000, you can do this

With(
   {
      wNewest:
      Sort(
         YourListName,
         ID,
         Descending
      )
   },
   Distinct(
      wNewest,
      YourFieldName
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (46,238)