Remove Items from Sharepoint List with Over 2k Items

[lucassbp]

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,

[FrostedFlake]

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]

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]

@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]

@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,

[FrostedFlake]

---

@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?

[lucassbp]

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]

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]

@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 · Accepted solution]

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.