cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lucassbp
New Member

Remove Items from Sharepoint List with Over 2k Items

Hi all 🙂

 

What would be the correct approach to delete specific items from a SharePoint list with over 2k items?

To be more specific, I have a SharePoint list with ~3k items, and I need my users to be able to see, edit and remove only their specific items. Everything was working fine until we crossed the 2k items threshold, which I wasn't aware of, and now the users are not able to delete their items anymore.

On my research I understood that the problem is that I am using the `RemoveIf` function, which makes no use of Delegation and consequently cannot read more than 2k Items.

 

What would be the best approach to do this?

 

I am relatively new to PowerApps, and totally new to BUILDING them, so don't shoot me if this is an obvious questions 🙂

 

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Most Valuable Professional
Most Valuable Professional

HI @lucassbp ,

Just noting your comment, if you only want to remove ALL items belonging to a user, the real Delegation issue is that User().Email is not Delegable, so do this at App OnStart

Set(varUserMail,User().Email)

then your text column storing the user's email will be Delegable

RemoveIf(
   YourListName,
   YourEMailField=varUserMail
)

 

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.

View solution in original post

11 REPLIES 11
FrostedFlake
Resolver I
Resolver I

Hi,

the DataRow limit of Sharepoint lists seem to be 2000, according to the following documents:

https://docs.microsoft.com/de-de/powerapps/maker/canvas-apps/delegation-overview
https://powerapps.microsoft.com/de-de/blog/powerapps-data-row-limit-for-non-delegable-queries

There are functions like Filter, Search and LookUp that can be "deligated". Look for "delegation warnings" in your PowerApp designer. A solution would be in example to migrate your SharePoint list to an SQL Database or any other deligatable data source.

I am not experienced with PowerApps that manage tables that big, but i hope the information i found will lead you to success!

