Видео

@@RommelGerardGalindo The destination list IDs? It pulls all destination list items, indexes them by the primary key column values, & then the GenerateSPData action is where the source dataset primary key values are used to lookup the ID of the destination list record where the source key value matches that destination item’s key value.

I am trying to do filter query using join function so it will do like accountnumber eq 1 or accountnumber eq 2 and so on. Now i got the id in sharepoint list but i cant add it in my array and cant make a batch out of it

@@RommelGerardGalindo You’re trying to do what? Are you trying to do a One to Many update where one source item may be used to update multiple destination items?

@@Hellocrzywrld The reverse is not crucial in any way, just a very minor performance improvement because there is a later Sort( ) in the Respond to PowerApp actions that must sort the entire set of data in ascending order. So if all the batches were sorted in descending order then that ascending step would take a little longer because it would have to reverse the order within each batch & do it on an action that isn’t working concurrently. Meanwhile reversing the order at the previous step should require less computation & happen concurrently.

@@davidlattimore663 I’d recommend watching the video & using the template for the newer Upsert V2.7. Then if you run into issues reach out again & we can troubleshoot. community.powerplatform.com/galleries/gallery-posts/?postid=dc95f047-afcc-4a31-bc84-962b240fe6fa

Roughly I think I'd add a Filter array action inside the relevant Modified Apply to each loop with the Filter From input as the "Last table version" action & the condition set to where the Loop record's primary key matches the Last table version primary key. That should grab the relevant previous version of the record & then you can build conditions checking the values of various columns for changes across the new version & the last version.

