Delete Items from SharePoint list when not in excel

[NotaTechGuy]

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 

[yashag2255]

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!

[NotaTechGuy]

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. 

[yashag2255]

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!

[NotaTechGuy]

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 🙂

[yashag2255 · Accepted solution]

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!

[NotaTechGuy]

GOT IT!  Thank you so much @yashag2255 

[Tyson]

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

[Tyson]

@yashag2255 Hi Yash,

I figured it out! You are a legend!

Regards

Tyson

[gonzalhu]

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

[matcad]

Hi @yashag2255 ,

I am trying to figure it out but without solution.

I wanted to delete the items in Sharepoint once they are deleted in excel.

Can you show me please some screenshots?

I am not a good programmer.

I was able to update and create from excel, but I cannot delete the items.

Please, I repeat Please, show some screenshots.

Thanks in advance.

Matteo.

[dumainej]

I would love to see your flow and how you applied it because I think we're trying to accomplish the same thing. 

[Anonymous]

There are so many people with the same question and there is no concrete answer. 

Could someone PLEASE provide a screenshot?

My solution is the following but it seems wrong. I think the condition if array contains the "Skill Name" which is my unique identifier is wrong. How am I supposed to capture this condition right?

Thank you for any help!

Best,

Ilona

[JulieBird]

I agree, I am trying to do a similar thing with update instead of delete.  But there is no solution above, just someone saying they 'Got it' 😞

Basically, I want to compere an Exell spreadsheet with a SP List every day and if the item in excel exists in SP list (identified by a unique field), then update SP List item, otherwise update the SP item with another default value (this update could be the delete option others are looking for).

But I cannot work out how to comrete the two lists or the conditions to tell it which way to go (PA is very complicated!)

Bring back SP Designer Ha!

[eli-g]

Hi! I think I may have something that helps, if you haven't already figured out a solution 🙂 I've done everything the same as you (except my Variable is named 'Top Location ID'), but in the last apply to each loop, which is relabeled 'SPItem', I have several additional Composes and a Condition. 

The 1st compose is to set up for 2nd compose. The 1st Compose is getting the Top Location ID from my SP List (which is the 'Title' field in the SP List) and then in 2nd compose is looking that the Top Location ID is contained in both the variable from excel and in the SP list. Then, if the output from Compose 2 is true (meaning they're contained both), do nothing; otherwise delete the SP Item. 

Hope this helps!