Delegation Issues

[TylerGraves]

I'm probably missing something that is simple, but im new to Powerapps and having a bit of an issue. I have looked at the delegable data sources, and I would think what I have here should all work with a SQL DB. Any help is much appriciated! 

Filter('[dbo].[Design_Request$]', If(IsBlank(Dropdown1.Selected.Value), true, Dropdown1.Selected.Value='Sales Rep') &&
If(IsBlank(Dropdown2.Selected.Value), true, Dropdown2.Selected.Value='Designer 1') &&
If(IsBlank(DatePicker1.SelectedDate), true, DatePicker1.SelectedDate='Date Created'))

I am trying to filter my gallery with two drop downs and a date picker. What I have underlined is what i'm getting a delegation warning on. Hope this is enough info, if not just let me know what else I need to include.

Thanks!

[Anonymous]

Hello Tyler,

Yes, PowerApps delegates the Filter function to SQL. The delegation warning is just that. Key to your filter working is the number of records on the SQL side. If there are more than 500, then it is possible that you will have unexpected results. If not, then the filter will work fine. You may have already reviewed this document regarding delegation:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

Does this work for you?

[TylerGraves]

The SQL table im  linked to has about 10,000 rows. I thought the 500 limit was if you were connected to a sharepoint list. 

[Anonymous]

Hello Tyler,

Yes, correct. You should have no problems if this is SQL. You may want to develop some test cases to make sure that there are no delegation issues.

[BenFetters]

Hi,

Something I've done to sort of "get around" the delegation is to put my datasource into a collection whenever simply showing the data. It doesn't show a delegation error and the app works lots faster as well.

Hope that helps!

Ben

[iAm_ManCat · Accepted solution]

Hi,

Haven't tested this out with SQL, but you could try re-write your query - if it's warning that there will be delegation issues, then there probably will be (exceptions being where you've already filtered down to a list under the delegation limit then perfor actions on the exterior of that) - I can say that using If's&IsBlank inside a filter will probably run you into some trouble in terms of delegable queries.

They way I have gotten around this is to collect an "ALL" value onto the beginning of your dropdown to have an option whereby they can choose to un-filter.

So you could create a collection of dropdown items using something like this
ClearCollect(
                    SalesRepFilterList,
                    {Value: "ALL"},
                    Choices(DataSourceName.SalesRep)
);

^ And repeat this for your other dropdown (datepicker can stay as-is)

Then set your Items property of the dropdowns to the local collections you've created, ie SalesRepFilterList

Then you can have your filter as this:

Filter(
         '[dbo].[Design_Request$]',

        (Dropdown1.Selected.Value="ALL" || 'Sales Rep' = Dropdown1.Selected.Value)
        &&
        (Dropdown2.Selected.Value="ALL" || 'Designer 1' = Dropdown2.Selected.Value)
        &&
        (DatePicker1.SelectedDate="" || 'Date Created' = DatePicker1.SelectedDate)
)

You may need to modify it slightly, but the concept is that when it checks for dropdown.selected.value="ALL", it will return true if true and not filter or else filter by what is selected - this also makes for a better UX as the user is more familiar with the concept of "ALL" than 'what Blank might do'

[TylerGraves]

I will give this a shot. Thank you!