I have gone through lot of blogs on this but nothing seems to be suited for my requirement, I have 30000+ records in my SQL table SOX_ISP. I want filter my gallery items on the basis of three selection (User().Email, Status dropDown and Resource list dropDown controls). but in Resource List dropdown I'm not able to receive resource names which occurs after 2000 in the SQL table. (attached screenshot for the same), can anyone please help.
Resource List DD: Sort(Distinct(Filter(SOX_ISP , LINE_MANAGER_EMAIL = User().Email),REGISTRATION_NAME),Result)
Gallery: Filter (SOX_ISP,REQUEST_STATUS = Dropdown1_9.SelectedText.Value && REGISTRATION_NAME = Dropdown2_4.SelectedText.Value)
I'm not sure if this will fix all of your issues, but you need to replace the user().Email in the Filter with a Variable. Functions like User().Email are not delegable, but the value generated by them is. You can use the following in the OnStart and then use the variable in your filter.
That may not solve your entire problem because Distinct() also isn't delegable. If changing the User().Email doesn't filter things below the data row limit you may have to use Power Automate to use a Stored Procedure to get the distinct resources and return them.
I don't know if this helps but can you create a View of distinct USERS and use the View in your equation.
CREATE VIEW [dbo].[LineManagerEmailV] AS SELECT DISTINCT LINE_MANAGER_EMAIL FROM dbo.SOX_ISP GO
That is a good solution to the problem of Distinct() not being delegable. Then use the currentUserEmail variable to make the filter of that view Delegable.
Check out new user group experience and if you are a leader please create your group
Check out how to claim yours today!
Test your skills now with the Cloud Skill Challenge.
We are excited to announce that Demo Extravaganza for 2021 has started!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks