Gallery not returning searches consistently or before timing out dependent on Office 365 details

[Anonymous]

Hi All,

On all my PowerApp I was using the forumla below to return the correct applicable details of an individual dependent on their office365 account email address comparing to the same email address in the list and the most recently created. This was working fine up until a week ago.

Now the filter on the gallery has no error messages but will not return any details or if it does can take up to 3 minutes (this is very sporadic if happens). The amount of data held in the lists are roughly 1000 items, so yes reasonably large, however this worked within 10 seconds a week ago. Furthermore, on lists which only have a few items the same issue is happening, so I don't think its down to volume of entries. I have reattached the Office365 account and re-entered the formula with no luck...

I am unsure what could have caused it. 

SortByColumns(Filter(DSE,Office365Users.MyProfile().Mail in Lower(EmployeeEmailAddress)),"Created",Descending)

Can somebody please help? This is a live system that has now broken.

Many Thanks,

Laura

[Andy_Tuke]

It sounds like you've got issues with the delegation limit.  More than 500 items in the source and you'll only ever get that many returned.  You can change the limit under App Settings\Experimental Features\Data Row Limit ...

It can go up to 2000.  Otherwise you need to write your filters in such away that the query can be delegated.  With User lookups, since they're complex data types that might not be an option.  Alternatively, you can pull the data into a local collection and then run much more complex filters there. 

Andy

Andy
Read my new blog at -
www.powerappssolution.com

[Anonymous]

Hi Andy,

Thanks for your message. Thing is I only want to return one item (there is only one relevant data for each individual) so I am only asking the gallery to return that single entry?

Like I said, what's strange is it worked fine a week ago, with the same amount of data in the list.

Thanks Andy,

Laura

[Andy_Tuke]

Its not how many you want to return, but how many its querying to find that one item.  If you have over 500, it will only check the first 500 in the data source so if the row you want isnt in that 500 then it will get missed.  I'm not sure how it chooses which 500, its not clear from the documentation, so whether its the first 500 by ID or just random I dont know

Andy
Read my new blog at -
www.powerappssolution.com

[Anonymous]

Hi Andy,

I made the changes you suggested and no improvement unfortunately.

I raised the limit up to 1500 and it still will not load.

Any other ideas?

Thank you in advance,

Laura

[Andy_Tuke]

As a quick thing to try, put a label in the app somewhere with the Text set to Office365Users.MyProfile().Mail just to confirm the form of the result is what you are expecting

Andy
Read my new blog at -
www.powerappssolution.com

[Mdorrani]

The “IN” operation is not a delegable operation if sharepoint is your data source
Yoh will have to modify your query to make sure only delegable operations are performed

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list