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

Hitting Non-Delegable query limit when I think I'm using delegable functions

Using the Common Data Service and PowerApps, I have built an Item Ordering App and have just today realized a critical part of the app is not retrieving all the records. I have confirmed it is hitting the non-delegable query limit; I increased the limit and it showed the missing records.

 

The user selects a company from the SupplierGallery, this in theory should load the items available from that company:

 

 

SortByColumns(
    Search(
        If(AllSupplierCheckbox2.Value = true, 
            'Supplier Parts', 
            Filter('Supplier Parts', cr546_Supplier.cr546_suppliername = 'Select Supplier Gallery'.Selected.'Supplier Name')
        ),
        PartsSearchBox.Text, "cr546_supplierpartnumber", "cr546_partdescription", "companypartnumber"
    )
    , "cr546_supplierpartnumber")

 

 

 
SortByColumns, Search, and Filter are all delegable when used with CDS. So my assumption is that the One-to-Many call is my issue. It seems like this comment confirms it: "The service handles the filtering for the One-to-Many so there is no delegation concern there. The resulting table is pageable, so any gallery over the result can be infinitely scrolled. However, we don't yet support delegation of filter or sort for the One-to-Many table." http://disq.us/p/1x8dpzh

Am I understanding this correctly? And does anyone have a clean workaround for this?

1 ACCEPTED SOLUTION

Accepted Solutions
v-siky-msft
Level 10

Re: Hitting Non-Delegable query limit when I think I'm using delegable functions

Hi @DevinFlickinger ,

 

Is there any delegation warning in your formulas? 

I have made a test on my side, and can reproduce your issue.  I find this issue is due to If function, If the If part is in the formula, the gallery will only show one item(I have set the non-delegable query limit to 1); If delete the If part in the formula, the gallery will show all filtered items, and it become delegable.

This is quite weird, because there seem to be no delegable warning.

Snipaste_2019-11-08_16-51-41.pngWith IF part   Snipaste_2019-11-08_16-56-58.pngWithout IF part

However, I find a workaround for my test, and hope this can help for you.

Just move If part function to the beginning of the formula, first to judge if the check box is checked. So modify your formula as below:

If(AllSupplierCheckbox2.Value = true, 
SortByColumns(
    Search(
            'Supplier Parts',
        PartsSearchBox.Text, "cr546_supplierpartnumber", "cr546_partdescription", "companypartnumber"
    )
    , "cr546_supplierpartnumber"),
SortByColumns(
    Search(
            Filter('Supplier Parts', cr546_Supplier.cr546_suppliername = 'Select Supplier Gallery'.Selected.'Supplier Name'),
        PartsSearchBox.Text, "cr546_supplierpartnumber", "cr546_partdescription", "companypartnumber"
    )
    , "cr546_supplierpartnumber")
)

 Best regards,

Sik

If my post is helpful for you, please click on “Accept as Solution” to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-siky-msft
Level 10

Re: Hitting Non-Delegable query limit when I think I'm using delegable functions

Hi @DevinFlickinger ,

 

Is there any delegation warning in your formulas? 

I have made a test on my side, and can reproduce your issue.  I find this issue is due to If function, If the If part is in the formula, the gallery will only show one item(I have set the non-delegable query limit to 1); If delete the If part in the formula, the gallery will show all filtered items, and it become delegable.

This is quite weird, because there seem to be no delegable warning.

Snipaste_2019-11-08_16-51-41.pngWith IF part   Snipaste_2019-11-08_16-56-58.pngWithout IF part

However, I find a workaround for my test, and hope this can help for you.

Just move If part function to the beginning of the formula, first to judge if the check box is checked. So modify your formula as below:

If(AllSupplierCheckbox2.Value = true, 
SortByColumns(
    Search(
            'Supplier Parts',
        PartsSearchBox.Text, "cr546_supplierpartnumber", "cr546_partdescription", "companypartnumber"
    )
    , "cr546_supplierpartnumber"),
SortByColumns(
    Search(
            Filter('Supplier Parts', cr546_Supplier.cr546_suppliername = 'Select Supplier Gallery'.Selected.'Supplier Name'),
        PartsSearchBox.Text, "cr546_supplierpartnumber", "cr546_partdescription", "companypartnumber"
    )
    , "cr546_supplierpartnumber")
)

 Best regards,

Sik

If my post is helpful for you, please click on “Accept as Solution” to help other members find it more quickly.

View solution in original post

DevinFlickinger
Level: Powered On

Re: Hitting Non-Delegable query limit when I think I'm using delegable functions

@v-siky-msftThat did the trick. Thank you so much for the quick response and work around! It didn't even occur to me that it could be the conditional causing the problem.

DevinFlickinger
Level: Powered On

Re: Hitting Non-Delegable query limit when I think I'm using delegable functions

Oh and there was ZERO delegation warning in the formula. I only discovered while I was giving a user a walk through.

@v-siky-msft Is setting the non-delegable limit to 1 considered standard practice in production? It seems like there's very little downside to doing so if you are building your calls to all be delegable.

v-siky-msft
Level 10

Re: Hitting Non-Delegable query limit when I think I'm using delegable functions

Hi @DevinFlickinger ,

 

Actually, you can do it if your want all formulas is delegable. And it also very helpful to check if your function call is delegable, this is why I like to use it.

Best regards,

Sik

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

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