@@tylerkolota Thanks for the help. This worked Another question: For showing both the previous and new values, I copied the code but it's not working for me. The select action is not returning the new value. I tried changing the "select" code but can't figure it out. Code as below Select_Modified Keys "from": "@body('Modified_records')", "select": { "": "@concat(item()?[outputs('Settings')?['KeyColumnName']], if(empty(item()?[outputs('Settings')?['KeyColumnName2']]), '', item()?[outputs('Settings')?['KeyColumnName2']]))" Filter_array "from": "@outputs('Last_table_version')", "where": "@contains(body('Select_ModifiedKeys'),concat(item()?[outputs('Settings')?['KeyColumnName']], if(empty(item()?[outputs('Settings')?['KeyColumnName2']]), '', item()?[outputs('Settings')?['KeyColumnName2']])))"

@@monikk2777 Did you remove or rename a “List rows present in a table” action?

@@tylerkolota I had renamed it to List rows before but now that you mentioned it, I changed it back, and now I get this error: The input parameter(s) of operation 'Read_the_excel_table_and_only_let_the_flow_run_if_no_edits_during_read' contains invalid expression(s). Fix invalid expression(s) for the input parameter(s) of operation 'Read_the_excel_table_and_only_let_the_flow_run_if_no_edits_during_read'. I'm using the same formula you used: removeProperty( removeProperty( json(replace(string(item()), '@odata.etag', 'odataetag')), 'odataetag' ), 'ItemInternalId' )

@ Is the expression greyed out? You may need to copy it, toggle the map format button on the right twice, & re-enter it in the expression tab.

@@tylerkolota that worked! Thanks a lot Tyler!

@@elijahd.spragueph.d8905 You have a lower level license that only allows 5000 pagination on the Excel list rows action. Try version b of the flow import which uses Excel list rows in a loop that pulls in records in 5000 row increments drive.google.com/file/d/1_YDFzkf4QSAeloTv9_zxVuVQBAWlt8kf/view?usp=sharing

@@AyurvedBenefitsHealthyLiving Have you tried the formatDateTime( ) expression?

Here's what I did for Date: formatDateTime(items('Apply_to_each')?['Date'], 'MM/dd/yyyy') For time, in 12 hour AM/PM format ( e.g. 08:30 PM) I did this: formatDateTime(items('Apply_to_each')?['Time'], 'hh:mm tt') Just make sure to change the Apply to each and column header in each function to match your flow.

@@JackDollarss If you aren’t using the PA Desktop flow inside a cloud flow & thus can’t use the template from the main post, then you can set a subflow in Power Automate Desktop to email you & then set the On Error of every action to trigger that subflow.

​@@tylerkolota That's exactly what I did! I setup a Subflow to send an email which is working fine so far but the issue is that in the On Error settings I have set "Continue Flow" as well which continues even if the flow fails! So If the flow fails then it will first send the mail and then continues the flow! If the flow continues then it fixes it self (I have set it up that way according to my process) so sending an error mail becomes useless! That's why I want to setup in Cloud to set an email when an actual error occurs in PAD!

@@meyo10240 I think the solution package for this template includes a OneDrive example. Then you would just have to set up the Apply to each loops with SharePoint actions in them.

@@yashgandhi6128 Then you can only create items, not update them tachytelic.net/2021/06/power-automate-flow-batch-create-sharepoint-list-items/

I'm able to download the solution file from the link just fine on my laptop: drive.google.com/file/d/1UG3mj9Y-FCZ0ncCfUs3ucGZDdoiZJtgp/view?usp=sharing

Are you talking about a Gallery in Power Apps? There is a way to reference AllItems for a gallery column & use the Sum( ) expression on it. www.crmonce.com/how-to-calculate-gallery-items-in-power-apps/

Are you talking about a Gallery in Power Apps? There is a way to reference AllItems for a gallery column & use the Sum( ) expression on it. www.crmonce.com/how-to-calculate-gallery-items-in-power-apps/

Do you have an expression somewhere with an items('Apply_to_each_Modified') reference outside the correct apply to each loop?

Hello, Were you not able to specify your id / primary key column names in the template flow & have it batch update based on the matches?

@@tylerkolota thanks for replying! I've managed to sort that issue out now - basically because it was an UPSERT and I just wanted it to an UPDATE so I just removed the UPDATE part. The latest challenge I've got is that I need to update based on the ID and also where another column in the SharePoint list equals let's say 'x'. This value isn't a look up into the excel but just a filter on the SharePoint list

@@Rob46 I would think you could go to the "Do until Get destination list IDs + keys" loop, add the relevant SP column to the "SharePoint HTTP Get items" select query, then add a Filter array action between the "Set variable LastID" and "Select IDs + Keys" actions to filter the SP records to just those where the relevant column = x, & adjust the "Select IDs + Keys" From input to use the Filter array outputs.

@@tylerkolota thanks I'll give that a go

@@tylerkolota got it to work! Thanks for your help 😃

Hello, I do have a post on Dataverse batch actions here: community.powerplatform.com/galleries/gallery-posts/?postid=1ee689f5-81e0-4fb0-9155-a4387fc1598c Alternatively you may also be able to use Dataflows to connect Excel data to Dataverse: learn.microsoft.com/en-us/power-query/dataflows/sync-excel-cds-dataflow

Hello, If you just want to batch create SharePoint items, then Paulie's original blog on Batch Create may be helpful: tachytelic.net/2021/06/power-automate-flow-batch-create-sharepoint-list-items/

@@ekoiswinarso the way this is designed, I don’t see paging reducing load times much on the Power Automate side. It still has to load all the items into the flow before it then applies the page cut-offs.

Hi@@tylerkolota, are there some method on getting list data with combination of offset and limit in power automate, similiar with Odata filter?

@@ekoiswinarso I gather offset & limit in Power Automate would refer to the Top count & Skip count. That’s the thing, the Excel connector / calls include both a Top & Skip, but the SharePoint connector / calls only include a Top parameter. To do something like a Skip in SP I think you need to actually make a call to SP, get the items, & take the ID of the last item to then in the next SP call get items where ID is greater than the previous last ID. The set up for this template doesn’t even use that type of set-up, instead it uses a faster method of concurrently pulling all items at once.

@@ekoiswinarso There is an option to use pagination links, but the set-up requires a slower Do-until loop, just like the other slower ID method I mentioned. We want something that works with a faster, concurrent Apply to each loop. www.matthewdevaney.com/get-over-5000-items-from-a-sharepoint-list-in-power-automate/#Initialize-Variables-For-List-Items-and-SharePoint-URI

Hi @@tylerkolota , very appreciate for your replies 👍😊. The "Skip" clue is very interesting, unfortunately it only works for Excel. And also yes, I already read that link. However, back to my requirement for pagination, I won't rely on ID at all since user will have capabilities to sort and search, thus the IDs will be randomly sort as the query result as well, not in ascending order anymore.

It looks like multiple choice fields can be updated through HTTP calls with the following format: { "__metadata": { "type": "Collection(Edm.String)" }, "results": ["Choice1", "Choice2", "Choice3"] // Your choice values } So in the GenerateSPData Select action you would specify your multiple choice field name on the left side of the mapping, then on the right-side of the mapping for the value you would need to write a dynamic expression to output something like the above json. So you would likely form the above as a string in a concat( ) expression & then surround that concat with a json( ) expression, json(concat('{ "__metadata": { "type": "Collection(Edm.String)" }, "results":' **Insert expression to output a string-ified array of the multi-choice values for each item**, '}'))

Thank you so much! I will try this.

What error(s) are you getting? How is it not working?

Thank you!

If you are saying your main file you are checking for updates is itself a JSON file with a JSON array of records in it, then you will need to switch out all the Excel List rows actions with actions to get the file content of the JSON file & convert it into typed JSON like what I did for the previous table version JSON file in the "Get file content using path Last table version" & "Last table version" actions. Then use the output of those actions wherever the Values of the Excel List rows had been referenced.

@@meierthomas There is an option on the HTML table action to change from automatic to custom columns. If you change it to custom then you can add column names to the left side mapping & their corresponding expressions item()[‘InsertColumnName’] to the right side of the mapping.

@@meierthomas You can add a Filter array action on the previous version JSON where an array of modified primary keys contains the item primary key to get the previous values for modified records.

@@meierthomas I’m not sure what may be off on your styling after the HTML table

@@tylerkolota I have tried custom columns at the create HTML table step using the body from the previous step (Modified records) in the from field. The table content renders as item()['Ordered Date'] so it doesn't pick up the actual value. Looking at the execution the 'raw input' to Create HTML Table shows the data is there; i.e. { "from": [ { "Column1": "May Containers", "Ordered Date": "45337", "Warehouse": ".....", "Email": "......", "Payment Required ": "45398", "ETA": "45395", "Model": ".....O", "Ordered by": ".....", "PO No_x002e_2": "575", "Customer name": "......", "Sales Order No_x002e_": "SO009071", "Supplied": "0", "CIN": "2405-991", "ID": "mY885K" }, but the output doesn't pick up the data. Also I'm not able to enter <space bar> when I entered the header / value pairs; wouldn't accept item()['Ordered Date'], only item()['OrderedDate'] so I created the expression in a notepad and copied it into header / value

@@tylerkolotaDid some more research and found that GMail seems to be the issue. Works fine on Outlook

You may want to reduce your batch size & turn concurrency off on the Apply to each loops inside the Batch Update & Batch Create scopes

@@tylerkolota sir i have turned off the concurrency but it has increase my flow running time

@@ashishrawat8276 Yes, the issue with the skipped records is SharePoint may be overwhelmed with requests & not finishing some, so the flow needs to slow down & send the requests over more time. I can't do anything more about this trade-off. Thanks

@@shinsvaka93 No that’s not expected

​@@tylerkolota Understood, thanks for the reply. Should the mul(20000,... be changed depending on the table size? Looking at the outputs of the initial List rows in the Do until Get row count for batches loop, it seems like it doesn't actually return anything except the first row, the second do until loop doesn't give any value for the output?

Looks like since I didnt import it, I had to manually turn on pagination, since the Get Rows items were limited by default. Cant believe I didnt realize that was the issue.. Thanks for sharing the flow by the way, very helpful!

@@MrCheddah13 Are you saying like have a Kanban board of projects & then have various sub-Kanban boards for each project with all their tasks? I’d think there’s probably a way to do that by creating the sub-Kanbans where each item has a Lookup column to items in the main projects Kanban. Or if you still want all the items in the same Kanban, then it should be a similar set-up to the ParentGoals lookup column where the lookup is to all the items in the same Kanban board, so you would just create a ParentProject lookup column & in each task select the parent project card in that ParentProject column.

@@tylerkolota Thank you for the response Tyler, I think what I meant was the second half of your explanation. For example my current department is utilizing Notion's Project management template so I have a "page" that shows a list of all the projects (20+) with their varying properties as needed and it is its own database. On separate pages you have all of the tasks everyone is assigned too, each person has their own page, and the database of tasks is all one database separate from the projects database, the pages are just filtered per person. One of the properties inside the tasks is the option to select which project it belongs too. The way Notion handles this is by using a "relation" property and allows you to select the Project database to find the list of projects. While Notion has worked great, there is a separated half of our department that uses their own system for task tracking and I have been asked to combine the two departments using Sharepoint. I really appreciate you taking the time to respond!

@@MrCheddah13 Yeah so you would have all the projects & tasks on the same board & use a lookup column to link each task to their respective project. Then you’d probably want to add something, maybe a choice column with Praject or Task as options & set different SharePoint views to filter to only Project items or only Task items. And then people could also filter the Task view to only items where they are listed as Accountable or Responsible (or whatever roles/assignments you plan to use).

@@tylerkolota Thats really awesome! Ill give it a try tomorrow I really appreciate your time and creating this!

Sure, the flow can use any trigger

@@s1301950 You could add some parameters to the flow so it only returns x number of items between set page numbers/items. But personally I have yet to encounter a use-case where I’d have a user going page by page through results instead of refining their filters & search input to go directly to the item(s) they need.

@@cat-si1et Hm, I have not heard a use-case where someone wants to search two non-contiguous words/phrases before. But I would think you could edit the flow to check if there is a second search term, then use a Filter array query with an or( ) & 2 contains expressions.

You can try that, but I believe it will be limited to search, as in you won't be able to combine searches with filters. And it uses the Office365Groups connector which at least in Power Automate is being replaces by a V2 which does not allow broader graph api queries. So I don't know how long it will be available for. ruclips.net/video/NJaHC5S9Cjo/видео.htmlsi=nMdsc1QRJ5pEqVpY

@@tylerkolota I see, the graph api is not really a sustainable way as of now. Thank you for the info! Love your work :)

@@madhavilatha7881 The template orders the text replica based on wherever the center of the text boxes are. I don’t have any further adjustments to help with a significantly tilted page on this template. However if you want you can try using a different method with premium HTTP actions to call GPT4o Mini’s image/vision component to extract from documents community.powerplatform.com/galleries/gallery-posts/?postid=73cdb790-11c9-45b7-80d0-b991d1f43f34

@@tylerkolota Thank you for the input. I checked the above approach but I may not be able to go with this approach because of the premium actions and Azure functions logic.

This is specifically for SharePoint lists. I have yet to see any way to use this or a different api for SharePoint document library batch calls.

Microsoft started renovating their community site, they didn't transfer the upload files from the old site, & I can't manually re-upload any of the download files there at the moment. I'm waiting on a solution from their support team. In the meantime, here is a Google drive link to a zip folder with solution imports for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing

Microsoft started renovating their community site, they didn't transfer the upload files from the old site, & I can't manually re-upload any of the download files there at the moment. I'm waiting on a solution from their support team. In the meantime, here is a Google drive link to a zip folder with solution imports for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing

@@tylerkolota Thank you so very much. I really appreciate you responding this quickly.

Yes, they didn't transfer the upload files from the old site, & I can't manually re-upload any of the download files there at the moment. I'm waiting on a solution from their support team. In the meantime, here is a Google drive link to a zip folder with solution imports for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing

@@tylerkolota You are amazing, truly thank you!

Microsoft started renovating their community site, they didn't transfer the upload files from the old site, & I can't manually re-upload any of the download files there at the moment. I'm waiting on a solution from their support team. In the meantime, here is a Google drive link to a zip folder with solution imports for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing

@@tylerkolota thanks a lot

Microsoft started renovating their community site, they didn't transfer the upload files from the old site, & I can't manually re-upload any of the download files there at the moment. I'm waiting on a solution from their support team. In the meantime, here is a Google drive link to a zip folder with solution imports for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing

Yes, with their forum renovation they have yet to include upload files on any old posts and it won't let me edit the old posts to try to manually re-upload the files either. While I'm waiting on support for a fix, here is a Google drive link to a zip folder with solution imports for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing

@@tylerkolota Microsoft likes to keep people "on their toes" lol. This is a great video, very detailed and a solid approach. Thank you.

Microsoft recently renovated their community forum and have yet to transfer file uploads for old posts. And it also won't let me edit the posts to try to manually add the files back. While I'm waiting for a fix, I created a link to a zip folder on my Google drive that includes the imports for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing

Microsoft started renovating their community site & I can't re-upload any of the download files there at the moment. Here is a Google drive link to a zip folder with solution imports for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing

@@tylerkolota Thank you so much for the quick response. I am going to try to set up my teams project channel today.

Microsoft is renovating their community forums. I'm not sure if the page they moved my stuff to even has file uploads and I'm unable to edit the posts. I'll need to figure something else out, but in the meantime, email me at takolota@gmail.com with what templates you want the solution packages for.

Here is a google drive link to a zip folder with the solution package import for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing

@@tylerkolota Thank you very much! Appreciated!