Delegation on filtered patch to SharePoint list

[BigE]

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.

[KrishnaV]

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.

[Anonymous]

@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.

[BigE]

@Anonymousand @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.

[Anonymous]

@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]

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.
 

[v-xida-msft · Accepted solution]

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,

[BigE]

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]

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,

[BigE · Accepted solution]

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!