Filter only works on first 2000 items

[MichelH]

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

[Jeff_Thorpe]

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

[RezaDorrani]

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]

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?

[Drrickryp]

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]

Hi Drrickryp,

While this might be a nice workarround, I'm actually looking to get just 10 records maximum.

Problem seems to be that no matter how I do my query, it is done on the first 2000 records of the datasource.

If it would just send the query to sharepoint and get back the 10 records or so, all would be fine.

But it seems my powerapp is somehow determined to do the query itself, requesting the datasource to send everything, causing it to be limited by the 500-2000 item limit.

Michel

[RezaDorrani]

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

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.