cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichelH
Level 8

Filter only works on first 2000 items

For an edit form in a list I'm trying to replace an infopad form by a powerapp form.

All is fine except that I'm missing records in the query result. 

It is all quite simple, I just need the records where the 'Applicant' person matches the person in a combo box.

So in the app OnStart I say this:

Collect(
    oldlst,
    Filter(
        OldListDS,
        Applicant.Email = Email
    )
);

Where Email is the Trim( User().Email ) .

Nothing complicated, and I expected 2 records in the returned collection, however I only get one.

I believe this is because somehow the query is done by powerapps, rather than just asking the right items from sharepoint, it gets all the stuff I don't need, and then drops 99,9%.

 

The list has about 2300 entries and I'm sure my second record is in the last 300, not in the 2000 records that powerapp happily pulls down.

In other words it is somehow not delegating the query to sharepoint.

How can I stop powerapps from doing this?

What can I do to make it get just the handful of records I need.

I read that 'complicated' queries can't be delegated but I have not yet seen any limits related to 'person' type of variables.

 

Replacing the Email by a few other peoples email give the number of records based on the number of entries in the oldest 2000 records.

Since I'm mainly interested in the last record, getting the last 2000 records, instead of the first, would also solve my issue.

 

Thanks for any enlightenment.

 

Michel

1 ACCEPTED SOLUTION

Accepted Solutions
MichelH
Level 8

Re: Filter only works on first 2000 items

I figured out what caused my issue. (It was me)

 

My company has changed name and I had to reconnect several apps to the new datasource.

Since I cannot control the name of the datasource, this caused quite a bit of editing.

 

I thought I could put the name of the datasource in a variable but ofcourse, all I do is put a table in a variable with the first 500-2000 records of the datasource.

 

It is not because I do not do a query, it won't do a query. (select * from)

 

Besides that, in the inital situation, the query on a person type of variable could not be done in a delegated way (by sharepoint), but this is solved in the 14/8 update of powerapps.

 

Thanks for all comments and feedback.

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Filter only works on first 2000 items

When your cursor is in the formula box do you see a blue squiggly line? This would indicate what piece of the formula isn't delegable. This week they release a change that made PersonField.Email and PersonField.DisplayName delegable in SharePoint. They also made some other complex field types in SharePoint delegable. 

 

If OldListDS is a SharePoint list, Applicant.Email is a person field in SharePoint and Email is a variable with the email address then you shouldn't have a delegation issue on the SharePoint side.

 

Here is a link to the delegation improvements that were released this week.

SharePoint delegation improvements



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.
Dual Super User
Dual Super User

Re: Filter only works on first 2000 items

Hi @MichelH 

 

Collect(
    oldlst,
    Filter(
        OldListDS,
        Applicant.Email = Email
    )
);

Your query is delegable as now Person fields have been included as a delegable operation as pointed out by @Jeff_Thorpe 

 

But since you are using a collection the data will be stored locally in the PowerApp

Collections will only store upto 2000 records

As an example, if your query returns 2500 items, then collection will ony hold 2000 (or the delegable limit set on the App)

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

MichelH
Level 8

Re: Filter only works on first 2000 items

Thank you Jeff and Reza,

 

Great to know that the person type variable is now at last delegatable, so it should not be an issue anymore.

I think if I put a delegatable query in the code, it will always call back to the sharepoint when I clear and collect the collection ?  Or only in the Onstart?

 

For now I can see that for some people who have many entries in the list ( 6-8 ), the result varies when I adjust the limit for non-delegatable queries (from 2000 to 500).

This tells me my query is still not treated as a delegatable query.

 

At the same time I do not see anything blue in the editor to indicate a delegation limit warning.

So I cannot make it show the latest records.

 

I use collections so it would not need to do the query again when I try to determine the last entry, but I do not expect this to affect delegation in any way.

 

I've just tried to use a normal variable to store the resulting table:

Set(varRequester , DataCardValue18.Selected.Email);  

Set(list1,    
    Filter(
        OldListDS,
        Applicant.Email = varRequester  
    )
);

Set( RecCount , CountRows( list1 ) ); 

but the CountRows result still depends on the item limit for non-delegatable queries.

 

Basically when I see the ID of the item getting > 2000 then that record is not in the returned table

 

Clues?

 

 

Super User
Super User

Re: Filter only works on first 2000 items

Hi @MichelH 

@RezaDorrani  is not exactly correct that a collection can hold only 2000 items. The Collect() function is not delegatable but by combining two 2k collections you can have a collection with all of the records.  You can test this by using CountRows on the combined collection.  For example one way to solve your problem is to create two collections and then combine them as follows.  

Collect(first2k, YourList); Collect (last2k, Sort(Yourlist, Descending) and then combine them as 
Collect(all4k, first2k, last2k)

This will create a collection of 4k items but with duplicates.  you can derive a single column list of unique email addresses using

Distinct(all4k, email)

which will result in a single column called Result that contains all of your email addresses. The reason this works is that once created, collections are not subject to delegation issues.  Distinct() is not a delegatable function so  you can't do it on the original list.  

Another way to do it and to keep all the data in the final collection without duplicates is as follows (by @CarlosFigueira , @KroonOfficeSol , @martinav and myself)

 

ClearCollect(all4k,  first2k, Filter(last2k, Not(ID in first2k.ID))

Not() and "in" are not delegatable but since you are working with collections and not original datasources, they work in the above formula just fine.

MichelH
Level 8

Re: Filter only works on first 2000 items

Hi 

 

 

 

Highlighted
Dual Super User
Dual Super User

Re: Filter only works on first 2000 items

Hi @MichelH 

 

@Drrickryp is right

Sorry for the confusion, what I meant to say was that for each Collect call PowerApps collection can hold upto 2000 records

But yes, if you keep doing Collect then it can hold n number of records within the same collection

 

Also,

Set(varRequester , DataCardValue18.Selected.Email);  

Set(list1,    
    Filter(
        OldListDS,
        Applicant.Email = varRequester  
    )
);

Set( RecCount , CountRows( list1 ) ); 

 

You mentioned it keeps quering only on first 2000 records

So this Filter( OldListDS, Applicant.Email = varRequester ) is quering just the first 2k records in SharePoint?

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

MichelH
Level 8

Re: Filter only works on first 2000 items

I figured out what caused my issue. (It was me)

 

My company has changed name and I had to reconnect several apps to the new datasource.

Since I cannot control the name of the datasource, this caused quite a bit of editing.

 

I thought I could put the name of the datasource in a variable but ofcourse, all I do is put a table in a variable with the first 500-2000 records of the datasource.

 

It is not because I do not do a query, it won't do a query. (select * from)

 

Besides that, in the inital situation, the query on a person type of variable could not be done in a delegated way (by sharepoint), but this is solved in the 14/8 update of powerapps.

 

Thanks for all comments and feedback.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 292 members 5,792 guests
Please welcome our newest community members: