How to Update or Delete records in SP list with input data from Excel

[Anonymous]

Hello,

I have a need to do the following using flow. Wondering if anyone can help!

1. Always Read the newest excel file which is saved in SP --> Documents -->Output. 

2.  Column A is labelled as "block Code". On the same row, column G is labelled as "final status" where the value is either successful or fail.

Block Code	Block Validation	Contract Validation	Block Entry	Harvest Entry	Contract Entry	Final Status
2087437646	Successful	Successful	Successful	Successful	Successful	Successful
2087437647	Successful	Successful	Successful	Successful	Successful	Successful
2087437648	Successful	Successful	Successful	Successful	Successful	Successful
2087437649	Successful	Successful	Successful	Successful	Successful	Successful
2087437650	Successful	Successful	Successful	Successful	Successful	Successful
2087437651	Successful	Successful	Successful	Successful	Successful	Successful
2087437652	Successful	Successful	Successful	Successful	Successful	Successful
2087437653	Successful	Successful	Successful	Successful	Successful	Fail
2087437654	Successful	Successful	Successful	Successful	Successful	Fail
2087437655	Successful	Successful	Successful	Successful	Successful	Fail

Now I want the flow to find the same block code in two different SP list.

 In SP list "A" the block code is found under column "Block code" and in SP List "B", the block code is listed under column "contract code". Column names are different in 2 SP list but the values are the same.

For block codes that have final status as Successful in Excel, I want the flow to delete those from both SP (A and B) list

For block codes that have final status as 'Fail" in excel, I want the flow to update the status in SP list as "Fail". Only SP list 'A' has a status column.

[abm]

Hi @Anonymous 

There two key things you need to do.

1. Filter List A and List B filter the records matching success and block code, then delete

2. Filter List A fail records and update the status

Please follow the steps. Change the below excel column names accordingly.

Rest of the below steps all are in one loop. Inside the loop you will see multiple loops.

Above I used Block Code columns name as Block_x0020_Code. The reason for this is in SharePoint ODATA filter space you need to substitute with _x0020_

If you want to know how to filter SharePoint records I have got a collection of video tutorials filtering SharePoint multiple columns.

https://www.youtube.com/watch?v=bSkWdDnhCmQ&t=15s

If you need any further help please let me know.

Thanks

[Anonymous]

Hello abm

I created the steps that you mentioned and I am running into an issue at first "get item" section. Also,

1. How do I make this flow automated?

2. There will be new excel files in the folder every two hours. So I want the flow to run as soon as a new file is added in the folder

3. The "Final Status" column is only present in the Excel spreadshet and not in the either of SP list. I see that "final status eq successful is mentioend in the Get item sharepoitn section.

The flow should follow this logic : if block code "1234567890" has "Final Status"= successful in excel, then find that block code in both SP list and delete. If the final status is "error" then update the "status' column for that block code to "Error".

And below is the screenshot of the Flow

[abm]

Hi @Anonymous 

Will get back to you.

Thanks

[abm]

Hi @Anonymous 

Please see this post for the trigger when a file is created.

Solved: Read excel when file is created on sharepoint - Power Platform Community (microsoft.com)

Ok I misread that filter. So all you need to do is remove the Get items filter second condition where we checking the status success or fail. Keep only the matching record identifier condition.

Also extend the list rows filter to check the condition where status = success.

Do the same steps to filter the the failed records. So for this you need to another new list rows excel action step. Then do the filter to check SP record exists and delete it.

Thanks

[Anonymous]

Hi abm

I am new to the power automate world and feel a bit lost 🙂.

I've added "when a file is created in a folder" to trigger the flow

I have also only kept the matching record identifier condition in the First and second "Get item".

if possible, can you share a screen shot of where ( after which step) i can extend the list rows filter to check the condition where status = success. The one that I am trying on is after 'when a file is created' trigger step. The pop-up box does not show this feature.🙁

or do I add list row present in a tale step after each get item?  ( there are total of 3 get item steps in this flow)

I apprecaite your help!

Ibex

[abm · Accepted solution]

Hi @Anonymous 

Following are the steps we discussed.

1. List Rows Present in table for retrieving all excel rows.

2. Filter Excel array for success records

3. Get Items SharePoint filter by Block Code

4. Delete Item SharePoint

5. Do the same for second list (Step 3 & 4)

6. Filter Excel array for failed records

7. Get Items SharePoint filter by Block Code

8. Update SharePoint Item

Note: Excel connectors doesn't allow dynamic mapping of filename and table so your trigger might have some issues. Manual trigger is the one you need to use. But there is an article from Josh which you could follow and see whether you could implement in the above logic.

Power Automate – Excel Dynamic Filename – Flow Alt Delete – Josh Cook [Microsoft MVP]

Thanks a lot

[Anonymous · Accepted solution]

Thank you abm for your help. As you suggested, I will look into the steps of using this as automated flow.