Filter not working on collection from SharePoint.

[Anonymous]

Hi community,

I am getting the result for a html while filtering a table that has been imported from an local excel file, but this is not working for the collection that I imported from a SharePoint list.   Please check the attachments.

Note:  Table5 is the excel table and MyCol is the collection from SharePoint list.

I am using Collect(Mycol, SabysachiMohanty_Test) for the collection.

Please help with this issue.

[v-xida-msft]

Hi @Anonymous ,

Currently, the 'in' operator could not be delegated in SharePoint Connector. Please check the following article for more details:

https://docs.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-operations-for-sharepoint

If you use 'in' operator in your Filter function, it could not delegate the data process to your SP List itself, instead, it could only process data locally in your app. You could only process 2000 records locally at most in your canvas app.

In addition, when saving your SP List records into the collection, there is also an known limit -- you could only save 2000 records at most from other cloud service into a collection once time.

If you want to load more than 5000 items into a collection in your canvas app, I think the integration of Power Automate flow could achieve your needs.

Please check and see if the following video could help in your scenario:

https://www.youtube.com/watch?v=2M0zCyu__20

Best regards,

[WarrenBelz]

Thanks @v-xida-msft ,

Pretty much what I said - you can collect more than 5000 with the workaround on the shadow ID, but it is a lot of effort unless you really need it.

[Anonymous]

Hi @WarrenBelz, 

I would like to first thank you for your time so far.  This is the first app I am creating using the powerApp and I am not aware of all the functionality of the app.  What I was able to understand so far is that updateif helps to update a SP list from a form.  Is it possible for you to elaborate a little on the steps please, like what to create first and where to use which function.  

[WarrenBelz]

Hi @Anonymous ,

If this is your first app, you are taking on a complex task that probably requires a bit more understanding. The UpdateIf or Patch (they both do the same job in this case) is adding the ID number just created for a new record to the numeric field you need to do the collection of more than 2000 items. I cannot elaborate much more on the rest of it than I did in the previous post unless you have a specific question. 

Have you considered my other comments on filtering by other means?

[Anonymous]

Hi @WarrenBelz,

I understood that updateIf is being used to create a new column in the SP list that go with a button.  I am not able to get what I have to do with the form here.

[WarrenBelz]

Hi @Anonymous ,

UpdateIf adds data to an existing column - there is no code that can add a column to a SharePoint list.

This also is nothing to do with a Form - this is preparation to be able to do a big collection by populating a numeric field matching the ID. As I mentioned, I was reluctant to introduce this as it is another process before you even attempt your in filter.

The issue of Delegation (or lack of it) is driving this workaround.

[Anonymous]

Hi @WarrenBelz,

I am still confused with this function.  

UpdateIf(
    MyListName,
    ID = YourNewFormName.LastSubmit.ID,
    {IDRef: YourNewFormName.LastSubmit.ID}
)

What I can use in the place of MyListName and YourNewFromName?  

[WarrenBelz]

Hi @Anonymous ,

MyListName is the name of your SharePoint List you are putting the value in the field - looking back to the start, I think it is Table5.

YourNewFormName is the name of the form you use to add new records (you have not told me what this is).

Seriously, the fact you are asking these questions indicates to me that at this stage of your Power Apps journey (and I was there two years ago), you are going to struggle with this process. Have you considered that other options I mentioned including getting a smaller data set with another text or numeric value and then using your filter on this?

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.

[Anonymous]

Hi @WarrenBelz ,

I really appreciate your effort to help me in this issue.  Thanks a lot for that.

Here, we have some confusion.  I have not used any form in the app so far to add records.  The SharePoint list was created from an excel file that will be periodically updated.

As mentioned earlier clarification post (No.4 point), I was able to bring a whole excel table (which has more than 5000 rows) from a local excel file and do this filter.  But this app is going to be shared among users in the organization, so we thought of, instead of taking the table from a local excel file, if we can use the table from the SharePoint list, it will have a better accessibility.  

Please let us know your thoughts regarding this.

[WarrenBelz]

Hi @Anonymous ,

Firstly, you just need to back-populate the existing records with the ID value in a numeric field you create. Then if you are creating new records, populate them s well - is this what you are asking?

Excel is never good at anything over 2000 records in a query, so SharePoint is a better option.

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.