cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Delete Items from SharePoint list when not in excel

I have a flow to create and update SharePoint list items based on a rows in an excel table, this works perfectly.  What I am trying to do now is create another flow to delete these SharePoint items when their corresponding row is now longer present in the Excel file.

 

I have tried adapting the example that @v-alzhan-msft suggested in this post Deleting item from specific Sharepoint list upon Master list delete but it deletes everything but the records I am trying to delete.

 

Please, please, please could somebody tell me where I am going wrong..  My example is below 

 

Pic1.pngPic2.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Dual Super User III
Dual Super User III

Re: Delete Items from SharePoint list when not in excel

Hey @NotaTechGuy 

 

So understand it like this: 

 

Get rows from excel and identify a unique column based on which you want to check the SP list. Now create an array variable and populate this array with the unique columns. (List rows from table-> apply to each value-> append to array variable)

 

Now, out of the loop add an action to get items from the SP List. Apply to each SP item, add a conditon and in this check if that array contains the field from the SP list. If it contains, all good and if not, delete that item. 

 

The blog article was just to reference existence of an element in a data source and it can help you in this scenario that you are building. 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

9 REPLIES 9
Highlighted
Dual Super User III
Dual Super User III

Re: Delete Items from SharePoint list when not in excel

Hey @NotaTechGuy 

 

In the current flow, you are getting all the items that match the title and then deleting those-> that means delete all the items in sp where the tiel matches the column in excel. 

 

In the filter query, can you try updating eq with ne?

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Highlighted
Frequent Visitor

Re: Delete Items from SharePoint list when not in excel

Hi @yashag2255 ,

 

I changed the filter query to ne and that deleted all records?  Could it be the logic in the condition?  I copied the example 

length(body('Get_items')?['value']) is greater than 0
 
Thanks for helping me out. 
 
 
Highlighted
Dual Super User III
Dual Super User III

Re: Delete Items from SharePoint list when not in excel

Hey @NotaTechGuy 

 

I just noticed. We are doing it all wrong. So the List Rows is getting us data from excel and then we are checking for a particular row item in SP list and then deleting the ones if that is not found (so this is bascially deleting all items other than the particular row item not the entire column). 

 

To get rid of this, you need to create another array variable and get all the matching items in that and based on that delete the items from the SP list. You can follow this article here to check the approach of adding the items to an array and then comparing with the list before deleting: https://powerusers.microsoft.com/t5/Microsoft-Flow-Community-Blog/Delete-duplicate-records-items-fro...

 

Hope this helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Highlighted
Frequent Visitor

Re: Delete Items from SharePoint list when not in excel

Thanks @yashag2255 , I am trying to adapt your example now.  I cant see how the "test"array gets it's values though..  You reference it in the condition, then append the results?? How does it get the values to check in the condition? As you can see I am a super non technical user, just trying to advance from using multiple versions if an excel file 🙂

 

 

Highlighted
Dual Super User III
Dual Super User III

Re: Delete Items from SharePoint list when not in excel

Hey @NotaTechGuy 

 

So understand it like this: 

 

Get rows from excel and identify a unique column based on which you want to check the SP list. Now create an array variable and populate this array with the unique columns. (List rows from table-> apply to each value-> append to array variable)

 

Now, out of the loop add an action to get items from the SP List. Apply to each SP item, add a conditon and in this check if that array contains the field from the SP list. If it contains, all good and if not, delete that item. 

 

The blog article was just to reference existence of an element in a data source and it can help you in this scenario that you are building. 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

Highlighted
Frequent Visitor

Re: Delete Items from SharePoint list when not in excel

GOT IT!  Thank you so much @yashag2255 

 

 

Frequent Visitor

Re: Delete Items from SharePoint list when not in excel

I @yashag2255 yash, I was trying to acheive something really similar. Could you please provide some support on how can this be achieved?

 

Regards

Tyson

Highlighted
Frequent Visitor

Re: Delete Items from SharePoint list when not in excel

@yashag2255 Hi Yash,

 

 

I figured it out! You are a legend!

 

 

Regards

Tyson

Highlighted
Regular Visitor

Re: Delete Items from SharePoint list when not in excel

Thank you very much for taking the time to post the solution; it works!

Helpful resources

Announcements
secondImage

August 2020 CYST Winners!

Check out the winners of the recent 'Can You Solve These?' community challenge!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (4,294)