cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jt0
Level: Powered On

CDS-Using conditional Logic within the datasource portion of a filter causing a non-delegatable query without the associated warning

Hi All,

 

Wanted to post this to share a learning rather than to ask a question.

 

If you have more complex filtering and sorting requirements that need nested a sorting/filtering/searching function, I have found that using conditional logic within the datasource portion of your function will cause non-delegatable query behaviour, even though the application doesn't show any delegation warnings.

 

If however you include your conditional logic before defining the nested function, then the query to CDS is as expected.

 

Sample Code with conditional logic within the datasource:

SortByColumns(
    Filter(
        If(
            FilterList = "Yes",
            Search(
                Accounts,
                TextInput1.Text,
                "name"
            ),
            Search(
                Accounts,
                TextInput1.Text,
                "accountnumber"
            )
        ),
        'Address 1: City' = "Dallas"
    ),
    "name",
    Ascending
)

In the case of the code above, the filtering and sorting will be applied on the result of the search, therefore if you are doing this on a larger dataset you will not get the correct result.

 

For example lets say the following:

In your CDS instance there are 2000 accounts containing the name "sytems" of which 250 have a address_1 city of Dallas.

 

In App settings, your data row limit for non-delegatable queries is 500, Filterlist = yes, the value of textInput1.text was "Systems" you would think it would provide a list of all  250 accounts containng the name systems in dallas right?

 

It turns out the CDS call will first run a search on name contains systems first, and then filter on the result set rather than include both filters to CDS. This means that depending on the order of your data, you may not get all 250 records from your dataset. This is despite the system not showing any delegation warnings!

 

If you tweak your code to something like this though, it will all work as expected, even though in theory the result should be the same.

SortByColumns(
    If(
        FilterList = "Yes",
        Filter(
            Search(
                Accounts,
                TextInput1.Text,
                "name"
            ),
            'Address 1: City' = "Dallas"
        ),
        Filter(
            Search(
                Accounts,
                TextInput1.Text,
                "accountnumber"
            ),
            'Address 1: City' = "Dallas"
        )
    ),
    "name",
    Ascending
)

So just a little something I have picked up and wanted to share with the community. Hopes it saves you some of the time I spent figuring this out the hard way!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: CDS-Using conditional Logic within the datasource portion of a filter causing a non-delegatable query without the associated warning

Hi @Jt0 ,

Thanks for your sharing. If you have any other questions about PowerApps, please feel free to reply here.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Rebetcha
Level 10

Re: CDS-Using conditional Logic within the datasource portion of a filter causing a non-delegatable query without the associated warning

Community Support Team
Community Support Team

Re: CDS-Using conditional Logic within the datasource portion of a filter causing a non-delegatable query without the associated warning

Hi @Jt0 ,

Thanks for your sharing. If you have any other questions about PowerApps, please feel free to reply here.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,032)