cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BigE
Helper II
Helper II

Delegation on filtered patch to SharePoint list

I'm running into a delegation warning on some of my submits. Here is my formula for a single checkbox:

Patch(Datasource, First(Filter(Datasource, ID=varEditItem)), {checkbox:true}));

 

The above works and patches without any issues but I'm concerned about the delegation. I tried using LookUp instead and although I don't have a delegation warning, nothing submits to the datasource. I get not errors, just nothing happens.

Patch(Datasource, LookUp(Datasource, ID=varEditItem), {checkbox:true}))

 

Is there another way to make this work? I have a few of these popping up all over the place and would love to see them gone.

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @BigE ,

Is your data source a SP List?

 

If your data source is a SP List, I think the formula you provided should work without any Delegation issue. The LookUp function could be delegated with the SP List, and the ID field could not delegated with '=' operator.

Please check the following article for more details:

https://docs.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-ope...

 

According to the LookUp formula that you mentioned, I found that you have a extra ')' at the end of your formula, please remove it, and modify it as below:

Patch(
       Datasource, 
       LookUp(Datasource, ID = varEditItem), 
       {
         checkbox: true
       }
)               // do not add extra ')' close bracket here

 

Please make sure the varEditItem variable is populated with proper Item ID value in your app. In addition, you could also consider try the following UpdateIf formula:

UpdateIf('SP List', ID = varEditItem, {checkbox: true})

or

UpdateIf('SP List', ID = BrowseGallery1.Selected.ID, {checkbox: true})

Note: The checkbox represents the Yes/No type column in your SP List. Please also consider set the "Data row limit for Non-Delegable queries" option to maximum value -- 2000 in Advanced settings of your App settings.

 

Please consider take a try with above solution, check if the issue is solved.

 

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

Thanks for the input @v-xida-msft . By using your following statement I was able to make the patch work.

Patch(
       Datasource, 
       LookUp(Datasource, ID = varEditItem), 
       {
         checkbox: If(
                      Checkbox1.Value = true,
                      true,
                      false
                   )
       }
)  

However, I noticed something when I was testing, don't know how I missed it throughout this entire process. I have two identical controls on this screen and each are only visible to a logged in user type set by a variable. Somehow I had these switched and this is where most of my issues were coming up. While the above Patch works, I have a shorter statement that does the same:

On Check:
Patch(Datasource, LookUp(Datasource, ID=varEditItem), {SPColumn:true}) 

Thanks for all the help in this, I'm at the very least learning new techniques and ways to approach issues withing PowerApps!



View solution in original post

10 REPLIES 10
KrishnaV
Super User III
Super User III

Hi @BigE ,

 

Is your checkbox a choice filed? then try this:

Patch(SampleList, First(Filter(SampleList, ID=4)), {Status:{Value:"Yet-ToStart"}})

In the above formula Status is a choice file in my SharePoint List.

 
I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.
Eelman
Super User
Super User

@BigE 

A few things you could try

 

1. Use UpdateIf() instead of Patch() when updating records. Patch is ok for new records but UpdateIf() seems to work better on record updates, for some weird reason

 

2. Double-checkhow you are creating varEditItem and make sure it's a number eg if it's a Text value wrap it n Value() to convert it

 

3. What column type is 'checkbox'? If it's a SharePoint Yes/No column then these can cause delegation issues. If I ever need to use a Yes/No column in my app/s I create a Choice column with choices Yes, No because these columns don't create any delegation issues for my apps

 

Hopefully one of these gives you a solution?

 

As a side note, I did notice that you have any extra brace on the end of your formula but I figured that was a typo. If not,and there's more to your formula than you've shown, this may be the delegation issue reason.

@Eelmanand @KrishnaV , thanks for the reply. For the variable, I'm setting it on the click on an item in a gallery: Set(varEditItem,ThisItem.ID) which is giving my a numerical value. I am using a Yes/No column in SharePoint to match up with my Checkbox boolean value. I tried replacing Patch() with the UpdateIf() but I received a bunch of errors. Also, the extra ")" was added to close out my clear collect that I failed to add to my initial statement above.

@BigE 

Id try replacing your Yes/No column with a Choice column. You really should avoid these columns if you are using them in PowerApps.

KrishnaV
Super User III
Super User III

Hi @BigE ,

 

Unfortunately, the Yes/No column in SharePoint with PowerApps is not working as appropriate. Rather you make that column as a choice with Yes/No.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.
 


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.
v-xida-msft
Community Support
Community Support

Hi @BigE ,

Is your data source a SP List?

 

If your data source is a SP List, I think the formula you provided should work without any Delegation issue. The LookUp function could be delegated with the SP List, and the ID field could not delegated with '=' operator.

Please check the following article for more details:

https://docs.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-ope...

 

According to the LookUp formula that you mentioned, I found that you have a extra ')' at the end of your formula, please remove it, and modify it as below:

Patch(
       Datasource, 
       LookUp(Datasource, ID = varEditItem), 
       {
         checkbox: true
       }
)               // do not add extra ')' close bracket here

 

Please make sure the varEditItem variable is populated with proper Item ID value in your app. In addition, you could also consider try the following UpdateIf formula:

UpdateIf('SP List', ID = varEditItem, {checkbox: true})

or

UpdateIf('SP List', ID = BrowseGallery1.Selected.ID, {checkbox: true})

Note: The checkbox represents the Yes/No type column in your SP List. Please also consider set the "Data row limit for Non-Delegable queries" option to maximum value -- 2000 in Advanced settings of your App settings.

 

Please consider take a try with above solution, check if the issue is solved.

 

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

Thanks @v-xida-msft, I tried each way and nothing will patch the update except for what I currently have published. Is this happening because I have this patch associated with a checkbox? Should I have a button or does that really matter?

v-xida-msft
Community Support
Community Support

Hi @BigE ,

I agree with you. If the Patch formula does not work with the Checkbox, please consider execute your formula in a Button. Please set the OnSelect property of a Button to following:

Patch(
       Datasource, 
       LookUp(Datasource, ID = varEditItem), 
       {
         checkbox: If(
                      Checkbox1.Value = true,
                      true,
                      false
                   )
       }
)  

Note: I assume there is a column called "checkbox" in your data source.

 

If you still want to execute your Patch formula within your Checkbox control, please set the OnSelect property of the Checkbox to following:

Patch(
       Datasource, 
       LookUp(Datasource, ID = varEditItem), 
       {
         checkbox: If(
                      Checkbox1.Value = true,
                      true,
                      false
                   )
       }
)  

 

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.

Thanks for the input @v-xida-msft . By using your following statement I was able to make the patch work.

Patch(
       Datasource, 
       LookUp(Datasource, ID = varEditItem), 
       {
         checkbox: If(
                      Checkbox1.Value = true,
                      true,
                      false
                   )
       }
)  

However, I noticed something when I was testing, don't know how I missed it throughout this entire process. I have two identical controls on this screen and each are only visible to a logged in user type set by a variable. Somehow I had these switched and this is where most of my issues were coming up. While the above Patch works, I have a shorter statement that does the same:

On Check:
Patch(Datasource, LookUp(Datasource, ID=varEditItem), {SPColumn:true}) 

Thanks for all the help in this, I'm at the very least learning new techniques and ways to approach issues withing PowerApps!



View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (58,183)