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

Flow to sync SharePoint List with Excel Table

OK, I have what I thought would be a simple common scenario but cannot for the life of me figure it out...

 

I have an excel file emailed daily from our ERP.  I have managed to get a flow working that will update the SharePoint list based on the rows of the table in the excel file. I copied this example here and it work perfectly.. https://www.youtube.com/watch?v=qxuTVjQbECo&lc=UgyopCzR8rrj_sp91Fp4AaABAg

 

What I cant seem to figure out is how to delete the SharePoint items when there is no longer a corresponding row in the excel file?  I have come up with this but it deletes everything?  I thought by filtering the Get Items step I would only return the items that do not exist in the excel file.

 

Capture.JPG

3 REPLIES 3
Highlighted
Dual Super User II
Dual Super User II

Re: Flow to sync SharePoint List with Excel Table

The way the logic of your flow works is that the First Loop processes each row of the list.  It then does a get Items for all the Records that don't match that Row. The Problem is that if the Record doesn't match that Row, but matches a different one it will be in the Get Items and will be deleted.  What you need to do is Loop through the Rows and add the reference column to a local collection.  Then Loop through each Record in the SharePoint list. If the Record matches an entry in the local collection do nothing, otherwise delete it.  With the local collection you should be able to check the one field in the SharePoint record with an 'in' operator.

 

If your SharePoint list is too large you'll need to do the records in Batches.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted
Frequent Visitor

Re: Flow to sync SharePoint List with Excel Table

Thanks mate, I appreciate your help.

 

Your response is a bit above my knowledge unfortunately, you have pointed me in a direction to start googling at least 🙂  Have you seen this anywhere where I could follow along?  

Highlighted
Dual Super User II
Dual Super User II

Re: Flow to sync SharePoint List with Excel Table

 I can't say that I've seen an example exactly like this.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

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

Top Solution Authors
Users online (6,067)