Power Automate Archive SharePoint List Item
Вставка
- Опубліковано 8 лип 2024
- Learn about Power Platform University at training.powerapps911.com/pag...
Learn how to use a Recurrence flow that gets all of the items from SharePoint that needs archived based on their date. Then create a copy of the item and delete the item. Power Automate Archive SharePoint List Item uses an oData query against the date to find old items and passes it to an apply to each loop. Also, the date to archive from is auto calculated using utcNow and Subtract time from date.
0:00 It Starts
0:28 Reasons you want to make your SharePoint list smaller with archiving
1:15 Create a flow to archive in Power Automate
1:52 Add the SharePoint Get Items action with an odata filter and limit top count
3:50 Looking at the flow output to understand the data and what you want to filter against
5:40 Power Automate odata filter a date column
8:34 Creating a SharePoint list from existing list
9:45 Create item in SharePoint via an Apply To Each loop
13:01 SharePoint Delete Item in Power Automate flow
15:40 Subtract from time and utcNow in Power Automate
17:26 Using a terminate action to make flow testing easier and faster
19:39 Change the trigger to Recurrence
Power Apps Consulting and training at www.PowerApps911.com - Наука та технологія
Thank you, Shane! Very easy to follow! Your videos really help me. I'm not an IT Developer nor in IT but am able to create complex apps and flows because of your "how to" videos!
Thanks Shane! I've been using an expression to calculate the date but this Subtract from time will make things much easier. Always helpful!!!
you have the easiest tutorials to follow that I've found! THANK YOU
Thank you for this video!. I was in the step at 18:41 and the flow did not get all my items. The problem was in "Get items". You need to make sure that pagination has a high number as threshold (click on the 3 dots on get items and then in Settings)
Thank you, Shane. I hadn't thought about auto achieve despite the obvious need. Another great video.
Happy to help!
Hello Shane. This just relieved all my headache. It was crystal clear and solved all my problem. Thank you sir!
Awesome! 🤗
Thanks Shane! Very clear. One thing to consider is that your list could need custom "Created" and "CreatedBy" fields, because those will be replaced in the copy process
Good point!
And modified and modified by. I am also bringing over the original ID for reference.
Thanks Shane perfect timing to get to this. Great work.
Very welcome!
Thanks 1st time using this and you made it super easy and clear
Shane, you're just THE BEST! Always something to learn from you! I had no idea how easy Subtract time was to use -- and you KNOW I love working with time -- right! 💥
Happy to help! And yeah you and time. It is like your arch enemy. 😹
Hi Shane I tried and run the flow some items are not deleted and getting error BAD request but If re run the flow that
Hey Shane, good video. In our line of work, we’ve developed a Sharepoint database back in 2018 with premise server system document records and reports, accumulating nearly records. My management wants major haul so we going to be migrating to SharePoint and utilizing PowerApps. We ran into the delegation issue for search due to 2,000 limit. We came up with a method of utilizing the clearcollection, by converting ID into 5 digit number and create bins by filtering with startswith first two figures. The result led to clean giant records collection and greater efficiency in the searches - no crashes.
Glad you got it all working. There are some other ideas in here that might help. ua-cam.com/video/lYi24okXDPs/v-deo.html
Very helpful. Thanks for waking up early to make this!!! 🤓
This was incredibly helpful! Thank you!
I'm so glad! Have a good day Lynn
I have been using SharePoint for a lot of years now and have fumbled through creating this type of flow and getting them to work has been hit and miss. This is absolutely the best tutorial I have ever seen for doing this.
Thanks Jojo 🐶
This is absolutely amazing. Super clear instructions. Thank you.
Glad it was helpful!
@@ShanesCows Thanks again. I don't know when this was introduced but I started to use the GUI version of the Filter Query by turning on Experimental Features via View All Power Automate Settings. Has made things even simpler for me.
Nice work Shane 💥 Thank You
Glad to help.
Thank you Shane!!
Crazy solution indeed!
You are welcome, glad it helped.
@@ShanesCows Can we archive 20000 data with the same process?
Um. This is awesome. Literally my current use-case. Thanks so much!
Glad it was helpful!
This was very helpful. Thank you.
You're welcome!
Thanks for another great video. New knowledge learnt
Awesome 🤩
You are the best!
Thanks 😎
Thanks a lot Shane, the video was very helpful to me
Glad to hear it!
Thank you!
Thanks Shane, keep makeing this kind of videos please. I would love to see some videos on more advance components, for example attach files to a library component with parameters such library url.
Will do!
@@ShanesCows you are the best
Thanks shane….. Thats very smooth !
I have a flow running every day for housekeeping and its a mess.
Ha ha, I was going to propose having an email reminder that this flow is running, but guess what, the last thing you mentioned was this, that must be that I've learned so much from you I know how you now think!!
PS. Yes, I'm a bit behind with videos, cleared my day to view the ones I missed, I have a bunch on different topics :).
Adi I love we are on the same page. 😎 Hope you are doing great 😊
Also a great way around delegation limits
If you are trying to beat delegation another hand tip is nested functions. ua-cam.com/video/iG8SjWtX1yM/v-deo.html
Awesome! TY. PS I'll prob be inquiring about your online classes and subscription in 60 to 90 day. I'm saving atm. PS. I'm a Networking IT Pro with about 7 industry certs and nearly 20 years exp in IT support. However, I'm trying to transition to Power Apps dev or at least something hybrid. Do positions like that exist? Support and SharePoint admin / power apps inhouse dev? And realistically what would I be looking at earning? Ballpark of course. I feel like I'm learning this and python pretty quickly w my IT background and am looking for ways to use it for IT Networking. TIA! @@ShanesCows
Thanks! I wanted to do this to a list that was reaching the limit on its view threshold
Happy to help. Have a great day. 🐶
Love your videos Shane! I do have a query though I have a list with multiple content types, I have a column that is mandatory but mandatory to only certain content types, when I use the Get Items it fails as its missing the required property as the item is blank for all the other content types. Do you know a way around this?
Hi Shane, really great video about SharePoint, I'd like to ask if there is a way to sort the data table lookup field(multiple values) using a combo box?
Thanks. This worked so well. Is there any way to maintain the original creation date? When my list archives, it uses the date of archive as the creation date. I'd like to maintain the original date given this list is populated through a PowerApp. Any ideas?
Hi Shane as always another great engaging video... Thanks. Moving this one stage further, is it possible to take multi-person field values, attachments and the items version history from live to archive list? Thanks Gary.
hope to get help here :)
Thanks Shayne for the video!. 👍
FYI, I was not able to archive images and also lose the ability to have version history for each item.
Yeah, versions I knew about. Images that is interesting but make sense why they don't work. 😐
Thank you Shane. I have this use case but I want to archive my records based on the “Approved” status value. What should I do? Thanks in advance.
Hi Shane, I find your videos very useful, but I am not sure if I have missed something. You use the created field and the UTC time stamp for that Field. I have a sharepoint list that people can create items/resources and put a date for when that resource is for. I would like to archive items based on that column but from 8 weeks from the current date. When I complete a test and download it, the date it gives me is the date it is for in dd/mm/yyyy format and not UTC. How do I go about changing the flow to do as i want it to?
Hi, had the same problem. If you set in your sharepoint list that you want to collect only date not time you get time in normal way. In that case do everything as Shane but under step "Substract from time" add one more step "Convert time zone", as Base time set "Calculated Time" Source time zone (UTC) Coordinated Universal Time, Destination time, the same as above, and Format string "Short date pattern". Last thing to change is in step "Get Items from sharepoint" change in filter Query not Calculated Time as Shane did, but Converted Time. Hope it helps.
Hi Shane, great vid - ty! Are any additional steps needed for attachments, or multi-field columns. Like in one of my list sites I'd like to auto-archive it has two checkbox fields that can have up to 5 selections each.
Attachments you would have to modify a bit to get File Attachments and then Add file attachments. This video would help. ua-cam.com/video/K74UFYgrKB4/v-deo.html Checkboxes I think the Dynamic data would just pass but haven't tried.
@@ShanesCows Thank you, I'll give it a shot.
Hi Shane, great video as usual but could you test the archival flow with people picker column in SharePoint list because we may have some people in the people picker column that left the organisation so how to handle such cases when creating archival flow
People picker column copy seems to work fine for me even with users who no longer exist in our organisation.
Hi Shane, great video as always, now I'd like to ask, how could I query these new archived Sharepoint lists in PowerApps for example, I mean, is there any way, to add them dinamically in Powerapps as a new datasources in addition to the original one that was already added?
You would need 2 data source and 2 galleries is what I would recommend. One connected to the original list, and one connected to the archive list.
Would a If / switch function not work with this in the items property of the Gallery ?
Thanks again Shane! Another fantastic tutorial. One question... how many records can we reasonably archive?
SharePoint will hold way too many. So a really big, big number. Just be careful on what you want to do with them after archive. Programmatic things get harder the bigger the list. I think I am going to make this a short. 😀
@@ShanesCows thank you!
Very helpful video, curious to know if anybody has figured out how to transfer attachments in these lists as well?
I’m trying to figure this out too. I need this feature.
Shane, thanks for the video. It is very helpful. In my list that I am trying to archive, there are attachments. Can those be moved to the archive as well with the line?
I haven't tried but I doubt it. What you will need to do is add functionality to your flow to get attachments from the item to archive and then loop through and add attachments. I think I show some of the pieces in this video. ua-cam.com/video/K74UFYgrKB4/v-deo.html
@@ShanesCows Thank you.
Hey Shane great video! I have almost the same process, where I'm also including multi people picker field and attachments, but one thing I've never been able to figure out is how to get a version history. I know I can get the last version number but would be awesome to get a field of the changes to that record for auditing purposes. Any ideas?
I haven't tried that scenario either. Sorry
Thanks for another great video. I assume this will work fine if i wanted it to filter out a set choice criteria. Not sure what equals would be in this case if its just text, would it be as simple as just e?
Hi shane I again I have some concern can I follow the same process if I need to archive a complex list where items are more then 7000.. And also lookup column is present..
I tried but it did not work
This is great and I resolved one large list with it but, how do you handle lookup columns?
Thank you, this helpe me a lot. But i have one issue, i want to keep the date as the same as in the orginal list. We use powerapps to manage internal transport, and we use the recived date when the record is last edited/changed. If the record is moved to a new list than this date is changed. Do you have any idea on ho to solve this?
Hi Shane, Thanks for the videos. I have one requirement on the same work flow which is my list contains choice column it allows multiple selection so in that case how can I get all the values from multi selection column. Thanks
Hi Shane
I have used this process multiple times and has always worked - however this time in the 'create item' section i have 'Assigned to claims - 1' and i cannot seem to select the same value without it creating multiple 'apply to all loops' which in turn is creating duplicates when its pulling data across. Is there a way around this?
Hi @Shane - can you build a Knowledge Base using PowerApps?
Shane. Another excellent and informative video! If I want to archive all items that are at least 90 days old and have a status of either Closed or Approved, would the OData filter be Modified le formatDateTime(addDays(utcNow().-90).‘dd.MM.yyyy’) and (Status eq ‘Closed’ or Status eq ‘Approved’)??
That looks close. Pretty sure And Or need to be capitalized. But I would give that a go and see what happens 😎
@@ShanesCows Turns out that I didn't even have to worry about that. I was only the GUI-ish display where you can add rows or groups. I had to play with the grouping to realize that it defaults to Or. I also added a Compose at the top with a link to this video in case I or anyone on my team needs a refresher. lol
if list has more than 7000 records will work? even Get items itself getting more than 5000 items means this flow will work sir?
Hi! Is there a hard limit to the number of columns you can use in the Get Items? Thanks!
The limitation is 5000 items, regardless of Power Automate license. This is a limit of the SharePoint REST API, and not Power Automate.
@@ThePayanazo so that's the column limit right within the get items setup? Items retrieved is typically 5000 items. Thanks!
@@fpanglergal sorry just to clarify, you mean rows right (or records or items)? just read that you said columns
@@ThePayanazo no I'm wondering if in the get items setup is there a column limit so if the list has 200 columns with data to map to the archive list is 200 possible or is there a limit? Thanks!!
@@fpanglergal got you, you need to create a SPO view with the desired columns (system columns will be always included unless you select them manually in the query). After that you can work updating only those columns instead of having a lot of them
Hi Shane! Please, could you do exactly this but considering items with attachments? How can we transfer an item with their attachments?
Did you see this? Closest thing I have. Power Apps Attachments for SharePoint - Files, Images, and Signatures
ua-cam.com/video/K74UFYgrKB4/v-deo.html
Hi Shane, this is really helpful - THANK YOU! I am getting stuck with a column title. I want to archive all items where the "Status" column equals "completed" I've tried to put that where you explain the UTC date is "lt date" and I've tried using "eq" and "equals" but it's failing every time :( any suggestions?
Alley it would be
ColumnName eq 'completed'
for the oData filter. Notice the column name doesn't have quotes and the text has single quotes.
Shane thank you for this video as it helps tremendously with what I needed. Mine gets a little more complicated and hope you can help. We have designed our share point to have a status but multiple different statuses that could fall under ‘Complete. For instance we have some like ‘completed - successful’ or ‘completed - unsuccessful’. Since these are both in a completed status is there a way to tell the query that if it contains the word ‘completed’ it will pull and archive?
Hi Shane I tried and run the flow some items are not deleted and getting error BAD request but If re run the flow that items are deleting please what's the solution for that
I was understanding this pretty well until I needed to divert and pick something I could use in my scenario. I need to have the filter find a value b/c I'm looking for a completed task. Once it says completed, I want that item to be archived. But everything I do and everything I look up is not helping me with this field. How would you tell it to search a column for a value?
On your Get Items action you need an oData filter query. I talk about those in this video. ua-cam.com/video/qGqqQjGMun8/v-deo.html
Hi Shane I tried and run the flow some items are not deleted and getting error BAD request but If re run the flow that
Super helpful! Is there a way to copy the comments? Also, what if the list items have custom permissions? Is it possible to copy the permissions?
I am guessing both are possible but would require you to hit the SharePoint API to get the info. Not sure, I have never tried either.
Hi Shane, what if the items I want to archive have pictures ? The Get Item from Power Automate doesn't work...
You would need to add a Get Attachments step into the flow to get the associated attachments and then loop through them and Add Attachment to your new item. Should be possible but I haven't done it before.
How to unhide the list and show it in the SharePoint again? I tried to pause the fluxe but apparently doesn't work :(
Hello Shane. I want to Archive "list items" but did not create a Sharepoint Site. How can I go about this>
Lynda - Sorry, I don't understand what/where you want to archive if you don't have them in SharePoint.
Is there a way to keep the created date the same?
I think you would have to use the rest api to create it. But I haven’t tried
I followed the video, and everything worked for me but i can't get the attachments over to archives. can you make a video for this please?
I think my flow is stopping after 100 iterations on the apply to each.is there a way to increase this?
Yes, click on the get items settings and look at pagination.
@@ShanesCows thanks so much Shane, I appreciate it
I need to auto delete SharePoint list items based on the value in a certain field. How can I achieve this? Please help me.
Very similar to this video. Use Get Items with an oData filter to get the items you want. Then use the Delete Item action. You got this!
@@ShanesCows Thank you so much, Shane. Nice to finally engage with you👑. Your videos helped me develop our company online shopping cart, from start to finish. You are such a blessing🙏.
I have 20000 items, can I archive in same way? Also can you please make video on relation I'd columns just like you said that relationship with ID
Shane, can you think of a reason that this flow would only allow 100 records to archive at a time? That is what is happening to me. Top Count is empty.
Click on the Get Items action, click Settings, and turn on pagination. Now increase it as you need. 😀
@@ShanesCows thank you very much! Your videos and help have been fantastic and I truly appreciate it.
Chuck here you go. YOu are famous now 🥳 ua-cam.com/users/shortsEmgMFGfNWW4?feature=share
PS - If you have feedback, leave a new comment on that video. I don't see comments on comments very easily.
Hi Shane young, I am Dhanvanthri I need your help can you please upload a short video regarding how to upload image in Data verse(latest power apps version). Please respond
Hello Shane. I followed the steps and it worked. But, it only archived 103 items. I have about 3000 items. Is there something else I need to do? Thank you so much for everything, Sir.
you are in luck, someone else asked this exact question and the answer is YES ua-cam.com/users/shortsEmgMFGfNWW4
Oh thank you so much but the video is blurry and can't see anything. I'll listen and try to figure out.
You're explaining is create. Step: Apply to each, it creates automatically for 4 sub groups, because I have many drop-down menus in my list. When I run the test then I receive the following. Do you know why?
The execution of template action 'Auf_alle_anwenden_2' failed: the result of the evaluation of 'foreach' expression '@items('Auf_alle_anwenden')' is of type 'Object'. The result must be a valid array.
I was wondering if someone can answer this question?
The bad thing is that the historic changes of the items will be lost
This works great for creating the item in a new list, but I need to archive my list items and maintain the metadata such as ID, created by, date created. So basically I need to MOVE the list item, but that doesn't seem to be a choice. I did find a video that I was able to move an item to another FOLDER within the same list (ua-cam.com/video/mN_Jr8GW64g/v-deo.html), but I need to MOVE the item and all it's data to a totally different list.
This has gotta be possible! Help me Shane and I will send Chewy some dog biscuits!!!