Delegation warning when filtering gallery on AAD dropdown user ID

[pa111]

Hello

I have created a gallery (GalleryRequestList) in a canvas app that shows a list of records from a Dataverse table (RequestList). The table contains a Lookup column so that rows can be assigned to users based on their Active Directory user ID. This lookup column in the RequestList table is called AssignedTo and references the AAD User table.

There are several sorting and filtering criteria that must be applied to the gallery. The high-level code for the Items property of the gallery is below.

The issue I have is the presence of the in, Or, and || functions triggers a delegation warning.

How can I adjust this code so that the gallery can be filtered by (distinct) AAD user ID in a delegable function?

ddAssignedTo (AAD User ID dropdown) [Items property]:

Distinct(RequestList,AssignedTo.'Display Name')

GalleryRequestList [Items property]:

SortByColumns(
Filter(
Search(
RequestList,
InpSearchBar.Text,
"column1",
"column2"
),
And(
        FaxReceived>DateTimeValue(Month(DatePickerFrom.SelectedDate) & "/" & Day(DatePickerFrom.SelectedDate) & "/" & Year(DatePickerFrom.SelectedDate) &" " & DropdownReceivedFromHours.SelectedText.Value & ":"& DropdownReceivedFromMinutes.SelectedText.Value,"en-AU"),  
        FaxReceived<DateTimeValue(Month(DatePickerTo.SelectedDate) & "/" & Day(DatePickerTo.SelectedDate) & "/" & Year(DatePickerTo.SelectedDate) &" " & DropdownReceivedToHours.SelectedText.Value & ":"& DropdownReceivedToMinutes.SelectedText.Value,"en-AU")
    ),
AssignedTo.'Display Name' in ddAssignedTo.SelectedText.Value Or IsBlank(ddAssignedTo.SelectedText.Value) || IsEmpty(ddAssignedTo.SelectedText.Value)
),
"cr000_receiveddate",If(
        ctxSortDescending,
        Ascending,
        Descending)
)

[365-Assist]

The way I like to remove these delegation warnings is by creating a Collection (In the App -> OnStart) for the data to be stored in. Then filter on the Collection (Rather than directly reading from the Data Source).

--------------------------------------------------
Please Accept as Solution if it solves your question. Or just give it a Thumbs Up if it is helpful as can help others.

Subscribe: https://www.youtube.com/channel/UCFpvUlpx84FuIPOdInGKMTw
Twitter: https://twitter.com/assist_365

Regards
Darren Lutchner - 365 Assist

[WarrenBelz · Accepted solution]

Hi @pa111 ,

You have Delegation issues all through that code, but to answer your first question, the below will work if the middle Filter (wList) which is Delegable in itself returns output under your Delegation limit (the list can be any size). NOTE - I have also assumed here that the display name will equal the dropdown value (you need = , not in)

With(
   {
      wFrom:
      DateTimeValue(
         Month(DatePickerFrom.SelectedDate) & "/" & 
         Day(DatePickerFrom.SelectedDate) & "/" & 
         Year(DatePickerFrom.SelectedDate) & " " & 
         DropdownReceivedFromHours.Selected.Value & ":" & 
         DropdownReceivedFromMinutes.Selected.Value,
		 "en-AU"
      ), 
	  wTo:
	  DateTimeValue(
         Month(DatePickerTo.SelectedDate) & "/" & 
         Day(DatePickerTo.SelectedDate) & "/" & 
         Year(DatePickerTo.SelectedDate) & " " & 
         DropdownReceivedToHours.Selected.Value & ":" & 
         DropdownReceivedToMinutes.Selected.Value,
         "en-AU"
      )
   },
   With(
      {
         wList:
         Filter(
            RequestList,
            (
               Len(ddAssignedTo.Selected.Value) = 0 || 
               AssignedTo.'Display Name' = ddAssignedTo.Selected.Value
            ) &&
            FaxReceived > wFrom && FaxReceived < wTo
         )
      },
      SortByColumns(
	     Search(
            wList,
            InpSearchBar.Text,
            "column1",
            "column2"
         ),
         "cr000_receiveddate",
         If(
            ctxSortDescending,
            Ascending,
            Descending
         )
      )
   )
)

To your next question, the filter is not an issue, but rather getting all the items into the drop-down as Distinct() is not Delegable. How many items are in the list ?

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

[WarrenBelz]

Hi @pa111 ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

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

[pa111]

Thanks for your help with this!