Answered

Gallery filter not returning all results, delegation issue?

(0) Share

Report

Posted on by jatsson

Hi, I have created a Powerapps canvas app that uses a SharePoint list with about 1500 records as its database. In the gallery I want to show records that only belongs to the logged in user/coworker. The below code works fine for all users if they choose anything but "All except done" in the dropdown filter. For some users the "All except done" works also but others get no result, I believe this is because of the delegation warning that I get in Powerapps studio. How to fix this?

Sort(
 Search(
 If(
 DropdownRingStatus.Selected.Result = "All except done";
 Filter(
 'SP list';
 'Coworker email' = CurrentUser.Email And Ringstatus.Value<>"Done"
 );
 DropdownRingStatus.Selected.Result <> "All except done";
 Filter(
 'SP list';
 'Coworker email' = CurrentUser.Email And Ringstatus.Value = 
 DropdownRingStatus.Selected.Result
 )
 );
 TextSearchBox1.Text;
 "Title";
 "Lastnamn"
 );
 Title;
 If(
 SortDescending1;
 Descending;
 Ascending)
)

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@jatsson

Your list is still under the maximum record limit, so you could increase your maximum limit to 2000.

However, your current limit is showing you that the <> is not delegable.

Consider the following formula to prefilter your data and then perform the no delegable criteria on the results.

With({_prefilter: Filter('SP list'; 'Coworker email' = CurrentUser.Email)}, Sort( Search( Filter(_prefilter, (!(DropdownRingStatus.Selected.Result = "All except done") || Ringstatus.Value = DropdownRingStatus.Selected.Result) && (!(DropdownRingStatus.Selected.Result = "All except done") || ((DropdownRingStatus.Selected.Result = "All except done") && Ringstatus.Value<>"Done")) ); TextSearchBox1.Text; "Title"; "Lastnamn" ); Title; If(SortDescending1; Descending; Ascending) ) )

I hope this is helpful for you.
Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @jatsson

I tried to simplify the formula a bit here. I tried to prefilter and sort the list and then apply the condition. Finally I did a Search on the final sorted and filtered list. See if it works. If you have set the record limit to 2k in advanced settings, it should work with a list of 1500 records.

With({DRSR:DropdownRingStatus.Selected.Result; wprefltrd:Sort( Filter( 'SP list';'Coworker email' = CurrentUser.Email); Title; If( SortDescending1; Descending;Ascending ) ) ) }, With({filtrd: If( DRSR <> "All except done"; Filter( wprefltrd; Ringstatus.Value = DRSR ); Filter( wprefltrd;Ringstatus.Value="Done" ) ) }; Search( filtrd; TextSearchBox1.Text; "Title";"Lastnamn" ) ) )
jatsson 6 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi Randy, thank you for your reply. I am not able to get it to work after adding your adjustments. Could it be that I have the following code in the screens Onvisible field?
ClearCollect(collectStatus; {Result: "All except done"});; Collect(collectStatus; Distinct('SP list';Ringstatus.Value))
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@jatsson

No, but you don't really need all of that for your dropdown.

Sorry I think I made a typo in the formula, should be this:

With({_prefilter: Filter('SP list'; 'Coworker email' = CurrentUser.Email)}, Sort( Search( Filter(_prefilter, ((DropdownRingStatus.Selected.Result = "All except done") || Ringstatus.Value = DropdownRingStatus.Selected.Result) && ((DropdownRingStatus.Selected.Result = "All except done") && Ringstatus.Value<>"Done") ); TextSearchBox1.Text; "Title"; "Lastnamn" ); Title; If(SortDescending1; Descending; Ascending) ) )

See where that leads you.