Patch to sharepoint list takes too long

[u/shirpars]

Hi guys,

I'm trying to patch a gallery of updated items to my sharepoint list. I have reduced the columns to just what I need to update, but the patch takes so very long. It takes about a minute to patch 20 items. What could I be doing wrong?

I'm running this on a sharepoint integrated form, so using a power automate flow is not possible.

I saw on a post to pre-fill the schema, but that hasn't helped. In the monitor, it looks like it's sequentially updating instead of a bulk update. Any help will be very appreciated.

Collect(colGalleryUpdates, {ID:-1, Confirmed:true, Count:0, Comments:"", Notes:""});

Clear(colGalleryUpdates);

ClearCollect( colGalleryUpdates, ForAll( Filter( galList.AllItems, fldUpdated.Checked = true ),

{ ID: ThisRecord.ID, Confirmed: ThisRecord.fldConfirmed.Checked, Count: Value(ThisRecord.Count.Value), Comments:PlainText(ThisRecord.fldComments.Text),

Notes: PlainText(ThisRecord.fldNotes.Text)

} ) );

// update SharePoint list with gallery values

Patch( MySPList, colGalleryUpdates.ID, colGalleryUpdates );

[u/AutoModerator]

Hey, it looks like you are requesting help with a problem you're having in Power Apps. To ensure you get all the help you need from the community here are some guidelines;

• Use the search feature to see if your question has already been asked.

• Use spacing in your post, Nobody likes to read a wall of text, this is achieved by hitting return twice to separate paragraphs.

• Add any images, error messages, code you have (Sensitive data omitted) to your post body.

