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.
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.
The only issue with patch is WiFi/cell connectivity on tablets that are running power apps. If you have a shotty network that could drop signal making the call to power automate in the cloud would work better as once the flow is triggered it wouldn’t matter if the users connection dropped, the flow would still run saving whatever data it was.
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.
+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!
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.
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).
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.
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?!
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.
@@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.
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
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. 😎
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.
@@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?
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! 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.
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? ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-K74UFYgrKB4.html
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.
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!
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.
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.
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).
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.
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?
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? ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-nkp9MrBHN-s.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).
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
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!!
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.
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.
@@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.
