cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RodM
Helper III
Helper III

Filter SharePoint list gallery by the 'Created' date and Person columns without delegation limit?

I have a SharePoint list with over 5k items which exceeds the delegation limits of PowerApps. I'd like to filter/search a gallery to show only a portion of the items based on 4 variables:

  1. Filter gallery based on 'Created' column; Show items from the last 120 days (Ex: StartDate: Today()-120, EndDate: Today())
  2. Use a combo box control to search a person-picker column based on the person's Display Name in Microsoft 365 (Column name : 'Employee Scored').
  3. Use a drop down control to filter distinct Display Names from the renamed Created By column (Column name: 'Evaluator')
  4. Use a drop down control to filter distinct Display Names from a people-picker column (Column name: 'Supervisor')

 

I've attempted several variations of formulas but end up with a delegation limit or empty gallery due to the known challenges with Created columns and Person/Group columns. Is anyone able to assist?

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User
Super User

Hi @RodM ,

Firstly, Created and Created By are not Delegable, so you need to "pre-filter". Person Columns will filter in a Delegable manner on DisplayName. Note you will not get warning with the below, but the top filter output (not the input which can filter any number of records) is limited to your Delegation Limit.

With(
   {
      wList:
      Filter(
         YourListName,
         'Employee Scored'.DisplayName = YourEmployeeComboBox.Selected.???? &&
         Supervisor.DisplayName = YourSupervisorComboBox.Selected.???? 
      ),
      wDate:
      DateAdd(
      Today,
         -120,
         Days
      )
   },
   Filter(
      wList,
      Created > wDate &&
      'Created By'.DisplayName = YourEvaluatorComboBox.Selected.????
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

View solution in original post

9 REPLIES 9
WarrenBelz
Super User
Super User

Hi @RodM ,

Firstly, Created and Created By are not Delegable, so you need to "pre-filter". Person Columns will filter in a Delegable manner on DisplayName. Note you will not get warning with the below, but the top filter output (not the input which can filter any number of records) is limited to your Delegation Limit.

With(
   {
      wList:
      Filter(
         YourListName,
         'Employee Scored'.DisplayName = YourEmployeeComboBox.Selected.???? &&
         Supervisor.DisplayName = YourSupervisorComboBox.Selected.???? 
      ),
      wDate:
      DateAdd(
      Today,
         -120,
         Days
      )
   },
   Filter(
      wList,
      Created > wDate &&
      'Created By'.DisplayName = YourEvaluatorComboBox.Selected.????
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

RodM
Helper III
Helper III

Thank you! This worked with a minor change to the missing parenthesis from the DateAdd function. However, I have "All" options as part of a Collection. I'm not sure how to work out the logical functions. For simplicity I changed to 3 dropdown controls. Are you able to assist with the logical functions?

Sort(With(
   {
      wList:
      Filter(
         'QA-Call Monitoring Scores',
         'Employee Scored'.DisplayName = EmployeeDropdown.Selected.Result &&
         'Assigned Supervisor'.DisplayName = SupervisorDropdown.Selected.Result
      ),
      wDate:
      DateAdd(
      Today(),
            -120,
            Days
      )
   },
   Filter(
      wList,
      Created > wDate &&
      Evaluator.DisplayName = EvaluatorDropdown.Selected.Result
   )
),Created,Descending)

 

Hi @RodM ,

Do you mean you have "All" as one of the options of the drop-downs ?

With(
   {
      wList:
      Filter(
         YourListName,
         (
            EmployeeDropdown.Selected.Result = "All" ||   
            'Employee Scored'.DisplayName = EmployeeDropdown.Selected.Result
         ) &&
         (
            SupervisorDropdown.Selected.Result = "All" ||
            Supervisor.DisplayName = SupervisorDropdown.Selected.Result 
         )
      ),
      wDate:
      DateAdd(
      Today,
         -120,
         Days
      )
   },
   Filter(
      wList,
      Created > wDate &&
      'Created By'.DisplayName = EvaluatorDropdown.Selected.Result
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Thank you for trying to assist again, but this is returning a blank gallery unless the Supervisor and Evaluator dropdowns have a selected value other than "All".


Hi @RodM ,

There is something else wrong here - the code is valid (I use it all the time) and should either filter on the drop-down, or if "All" selected, show all records (filtered only by the other filters)

With(
   {
      wList:
      Filter(
          'QA-Call Monitoring Scores',
         (
            EmployeeDropdownOld.Selected.Result = "All" ||   
            'Employee Scored'.DisplayName = EmployeeDropdownOld.Selected.Result
         ) &&
         (
            SupervisorDropdownOld.Selected.Result = "All" ||
             'Assigned Supervisor'.DisplayName = SupervisorDropdownOld.Selected.Result 
         )
      ),
      wDate:
      DateAdd(
      Today(),
         -120,
         Days
      )
   },
   Filter(
      wList,
      Created > wDate &&
      Evaluator.DisplayName = EvaluatorDropdownOld.Selected.Result
   )
)

 

I created new dropdown names and a new gallery to test it. The only change was to correct the Today function which was missing the parenthesis: Today ().

 

Same result, the gallery returns blank unless the Supervisor and Evaluator dropdowns are filled, or the Employee and Evaluator dropdowns are filled. All 3 set to "All" returns a blank gallery, along with Employee+Supervisor or any other combination.

Here is the formula for one of the dropdown collections:

ClearCollect(CollectEmployee, {Result: "All"});
Collect(CollectEmployee, Sort(Distinct(With(
    {
        StartDate: Today()-120,
        EndDate: Today()
    },
    Filter('QA-Call Monitoring Scores','Call Recording Date' > StartDate,'Call Recording Date' <= EndDate)
),'Employee Scored'.DisplayName),Result,Ascending));

 

What do you think could be the problem?

Hi @RodM ,

Sorry, but makes no sense to me- try the filters on their own

Filter(
   'QA-Call Monitoring Scores',
   (
      EmployeeDropdownOld.Selected.Result = "All" ||   
      'Employee Scored'.DisplayName = EmployeeDropdownOld.Selected.Result
   ) &&
   (
      SupervisorDropdownOld.Selected.Result = "All" ||
      'Assigned Supervisor'.DisplayName = SupervisorDropdownOld.Selected.Result 
   )
)

 

I tried a different direction. I replaced my dropdowns with combo boxes, then used this formula on my gallery:

SortByColumns(Filter('QA-Call Monitoring Scores',
  ('Assigned Supervisor'.DisplayName = SupervisorDropdownOld.Selected.Result || IsBlank(SupervisorDropdownOld.Selected)) &&
  ('Employee Scored'.DisplayName = EmployeeDropdownOld.Selected.Result || IsBlank(EmployeeDropdownOld.Selected)) &&
  (Evaluator.DisplayName = EvaluatorDropdownOld.Selected.Result || IsBlank(EvaluatorDropdownOld.Selected))
),"CallRecordingDate",Descending)

 

And for my combo box formulas, eliminated the collections, and just placed something like this in all 3:

Sort(Distinct(With(
    {
        StartDate: Today()-120,
        EndDate: Today()
    },
    Filter('QA-Call Monitoring Scores','Call Recording Date' > StartDate,'Call Recording Date' <= EndDate)
),'Employee Scored'.DisplayName),Result,Ascending)

 

This is working, and I honestly have no clue why I have no delegation warnings, and I am (apparently) able to see my full dataset which is over 6k records. Why am I doubting myself here? Do you see anything wrong with this different approach? And why am I not hitting a delegation wall?

Hi @RodM ,

Two reasons - the code I supplied (which I was puzzled why it was not working)  gets rid of the If() statements inside a filter (which are not Delegable) and breaks the whole thing down into two Delegable filters. The "All" statement sets are a true/false test, so if the first is true, nothing happens and the second bit (Filter) does not execute and the reverse if not true (it filters).

The With() statement can be used for many things and is effectively setting a (very) temporary variable BEFORE you enter the filter, which is delegable when you compare a value with a Variable. I have a blog on this (as well as one on Delegation) that may interest you.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,127)