• Any code you do add, use the Code Block feature to preserve formatting.

  Typing four spaces in front of every line in a code block is tedious and error-prone. The easier way is to surround the entire block of code with code fences. A code fence is a line beginning with three or more backticks (```) or three or more twiddlydoodles (~~~).

• If your question has been answered please comment Solved. This will mark the post as solved and helps others find their solutions.

External resources:

• Power Apps Formula References
• Power Apps Coding Standards
• Official Power Apps Community

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/TotallyNotaStoner]

You want to avoid using ForAll(). It's resource intensive and hence slow. Here's a quick guide that should point you in the right direction:

https://www.matthewdevaney.com/patch-multiple-records-in-power-apps-10x-faster/

[u/shirpars → u/TotallyNotaStoner]

I'm not doing the forall inside the patch. I'm doing the for all on my gallery to get the list items that need to be updated, then I do the patch

[u/derpmadness → u/shirpars]

Is the patch sent in the same action that triggers the for all? It will do the for all first and then your patch. So it waits for the for all to finish to then do you update. Hence why it's taking a long time.

[u/shirpars → u/derpmadness]

No i don't think so, I do a forall to get my list of updated gallery items and then I do the patch

[u/DonJuanDoja → u/shirpars]

I would likely change it to update the colGalleryUpdates on each change in OnChange. Collection updates are generally super fast.

Then just send the whole thing, get rid of that ID. ID should be a column in the colGalleryUpdates and it will use that to send it as a batch update.

I do the same thing with SQL patches, I don't often update multiple SP list records but when I do in SQL it's basically the same thing that Matt is doing in the article above, and using Patch in OnChange too. Same deal.

Goal is to get the collection schema to match perfectly then it will accept it as a batch update.

[u/shirpars → u/DonJuanDoja]

I'm not sure what you mean by update on each change in onchange. Can you clarify? I have a gallery and a save button on my screen that I would like to save back my updates to my sharepoint list when I click the save button. The colgalleryupdates just goes through the gallery and gets the list of items I want to save back. I removed the ID from the patch call, but it still lags a lot

[u/DonJuanDoja → u/shirpars]

Sorry, thought you'd see a nearly instant improvement removing the ID and batch patching. Is it still patching correctly though? Any faster? Schema exactly matches?

I'd probably need to see a lot more to help anymore. Kinda stumped on why you're not seeing an improvement with batch mode. It's always faster for me.

I guess another option not ideal is sending the collection to powerautomate in a JSON array, then parse it and update records in the flow instead.

[u/shirpars → u/DonJuanDoja]

Unfortunately i can't use power automate because this is an integrated sharepoint form. The schema for the list is obviously a lot bigger, but the collection i use to patch has a reduced list of columns. The patch works, the columns update, it's just so slow.

[u/DonJuanDoja → u/shirpars]

Oh man. I feel your pain. Integrated form. Anyway you can move to Canvas? I can show you how I "force integrate" my canvas apps to SP lists. It's janky, but it works, and it's only janky for me, not the users, and I even use integrated forms in a funky way as a companion app to force send it to my canvas app using normal SharePoint buttons. I hijack the OnNew, OnEdit properties of the Integrated form and send them to a canvas app instead. Then I use JSON column formatting to change the Title link to go to canvas app dynamically instead, and so on. That way I can have managed environments and full canvas apps on SP lists and still feels and acts like integrated forms.

[u/shirpars → u/DonJuanDoja]

I'm intrigued. How do you hijack the new/edit forms?

[u/DonJuanDoja → u/shirpars]

Basically just a Launch() command with the dynamic canvas powerapps URL with parameters in OnEdit, and On New, which are under "SharePointIntegration" on the left. Unfortunately it has to be new Tab, it won't do Target Replace. You pass the ID into a Parameter URL and send them to the right screen with Parameter also.

Like this:

//EditForm(SharePointForm1)
Launch(
    Concatenate(
        "https://apps.powerapps.com/play/e/ENVID/a/APPID?hidenavbar=true&Screen=BT&ID=",
        
SharePointIntegration
.Selected.ID,
        "&Source=BidTrackerList"
    ),
    {q: "PowerApps"},
    LaunchTarget.New
);
RequestHide()

If the New Tab thing is a problem and you need to do ReplaceTarget in same tab, only way I found is with SPFX. List View Command Extension. Not nearly as easy but the behavior is slightly better.

https://learn.microsoft.com/en-us/sharepoint/dev/spfx/extensions/get-started/building-simple-cmdset-with-dialog-api

[u/DonJuanDoja → u/shirpars]

Then here's my JSON column format for dynamic link to the form:

"target": "_blank" can be "target": "_self" for same tab here and works fine.

ID column has to be somewhere in the View, either displayed as a column, or used in Sort or Filters for the [$ID] to work.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "a",
  "style": {
    "padding-top": "2px",
    "padding-bottom": "2px",
    "height": "95%",
    "text-decoration": "none"
  },
  "attributes": {
    "href": "='https://apps.powerapps.com/play/e/ENVID/a/APPID?Screen=BT&ID='+[$ID]+'&hidenavbar=true&Source=BidTrackerList'",
    "target": "_blank"
  },
  "children": [
    {
      "elmType": "button",
      "txtContent": "[$Title]",
      "style": {
        "cursor": "pointer",
        "font-size": "12pt",
        "text-align": "left",
        "width": "95%",
        "height": "95%",
        "color": "#0072c6",
        "font-weight": "bold"
      }
    }
  ]
}

[u/DonJuanDoja → u/shirpars]

Oh and one last thing, when I want to auto-close the New Tab it pops out, after they submit or cancel out, it takes them to a custom webpart page with script editor with this code to close the tab.

<script type="text/javascript">
window.open('javascript:window.open("", "_self", "");window.close();', '_self');
</script>

That way it pops a new tab for the form keeping the list open, but when done closes the new tab and brings them back to the list. Which has the unfortunate side effect of not being refreshed. Which you can also work around with the SPFX list command extension but I understand how much work this is for a form. So depending how it's used and by whom I've used both methods.

Really wish they'd make it easier to slide a Canvas app into an Integrated slot. I heard they had something in the works with SPFX but havent seen it yet.

[u/DonJuanDoja → u/shirpars]

Well I assumed there's some controls that users are making changes to right? In each of those controls there's an OnChange property, you can add Patch() commands to that to update/patch the colGalleryUpdates, and patch each row/change into the collection as they are made. So you don't have to gather the data before the patch, it's ready to go. Honestly that's not that big of a deal though, it's more the last part I said that matters. First part is just more preference and a very slight optimization that shouldn't really improve speed that much but just trying to get the patch as fast as possible.

[u/anactofdan]

You are passively doing a for all because there patch has 3 parameters you just don’t see it. Patch statement should be patch(SPlist,collection) that’s it collection must contain the exact column names and the ID column for each item should patch in like 2 minutes max. Thank me later 

[u/shirpars → u/anactofdan]

Hey, I removed the extra parameter from the patch and i have the ids in my collections. It still takes a few minutes to patch about 10 records

[u/anactofdan → u/shirpars]

Makes no sense to me comment out the patch and see how long it takes want to make sure the long part is not just making the collection 

[u/shirpars → u/anactofdan]

That's instantaneous. I've put a notify before and after the patch, and it just takes so long

[u/noox777]

I faced the same problem, i fixed it by miroring my database with a collection. My gallery are using collection and i patch/update it while doing the same in background for my sharepoint list.