Stop using Power Apps to bulk update SharePoint use Power Automate Instead
Vložit
- čas přidán 24. 07. 2024
- In this video, you will learn how to update your data source like SharePoint using bulk update methods. ForAll is great but slow so instead we learn to use the JSON function to format our collection and then send it to Power Automate flow to Parse the JSON and then Apply to each. A much faster method that avoids your users from having to wait.
0:00 PowerApps Bulk Updates
1:18 Demo of Patch vs. Power Automate flow speed
3:00 Review what not to do and overview of the process
6:05 Create the collection to update from
7:32 Create a flow to bulk update your data source using Compose, Parse JSON, Apply to Each, and Create Item
13:20 Refresh your Power Automate flow in your Power Apps app
14:47 A couple of things to think about when using this method for Bulk Updates
Power Apps Consulting and training at www.PowerApps911.com - Věda a technologie
+1 for keeping your mug on screen. Great video! I've passed a collection to Power Automate for handling a bunch of file attachments, never thought about applying that to handle a batch of data records. Good tip!
Thanks 😊
Shane, it was a pleasure meeting you at the workshop in the conference last week. Thanks for your time and for answer all my questions.
Great meeting you Edgar!
Thanks again, Shane. This is a major performance improvement on my app that has to check for existing. The flow processed it so much faster than the ForAll with Patch. Also, now the user can choose to wait for the app response, but does not have to since I have the flow sending a notification and updating an SP bulk changes audit log list.
Awesome. I love hearing this type of applications where you are putting in play for your needs. Thanks!
I'd keep your video on, it makes it more personal, one of the reasons why your content is where I keep going back too.
Appreciate it Sean. 😎
Starting the flow may take less time than executing the Patch function, but the flow will run much longer than the Patch function does. Also, the patch in your example makes a single call to SharePoint, while your flow makes a different call for creating each item, which can lead to throttle error as well as overconsumption of your daily billable action quota, which in turn can lead to your flow being slowed down or even disabled by Microsoft. In general I would say let the user wait for the Patch function to complete, maybe display a spinner gif while it's executing. If you still want to use flow, couple of remarks: replace the trigger with PowerApps v2, so you can specify the required parameter(s) without having to use a separate Compose action. You can also get rid of the Parse JSON action by nesting the parameter value in the JSON expression function which doesn't require a schema (the only downside is that you have to specify field names manually in subsequent steps, can't pick them dynamically). To perform bulk SharePoint operations with flow, always use the 'send http request' action with batch API, build the body by using the select action, never with apply to each. Not low code, but the only method that works reliably for the long term with huge amount of data.
Thanks for sharing. I most certainly would never delete a comment like this. 😎 Learning and sharing is the whole point of why I create content here. I enjoy reading different takes and ideas.
I agree with bgergoe's post above. Shane, your example hides the fact that the data is not instantly loaded with a flow so the timing is a bit deceptive. I have many bulk flows adding into SharePoint and the more data the slower they are. Also, as mentioned above, there is no communication between the flow and the app so indication the flow finished. This can be problematic for an app. I agree that the loading of large amounts of data should be done in Power Automate but doing so from a PowerApp has a very specific use case and is not a general method of processing data for an app.
I aboslutely agree with you, bgergoe.
Hi bgergoe,
I totally agree with your observations. Although, is there anyway we can add values to people column while doing bulk update with batch API in flow? This is something I have been struggling with. Could you please share an example if you've already accomplished this?
Thanks.
Thanks for the video. It was great meeting you at MCPP last week. I was one of the people who won the gift card! Sharing your videos with my staff!
Awesome Cory. You can tell everyone you are a pro at heads or tails. 😎 Great meeting you.
I use ForAll mostly. Secondly, Shane, I appreciate your PowerApp videos here. I just discovered an easier way to upload attachments to share point list using patch function, no flows , easiest way.
How are you saving attachments with Patch? Sounds interesting. :)
@@ShanesCows dont we use form1.updates to patch the attachments back to list? You have another way to deal with attachments?
Awesome video as always! You could also speed up the Flow by turning on concurrency in the apply to each loop (assuming you don't need to create those records sequentially).
Great tip! I should have also shown that setting. Boo me.
You can replace the for each loop with a http request to sharepoint and do the bulk update with a single post. This is so much faster than updating or inserting items one by one. Also, by not sending a response back to powerapps, the user will not know if the flow fails for whatever reason. I think therefor its always best to include the response with the result status of the flow and show the user if it was processed successful or not.
Nice, you can also use the ParseJson keyword in PowerApps to store all data in key value pair and send that to flow create or update item,
Also if data is in bulk but mostly 10 to 20 records then Patch(list table, collection table or variable table name) where both having same schemas can help immediately patch items, if no ID it will add new record, if there is ID it will update same record
Hi Shane, when collecting the records, can we collect values from Dropdown, Combo box and toggle true/false inputs? If so, how would these be formatted in in your ClearCollect? Would this be the same formatting as you would use in a direct patch to SharePoint? (outside of flow).
Seeing your face is helpful .. your expressions are what help identify important/key steps.
Thanks Joe 😊
Hi Shane, I have two excel files which I am trying to bulk upload to sharepoint or dataverse and I'm struggling with a lookup column. The CDS does not list the lookup column in the target destinations to map the columns
Thanks Shane, great video!
Happy to help. Have a great day. 🐶
Thanks! I pass MANY columns back at a time, but they are usually updating existing rows, how would you approach updates? I get tangled up with how to ask power automate to check if the record exists. Users are SO impatient! Me included 😅.
And YES, to keeping your face on the videos, same reasons as the others stated.
Awesome vid and technique/method Shane! QQ: Whenever I use the parse JSON, I always remove the "Required" element that is produced when you generate the schema from sample (at 11:15 mark of the video). Is this bad practice?
is this possible with a powerbi integrated app? wherein the flow updates the rows on the existing data in the app?
Thanks Shane, great video bro!! you get things done fast an easy. I was wondering if is it possible to use this bulk update method to insert new records with attachements in sharepoint list?? hope you could read my message.
Attachments I don't think so :( Since they don't work with anything but forms. You might have to do a bulk update using flow, it can also add attachments. Sorry, no easy answer here. This video might give you some ideas? czcams.com/video/K74UFYgrKB4/video.html
Hi Shane, thank you for all these cool tricks. Can you also post a video on best practices to avoid issues when multiple users are writing to a sharepoint list via powerapps.
Stopping 2 users from conflicting is so hard. :( I have done it but it was terrible and embarrassing all of the traps I made for it :(
@@ShanesCows haha i'm ok with it. Better something than nothing at all :) keep up the great work! Can you recommend a datasource that's more effective for multiuser?
Is there a way to unpivot specific columns in a SharePoint List using Power Automate?
Thanks for the video! It seems a little misleading to compare the patch and the flow, because of the caveat you snuck in at the end. The bulk patch is a complete statement--create the records, respond to the app with success, and update the datasource cache to include the records. I believe that method is still faster than doing the same with a flow. You compared using flow to just create the records vs. the full solution of patch. The real comparison would be waiting for a response from the flow, then refreshing the datasource. I'm sure that would be slower than a bulk patch. Ultimately depends on if you need to immediately know those records or not.
I wasn't trying to sneak it in. I think the idea is more of trying to offload the work to flow so the user doesn't wait on anything. Users are impatient. But yeah, depending on your scenario you might need to wait on the work to be done either way. In that case maybe Patch is better. It is a fair debate, I just like to plant ideas of what is possible. 😎
@@ShanesCows I definitely found it intriguing and glad you made the content so I could think about it!
Hi shane,
I am importing excel data into dataverse through power automate flow. And i used Apply to each and inside that add a row action.
When i am trying to import more than 500 records, flow is taking much time to import data even though i used concurrency. Is there any way to import data quickly in less time?
Thanks in Advance!!
Amazing video! Thanks Shane.
2 quick questions:
1. Can you use the same method to bulk edit existing records? (IE. 100+ customers parent data updated with new overall balance)
2. Will using complex data types from Dataverse (Like option sets) change how the Json is parsed?
This could be game changer for my team's powerapps. We do lots of bulk updates in low connection areas.
I was attempting to do this with a complex data type (lookup field) and got this error on the flow call in the power app "The JSON function cannot serialize tables / objects with a nested property 'the lookup field name' of type 'Polymorphic' ". Would be interested to see if there is a solution to that
Thanks for another awesome video. I miss the face
👍
Thanks for this Shane. All of the videos I find about Patching back to SharePoint seem to assume I have a bunch of NEW records. What if I collect a bunch from SharePoint (say comments) and then the user adds a comment to the collection. When I patch back in this way, it duplicates all the comments I downloaded then adds the new record. How to I get it to Add new rows and update existing rows? Really appreciate all you do to teach us about PowerApps.
If the ID is blank it will create a new record. But if your row has an ID column with a valid ID then it should update that record.
@@ShanesCows That was it! I was including the GUID column but not the ID. You are brilliant.
Shane, with the new feature of Power Automate to choose which credentials a connector like Sharepoint will use (the user that executes the flow(run-only user) or the flow owner) do you think it will be possible to have the user with View-only permissions in a sharepoint list fill in a form in power apps and instead of submitting it directly through there (where they will need more than view only permissions), send json data to Power Automate and create the item with the flow owner credentials?
I see it as a way to improve security with Sharepoint as a data source, I can’t figure a way to have the users being able to create items in a list but stop them from deleting or editing them for good.
Hi Eduardo, yes this is possible. I have done this in several apps. All users have read only access in the list, but can add (and update) via logic in the PowerApp.. Collect all fields in the Power app form into a variable (record), and call the flow with a single param (the record) - flowname.Run(JSON(varAllFields,JSONFormat.Compact)) .In the flow I suggest you parse the passed data with ParseJSON, and then for the SP Create / Update Item action; create/set the connection reference to a user that has the necessary access, and configure the flow's run-only option "conncetions used" to use this SP connection. Hope that this helps. Note: If you have complex fields (such as people picker) you might need to "massage" the data before adding it to the record in PowerApps.
Is the only way to make sure the refresh is AFTER it is complete is by sending a response back and making the user wait? I have a Flow that uploads images to SP, but often my refresh fires before all the actions have happened. Thoughts?
Yup Sean. 😑 We have done some other clever things with timers but they are too crazy to quickly explain. Basically we tell Timer how many new rows to expect. It keeps refreshing until it gets that many rows back.
@@ShanesCows thanks. I have done that as well, but currently use a duplicate collection to feed a local gallery while uploading in the background and then refresh on the "back" button which usually get it very close unless someone is moving very quickly.
Pro Tip: Edit the settings of the "For All" action in flow and turn concurrency on for increased performance. Not turning that on for larger record counts would be fairly slow. Honestly surprised the 100 example was as fast as it was unless you had set that setting?!
Good point Chris. I should have shared that. Boo me.
Thanks for the tip Chris. I tested this with 1000 records, 8 minutes with currency off, 5 minutes with concurrency on. 😎
Patching (or any data function) in a ForAll is absolutely not the way to go from a performance perspective. Patch the collection to the source as Share showed.
@@munkybutler1 Now patch those same 1000 rows outside of a ForAll and provide the timing.
@@iamintractable1805 I think the point wasn’t to show the fastest. The point was to show the alternate :). Sometimes just a normal patch don’t work and you need to have flow / the for all do stuff. Although one can argue you do it all local in a collection that matches and patch up changes is how I do bulks but there might be times flow has to process / delegation issues etc.
Excellent video!
But will it work if I use an Update Item action to update a child database. Example I am updating a parent database and some of those columns I want to update into child database. Therefore I will only have the parent ID column in the child database and I won't have its own ID column.
You would have to build logic into the flow that after you create the parent, loop through the children, and use the ID you got from the newly created parent. It is possible, but take a bit of elbow grease.
Gracias! justo lo que necesito para realizar una carga masiva de registros a listas sharepoint!
Encantado de ayudar! (Glad to help is what I hope that translates to. 😎)
Hopefully this message will be read by you Shane…
I’m trying to bulk update and nothing seems to be working.
I have checkboxes on my gallery which has the collect this time function. I would then like to be able to select a few bits of data to change, for example a persons name and email which is on a couple of text input boxes.
I then want to update the records within the collection (based on the checkboxes I selected) and save the person fields but leave everything as is.
The datasource has 9k plus records and at most there bulk update will be less than a hundred records.
Any help would be appreciated 😊
Check out this video and see if you ahve better luck?
czcams.com/video/nkp9MrBHN-s/video.html
If not you can always hit up the support at PowerApps911. :)
@@ShanesCows Thank you, I will get back if it works.
PS Keep the combination of face and no.
PS PS thank you for all your videos, you took me from zero knowledge to relatively advanced (haven’t tried bulk update though).
@@ShanesCows I tried it and I get an error. See photos attached, please could you help?
The specified column is read only and can’t be modified…
Is there a limit on running a workflow? If all patches functions will be moved to power automate
There are API limits flow and Power Apps. Is that what you are asking? learn.microsoft.com/en-us/power-platform/admin/api-request-limits-allocations
@@ShanesCows No, I mean the count on how many times does this power automate floe gets triggered , per day or per month. Is there any limits?
Great video! But what about Bulk Updates? UpdateIf vs Update in a flow?
Hi Nelson. I haven't done bulk updates like that. I will have to think about it but I think Patch(table, collection) might be the best option there if you had the records.
@@ShanesCows Yeah... but would be fun to see all of the CRUD Operations you know?
+1 for keeping your face on, how else will we be able to obtain last week's weather from your tan?
This is a key service I offer Roy, I am glad you noticed. 😎 (Note to self: More sunscreen)
Sunscreen Shane. Sunscreen! ;)
Agree. But I also have a very red leaning face. The rest of me is pale white. 🤷♂️
@@ShanesCows same, but I come over yellow 👍
Keep your face on ....It adds more fun to the video 😀
Thanks. :) I agree!
Keep face, you make Power Apps human/friendly/approachable. Thank you.
I appreciate that!
Hi Shane, unfortunately, you've done an Invalid Comparison...
The Patch(Datasource,updateCollection) method carries out an "Upsert" operation, automatically figuring out if each record needs creating or updating.
Your Flow methodology is "Create" only, so using your method with "changed" records will create duplicate records in the datasource.
If you build a Flow that carries out a "lookup" to the destination to see if the current record exists, and then decide between Update and Create - the flow methodology is FAR slower!
I have never ran into this scenario where it wasn't just a create new. But you are correct if edit was in the mix the story changes. :)
+1 for face 🤷♂🤷♂
Thanks. I am handsome, that is for sure.
moderating, moderating?
I am here but I let some comment threads take their own path. 😎
@@ShanesCows my comment from yesterday was removed...
Not by me and no one, other than CZcams has the power to moderate my channel.. 🤷
I also double checked and it isn't held for review. So not sure what you posted but if it got deleted it had to be CZcams doing it. Sorry.
@@ShanesCows I'll add it as a new comment then
Keep the face!
Thanks
I miss your face!
Thanks Wendy 😎
@@ShanesCows And you're not ugly 🥰
Face is better
Thank you for the feedback!
Power Platform is an environmental disaster. The heat generated from CPUs processing an outrageous quantity of unnecessary API requests is a disgrace. Microsoft has manufactured and encouraged this ridiculous way of doing things in order to then rinse the less well informed of lord know how many millions with their request limit and allocation based billing . It's absolutely mind-blowing hearing crypto mining getting slayed for how environmentally irresponsible they are while Microsoft is coming up with what is basically IT debauchery.
That’s a pretty hot take.
@@13mschen Perhaps. Forgive my grief, recent experiences using Flow to do a some pretty simple data manipulation left me feeling empty inside when I discovered how utterly inefficient the out of the box actions are.
The energy required to run a Power App is less than what is required for you to watch this CZcams video.
@@PowerAppsEric Potentially depending how much of the video I watch and many requests the PA makes
tu comentario pierde objetividad frente al video, etas cagando fuera del tiesto amiguito.