cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NotaTechGuy
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
Pstork1
Dual Super User III
Dual Super User III

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.

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?  

Pstork1
Dual Super User III
Dual Super User III

 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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,644)