Patch Entire Gallery to SharePoint

[hnguy71]

Hi All.

I have a filtered gallery based on logged in user. With that, I have two buttons being utilized outside of the gallery:

1.  An EDIT button that sets the gallery to be in edit mode:

Set(g_displayMode, DisplayMode.Edit)

2.  A SAVE button that sets the gallery to be in view mode. In addition to that, the records are being read from a SharePoint list which needs to have just 1 field updated: Reviewer_Comments:

Set(g_displayMode, DisplayMode.View); ForAll(Gallery1.AllItems, Patch(BSN_FAL_PowerApps, First(Filter(BSN_FAL_PowerApps, ID = Gallery1.Selected.ID)), {Reviewer_Comments: txt_ReviewComment.Text}))

I've also tried the following but it's not updating properly either:

ForAll(Gallery1.AllItems, Patch(BSN_FAL_PowerApps, LookUp(BSN_FAL_PowerApps, ID = Gallery1.Selected.ID), {Reviewer_Comments: txt_ReviewComment.Text}))

The issue: I'm only able to update one record. The ideal situation would be to update the current selected gallery item's reviewer comment field. I've tried other methods found on this community but it just creates new list items, which I don't want.

Any help would be greatly appreciated! Thanks!

[Maurits]

Hi @hnguy71 , I think the lookup/filter of Gallery.Selected.ID makes it that only one record is updated, because only one gallery item will be selected.

Not sure, but I would try something like:

ForAll(Gallery1.AllItems, Patch(BSN_FAL_PowerApps, {ID = ID}, {Reviewer_Comments: txt_ReviewComment.Text}))

The ID=ID part should represent the record in BSN_FAL_PowerApps with the same ID as the item in Gallery1

Good luck!

[RandyHayes]

@hnguy71 and @Maurits 

I wouldn't rely on the ID=ID to bring you results.

You can consider the following formula if these are all *existing* records that you are updating:

ForAll(Gallery1.AllItems, 
       Patch(BSN_FAL_PowerApps, 
             Lookup(BSN_FAL_PowerApps, ID = BSN_FAL_PowerApps[@ID]), 
             {Reviewer_Comments: txt_ReviewComment.Text}
             )
       )

This way you are getting the real record you want to patch.

[hnguy71]

Hi @RandyHayes 

I have a delegation warning when I try to use the @ID of the list as described below:

Am I using the correct ID? Or is it actually working but it's an error that I can ignore?

[RandyHayes]

@hnguy71 

This is one of those "depends" answers...it depends on if you have a delagation concern.  How big is the data in rows?

If it is over 5000 or expected to grow over 5000, then there are alternative ways to go about this.

If not, then you can just ignore it.

[hnguy71]

Hi @RandyHayes 

I'm told to expect a minimum amount of 10,000 rows per quarter. I would like to anticipate for a larger amount of records to be created. If you can offer additional help that would be greatly appreciated!

Thanks!

[RandyHayes · Accepted solution]

@hnguy71 

Yes, that size is going to cause you delegation issues.

The key to dealing with delegation issues is to anticipate them.  However, I always recommend that you only deal with delegation issues if you expect to have them.  Too much time wasted trying to account for delegation when it will never be an issue.

Anyway, that said.  I don't believe you will have an issue with this particular formula.  I cannot stake a claim on that though as I've not personally tested that within this formula.  We are dealing with ID and the equality operator - which is supported for delegation.  

The only potential workaround that I can think of is to somehow get rid of the disambiguation.  We can't do ID=ID in this case because they will resolve to the same (even though once upon a time they didn't).  So, what you might consider doing is:

Put AddColumns(Gallery1.AllItems, "galID", ID) in place of the Gallery1.AllItems in the formula.  Then change the Lookup to ID=galID

See if the delegation warning goes away.

[hnguy71]

Hi @RandyHayes 

The delgation issue most definitely went away. Also, with the original formula you posted it was still only able to patch the first record only. Using the new workaround, both test records were able to be saved. And, since using the equal sign for delegation with ID should have worked, I still don't understand why we have to do workarounds. 

Again, thanks for your help! This community is amazing!

[RandyHayes]

@hnguy71 

Delegation leaves a lot scratching their heads as to why.  But, you have to kind of tear apart some things to understand.

On the data side:

One common misconception is that, since we can delegate against a numeric column properly without issue, then we are safe with ID.  Not the case...ID is a special type of column.  So, only the equal will work.

Okay...so then I will create a calculated column that is just a numeric "copy" of the ID.  Nope!  This is a different special column now and will not work that way (as it has to actually calculate).

So, let's create a real numeric column and then put the ID directly in that (with Flow or some other means).  Bingo! That works.

On the PowerApp side:

Trying to compare a text number to a number value....thought is, let's convert it with Value.  Ooops, now you're doing calculations in the stream.  Delegation warning!  Okay, let's add some columns to convert and compare.  Ooops, still in the stream.  Warning!  Let's preconvert the string to a number and use that.  Getting closer and perhaps problem solved.

In your case, we Added a column OUTSIDE of the stream.  So, this was then "precalculated" and was able to be used...and thus, no more delegation issue.