cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate I
Advocate I

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
Highlighted
Community Support
Community Support

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.

With IF partWith IF part   Without IF partWithout 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
Highlighted
Community Support
Community Support

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.

With IF partWith IF part   Without IF partWithout 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

Highlighted
Advocate I
Advocate I

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.

Highlighted
Advocate I
Advocate I

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.

Highlighted
Community Support
Community Support

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
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Top Kudoed Authors
Users online (7,383)