Let me know how you solved it (:

 

lucassbp
New Member

Hi,

 

Thanks for the response 🙂

Migrating to another Datasource at this stage is not an option for me, unfortunately.

 

I found the functions that you mentioned, but I am not sure how can I use them to remove items from the list.

 

I've tried doing `Remove(DataSource, Filter(/*...*/))`, but I don't think this will work, as I am still using the Remove filter which is non delegatable(?)

 

Do you have any idea if using the functions in this format would fix the problem? (Assuming that the Filter() function would return just a few items)

poweractivate
Most Valuable Professional
Most Valuable Professional

@lucassbp 

 

The default non-delegable limit is 500, not 2000 and it has to do with the App. Someone at some point raised it to the max, or 2000 in the App Settings of the specific App. This caused your issue to only be noticed after a while.

 

Recommend you make a new copy of the app and in this new copy set the non-delegable rows limit, instead of upwards from 500 to 2,000, set it far downwards even from 500 - set it to just 1 in your case we recommend - when testing any further on this app. Then it would be more obvious when it works or not. After you feel comfortable doing this, you can just set it like that for the real app as well to the non-delegable limit of 1. This should discourage further use of non-delegable queries (because they should almost never be used for most production scenarios, as most of them eventually involve data sources that go beyond 500 records or even beyond 2,000 records).

 

As for your original query, for that we would like to know more about the use case. Do you want people to remove the Gallery Item by pressing a button on the Gallery Item? Or is it something else? We need to understand more of it as we could not infer really why you were using RemoveIf in the first place, or even the Remove and Filter, so a high level overview of just the objective in next reply might be helpful to check if we can help further about it.

lucassbp
New Member

@poweractivate, thanks for the response.
Yes, a person in my team did raise the limit to 2000 and soon as we started facing the problem. It was meant as a debugging action, but we ended up not setting it back.

 

My scenario works like this: A user creates an order with multiple items and saves it. Each item on the order becomes an item on a SharePoint list, and an extra item is added to another list as the Order "header".
If the user wants to modify this order, he goes to the list and does the modifications he wants and saves it. As soon as the user is done with the change we remove all the items from the order list (RemoveIf()), and add all new ones.

The problem here is that when we crosse the 2k line, the updated items are added, but the original items are not removed....

So what is happening is that we have duplicated items now.

 

Hopefully that clarifies it.

 

Regards,


@lucassbp wrote:

Hi,

 

Thanks for the response 🙂

Migrating to another Datasource at this stage is not an option for me, unfortunately.

 

I found the functions that you mentioned, but I am not sure how can I use them to remove items from the list.

 

I've tried doing `Remove(DataSource, Filter(/*...*/))`, but I don't think this will work, as I am still using the Remove filter which is non delegatable(?)

 

Do you have any idea if using the functions in this format would fix the problem? (Assuming that the Filter() function would return just a few items)


 

Did you checked if you set the filter right? With a Textbox or sometinh similar?

I am using the User profile information for this, so I am filtering by a column that stores the user email.
The filter itself works fine, I checked.

WarrenBelz
Most Valuable Professional
Most Valuable Professional

Hi @lucassbp ,

I use 

RemoveIf(
   ListName,
   ID = ThisItem.ID
)

on lists and Libraries with over 20,000 items in them and it works fine. The Delegation issue only comes if the filter is not delegable (this one is) or the number of matching records you want to delete is more than your limit (and then it will only remove this number).

 

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.

poweractivate
Most Valuable Professional
Most Valuable Professional

@lucassbp 

You may want to do  it like we did in our response here Count Condition Update 

Put your filter formulas in Items property of a Gallery. Then from somewhere else you can use your Countif or Removeif or whatever you want on the Gallery then it should be no problem. Because even non-delegable functions can usually be used on Galleries with "no limit" - once they are in a Gallery, they are no longer on the data source directly *.  Moreover, operations performed on Gallery are less likely to have invisible non-delegable operations performed on them. For example, just using the regular delegable SharePoint filter on something other than a Gallery Items property, on a non-delegable limit of 1, may actually perform an under-the-hood non-delegable operation on the result, and return 1 record, and will give no warning - this may trick you into thinking Power Apps is not suitable for production - but that is not true - it actually definitely is, if you use it in that way we described.  The Gallery can be made not visible as well if it is only to get this to work correctly. Test it with a non-delegation limit of 1 to make sure it really works, like we did in above linked response.

Reason is if you do not, using even a basic filter - even one that is usually delegable - on something other than a Gallery may actually under the hood still perform another non-delegable operation - and worse yet, this will not show any warning - this only can be found by testing with the non-delegable limit of 1. 

 

Workaround to use non-delegable composed outside of a delegable function that reduces the number, and just "hope" or "guarantee" somehow that it never returns more than 2000 from the inner function, is considered the unsuitable for production method for us because at some point, the data may get higher than 2000 and there are edge cases to maintain in terms of making sure someone never goes over that number on any portion of the filter - even the filtered delegable part that is "inside" - and we would generally never resort to such a thing unless we had to, and so far, we never do, because it would impose a limit on how someone uses their database.

 

The idea always sounds tempting as it seems "easier" on surface, and one might ask - how can someone have such a high quantity of dataset that even just an inner filter returns more than 2,000 - seems so "pie in the sky" - right?  So why worry about it? Well in our case, no it is not pie in the sky. but actually we have many cases where the dataset was so huge, that in very early tests long time ago, the trick to just compose the inner function delegable and outer non-delegable actually would during the usage of the solution, fail and cause records to be missing, this is because even the number of records on the inner filter would far exceed 2000. This was even on small test cases, in production those databases are even more massive. Worse yet, this even happened with completely delegable queries, when they were not using our Gallery technique. Therefore we cannot use such technique in any production use case, it is unsuitable for us. In case you can guarantee your small production use case only, you can use it if you want, but it will impose limitation how someone uses the database. You must be sure the database cannot grow. Power Platform was not made by the Microsoft product team to be used in this limiting kind of way though is the thing.

 

Even if we were to refine and put more inner filters and say hey, let's keep using the inner workaround way and just chain more delegable filters and make the outer one non-delegable ,well even then - those would again become non functional soon enough once the dataset kept getting larger and larger, and this was even in a prototype test case - imagine our production cases then.

 

So that is why in general we only use the method we are telling you instead where we put it in the Gallery. Workaround like non-delegable composition outside of delegable - we do not like this way, but feel free to use it as long as you are sure the dataset inner filter will never, ever in future return higher than your non-delegable limit . Obviously this is something that never fits us as the dataset grows always and it will cause this to silently fail fast. If you are so sure this will never happen then feel free to use it though if you prefer it more. Only the Gallery solution we have though we consider production ready.

 

*We would make a note that Gallery displaying very high number of items, by high we mean like hundreds of thousands of records on the inner filter - even if it is not visible - is something you must test for your scenario on case by case basis - sometimes 

 

You may want to adapt from @mr-dang this way as well:

Re: 500 item limit in CDM entity search filter(need to switch to asp.net app if this exists for long 

 

By "adapt" we mean it loosely. We only mean that if there is a problem putting such high number of records in the Gallery like hundreds of thousands we mean - and even then, it may still work without breaking it up. While it may let you do it with this method of ours, it may have some performance problem when it is an extremely high number, by extremely we mean like hundreds of thousands -  breaking it up with tricks like @mr-dang  may help.

 

Oh and before using such technique of batching and splitting: see if you can just do something simpler like - make sure not to overuse our technique that much - for example to load all the items in Gallery and do all your filters from outside. Because yes, you can do that too with our method. However  if possible, please filter it down as much as you can in the Gallery Items property first with as many delegable filters and functions as possible first. So for example, instead of loading all one million records in the gallery, then after that filtering and counting from a formula outside (which is possible with our method, but less ideal in general to do in practice if you can avoid it), you should instead use as many delegable filters as you can on the Gallery Items property first directly, and only after that, use the non-delegable parts from outside. Example: Instead of Gallery one million records then Filter then Countif from outside (which is possible, but not recommended as an easily better way is possible here), try delegable Filter from inside and have the Gallery at say 100,000 records, then use the CountIf from outside on the filtered Gallery, this would much be better if possible. Try this simple kind of optimization first, only after that, to resort to something like the loose adaptation batching and splitting. 

 

However in @mr-dang post above it is for tricks to split or batch them for non-delegable. We mean "loosely adapt" because in our method, we avoid any use of non-delegable at all - the "loosely adapt" the splitting or batching tricks,  is in case there are so many in Gallery at once that you start seeing issues - we are not referring to splitting non-delegable queries in this case, we recommend avoid any non-delegable queries for production apps

 

Also, not sure if the above post is exactly the one we were thinking of, you may want to check the right post from @mr-dang , he has some great posts where he does some good tricks of splitting and batching the calls from PowerApps and he did some profiling, etc. you may want to "loosely adapt" the technique if you are talking hundreds of thousands of records from the inner filter, but only do that if you have to - try first just use our method simply with the Gallery, always put "optimization" last or not at all, premature optimization is not a good idea.

 

For your case, we think if you just follow our method without any splitting or batching, it should just work, we do not think you had such a high amount of records that you would have to go much beyond just our method for now, as our method even without any splitting or batching usually should work, even for very high number of records on the inner filter.

 

With our Gallery method you do not have to worry oh no! what if it goes above the non-delegable number and there would be no downside for most cases.

 

See if it helps the above @lucassbp 

WarrenBelz
Most Valuable Professional
Most Valuable Professional

HI @lucassbp ,

Just noting your comment, if you only want to remove ALL items belonging to a user, the real Delegation issue is that User().Email is not Delegable, so do this at App OnStart

Set(varUserMail,User().Email)

then your text column storing the user's email will be Delegable

RemoveIf(
   YourListName,
   YourEMailField=varUserMail
)

 

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

Tuesday Tip | How to Get Community Support

It's time for another Tuesday Tip, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.       This Week: All About Community Support Whether you're a seasoned community veteran or just getting started, you may need a bit of help from time to time! If you need to share feedback with the Community Engagement team about the community or are looking for ways we can assist you with user groups, events, or something else, Community Support is the place to start.   Community Support is part of every one of our communities, accessible to all our community members.   Within each community's Community Support page, you'll find three distinct areas, each with a different focus to help you when you need support from us most. Power Apps: https://powerusers.microsoft.com/t5/Community-Support/ct-p/pa_community_support Power Automate: https://powerusers.microsoft.com/t5/Community-Support/ct-p/mpa_community_support Power Pages: https://powerusers.microsoft.com/t5/Community-Support/ct-p/mpp_community_support Copilot Studio: https://powerusers.microsoft.com/t5/Community-Support/ct-p/pva_community-support   Community Support Form If you need more assistance, you can reach out to the Community Team via the Community support form. Choose the type of support you require and fill in the form accordingly. We will respond to you promptly.    Thank you for being an active part of our community. Your contributions make a difference!   Best Regards, The Community Management Team

Community Roundup: A Look Back at Our Last 10 Tuesday Tips

As we continue to grow and learn together, it's important to reflect on the valuable insights we've shared. For today's #TuesdayTip, we're excited to take a moment to look back at the last 10 tips we've shared in case you missed any or want to revisit them. Thanks for your incredible support for this series--we're so glad it was able to help so many of you navigate your community experience!   Getting Started in the Community An overview of everything you need to know about navigating the community on one page!  Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Community Ranks and YOU Have you ever wondered how your fellow community members ascend the ranks within our community? We explain everything about ranks and how to achieve points so you can climb up in the rankings! Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Powering Up Your Community Profile Your Community User Profile is how the Community knows you--so it's essential that it works the way you need it to! From changing your username to updating contact information, this Knowledge Base Article is your best resource for powering up your profile. Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Community Blogs--A Great Place to Start There's so much you'll discover in the Community Blogs, and we hope you'll check them out today!  Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Unlocking Community Achievements and Earning Badges Across the Communities, you'll see badges on users profile that recognize and reward their engagement and contributions. Check out some details on Community badges--and find out more in the detailed link at the end of the article! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Blogging in the Community Interested in blogging? Everything you need to know on writing blogs in our four communities! Get started blogging across the Power Platform communities today! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Subscriptions & Notifications We don't want you to miss a thing in the community! Read all about how to subscribe to sections of our forums and how to setup your notifications! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Getting Started with Private Messages & Macros Do you want to enhance your communication in the Community and streamline your interactions? One of the best ways to do this is to ensure you are using Private Messaging--and the ever-handy macros that are available to you as a Community member! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Community User Groups Learn everything about being part of, starting, or leading a User Group in the Power Platform Community. Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Update Your Community Profile Today! Keep your community profile up to date which is essential for staying connected and engaged with the community. Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Thank you for being an integral part of our journey.   Here's to many more Tuesday Tips as we pave the way for a brighter, more connected future! As always, watch the News & Announcements for the next set of tips, coming soon!

Hear what's next for the Power Up Program

Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp  

Tuesday Tip: Community User Groups

It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   Today's Tip: Community User Groups and YOU Being part of, starting, or leading a User Group can have many great benefits for our community members who want to learn, share, and connect with others who are interested in the Microsoft Power Platform and the low-code revolution.   When you are part of a User Group, you discover amazing connections, learn incredible things, and build your skills. Some User Groups work in the virtual space, but many meet in physical locations, meaning you have several options when it comes to building community with people who are learning and growing together!   Some of the benefits of our Community User Groups are: Network with like-minded peers and product experts, and get in front of potential employers and clients.Learn from industry experts and influencers and make your own solutions more successful.Access exclusive community space, resources, tools, and support from Microsoft.Collaborate on projects, share best practices, and empower each other. These are just a few of the reasons why our community members love their User Groups. Don't wait. Get involved with (or maybe even start) a User Group today--just follow the tips below to get started.For current or new User Group leaders, all the information you need is here: User Group Leader Get Started GuideOnce you've kicked off your User Group, find the resources you need:  Community User Group ExperienceHave questions about our Community User Groups? Let us know! We are here to help you!

Super User of the Month | Ahmed Salih

We're thrilled to announce that Ahmed Salih is our Super User of the Month for April 2024. Ahmed has been one of our most active Super Users this year--in fact, he kicked off the year in our Community with this great video reminder of why being a Super User has been so important to him!   Ahmed is the Senior Power Platform Architect at Saint Jude's Children's Research Hospital in Memphis. He's been a Super User for two seasons and is also a Microsoft MVP! He's celebrating his 3rd year being active in the Community--and he's received more than 500 kudos while authoring nearly 300 solutions. Ahmed's contributions to the Super User in Training program has been invaluable, with his most recent session with SUIT highlighting an incredible amount of best practices and tips that have helped him achieve his success.   Ahmed's infectious enthusiasm and boundless energy are a key reason why so many Community members appreciate how he brings his personality--and expertise--to every interaction. With all the solutions he provides, his willingness to help the Community learn more about Power Platform, and his sheer joy in life, we are pleased to celebrate Ahmed and all his contributions! You can find him in the Community and on LinkedIn. Congratulations, Ahmed--thank you for being a SUPER user!  

Tuesday Tip: Getting Started with Private Messages & Macros

Welcome to TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   This Week's Tip: Private Messaging & Macros in Power Apps Community   Do you want to enhance your communication in the Community and streamline your interactions? One of the best ways to do this is to ensure you are using Private Messaging--and the ever-handy macros that are available to you as a Community member!   Our Knowledge Base article about private messaging and macros is the best place to find out more. Check it out today and discover some key tips and tricks when it comes to messages and macros:   Private Messaging: Learn how to enable private messages in your community profile and ensure you’re connected with other community membersMacros Explained: Discover the convenience of macros—prewritten text snippets that save time when posting in forums or sending private messagesCreating Macros: Follow simple steps to create your own macros for efficient communication within the Power Apps CommunityUsage Guide: Understand how to apply macros in posts and private messages, enhancing your interaction with the Community For detailed instructions and more information, visit the full page in your community today:Power Apps: Enabling Private Messaging & How to Use Macros (Power Apps)Power Automate: Enabling Private Messaging & How to Use Macros (Power Automate)  Copilot Studio: Enabling Private Messaging &How to Use Macros (Copilot Studio) Power Pages: Enabling Private Messaging & How to Use Macros (Power Pages)

Top Solution Authors
Top Kudoed Authors
Users online (3,151)