Delegation warning for sharepoint online list with only Filter and Sort

[E_Vigors]

Hi All,

I have a delegation issue I do not understand as the functions I believe are all delegatable.

below is the value of the items for a gallery. 

Sort(Sort(Filter('HSE - Cov_EPC_Obs_tbl','Obs Type'= "EPC" && Report_Month = EPC_Obs_Month_dd.Selected.Result),Date,Descending),'Obs Category',Ascending)

HSE - Cov_EPC_Obs_tbl   ---> is a SharePoint online list

Report_Month --------------> is calculated text field equal to Text(Date,"mmm yy") where date is a date in date format. 

EPC_Obs_Month_dd --------> is a drop-down located on the same page referencing the Report_Month column of HSE - Cov_EPC_Obs_tbl, its items value is Distinct(Sort('HSE - Cov_EPC_Obs_tbl',Date,Descending),Report_Month)

I would be keen to understand why this is an issue, the exact same structure is working happily on another text field. 

An alternate solution would be a better way to generate a drop-down with specific months for the data set and allowing a gallery to return the selected month.

[WarrenBelz · Accepted solution]

Hi @E_Vigors ,

Your issue is your Calculated SharePoint field - they are not Delegable (among other issues they will cause). Try this - as long as the top filter results in less records than your Delegation limit or the newest (limit) records will do the job, this should work.

With(
   {
      wList:
      Filter(
         Sort(
            'HSE - Cov_EPC_Obs_tbl',
            ID,
            Descending
         ),
         'Obs Type'= "EPC"
      )
   },
   Sort(
      Sort(
         Filter(	  
            AddColumns(
               wList,       		  
               "ReportMonth",
               Report_Month
            ),
            ReportMonth = EPC_Obs_Month_dd.Selected.Result
         ),
         Date,
         Descending
      ),
      'Obs Category'
   )
)

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.

[GarethPrisk]

You will likely need to step through the function, given how its nested, to see what exactly is throwing the delegation warning. As you noted, the individual functions (Sort, Filter) are delegable, but necessarily how they are written and used.

https://docs.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-ope...

You can extract out the Filter portion and see if its there, and then layer back the Sorts. My guess is either that the Filter doesn't like joining clauses with an ampersand (&&) - you could have multiple criteria, as params, which is a supported way to evaluate an And() filter scenario.

Alternatively, you may have an issue referencing the EPC_Obs_Month_dd.Selected.Result explicitly. This could be set into a variable - something like UpdateContext({locSelectedDate: Self.Selected.Result}) for the OnChange of EPC_Obs_Month_dd. Some data sources will not accept references for delegation.

SharePoint isn't my specialty, but hopefully this helps.

[E_Vigors]

Hi The solution was useful, however, thank you for pointing out it was the calculated column that was causing the issue, I actually ended up continuing to use the calculated Reporting Month in the drop-down but reformating the text Reporting Month to date for the filter and using it on my DATE column when all entries were known to be on a specific date. In other cases, I move the calculation of the Reporting month string to the power app where the data is written rather than having it as separate step by SPLists