Instantly Delete items from two list using Power Automate - delete SharePoint list item based on another list

Instantly Delete items from two list using Power Automate: Delete SharePoint list item based on another list

No comments

 215 total views,  3 views today

In this “delete SharePoint list item based on another list” using Power Automate tutorial, we will learn how to use Power Automate to delete SharePoint list items based on another list. Before getting into this tutorial, let’s understand whether this is possible.

Can we delete SharePoint list item based on another list using Power Automate?

The very first rule is that both lists should have a unique column value. If the “Title” column value is unique, using this column, we can achieve this immediately by using the “When an item is deleted” trigger from the SharePoint list trigger. However, this is limited only to the “Title” column—meaning if you have any other custom column with a unique value in both lists than the Title column, we cannot use the “When an item is deleted” trigger. There is a technical reason behind this. We will know that in the coming section.

In this context, many would say: in both lists, we should have a custom unique ID column, then we could delete a list item based on another list using the Power Automate “When an item is deleted” from the SharePoint list trigger. However, this is also not correct. Below are the reasons why

If we want to delete List B items from List A—and the “When an item is deleted” trigger is set on “List A,” then while we query List B, we need to filter the target list (List B) based on the unique id column in List A. We will not get this reference from the trigger output of “When an item is deleted.”

Limitation in When an item is deleted trigger

If we look at the below screenshot, in the filter query we have set the “CustomUniqueID” column from List B. When we tried to pass the “CustomUniqueID” column from the trigger output of List A “When an item is deleted“, we only saw the below out of the box columns:

  • ID
  • Name
  • Filename with extension
  • Deleted by
  • Time deleted
  • Is folder

Delete SharePoint list item based on another list using Power Automate

Delete SharePoint list item based on another list using Power Automate

Because of this limitation, we cannot use any custom columns even though it has a unique value except the Title column (map Title column as Name in the filter query) in the “When an item is deleted” trigger.

The technical reason for the delete trigger is that it does populate custom columns in Power Automate.

We cannot get the dynamic content because the item has already been deleted. Hence, we will get a 404 item not found error.

An alternate approach to delete SharePoint list items based on another list using Power Automate

Instead of using the “When an item is deleted” trigger, we need to use the “When an item or a file is modified” trigger in SharePoint, and to be more conditional, we need to have a custom column something like “WantToDelete” type of Yes/No in List A.

Then the flow should be like the below:

Delete items from two lists using Power Automate
Delete items from two lists using Power Automate

Explanation: Delete items from two lists in SharePoint online using Power Automate

 When an item or a file is modified

Add this trigger event.

When an item or a file is modified - Delete items from two lists
When an item or a file is modified – Delete items from two lists

Parameters:

Pass the below parameters in the “When an item or a file is modified” trigger action:

  • Site Address – Your site URL
  • List or Library Name – Your list or library name

Get item from List A:

Add this action to a get item from the current (source) list.

Get item from source list - delete list item
Get item from source list – delete list item

Parameters:

Pass the below parameters in the “Get Item” action:

  • Site Address – Your site URL
  • List or Library Name – Your list or library name
  • ID – Select the ID from the trigger output of the “When an item or a file is modified” trigger action, from the Dynamic tab from the left side panel.

Initialize & Set VariableDelete confirmation

Create a boolean variable “WantToDeleteConfirmation” and set the variable with the value from the “WantToDelete” column of the trigger output of the “When an item or a file is modified” trigger action, from the Dynamic tab on the left side panel.

Create a Boolean variable in Power Automate for delete an item
Create a Boolean variable in Power Automate to delete an item

Set variable – WantToDeleteConfirmation:


body('Get_item_from_List_A')?['WantToDelete']

Initialize & Set Variable – Get Unique ID column value

Create a string variable “GetUniqueID” and set the variable with the value from the “CustomUniqueID” column of the trigger output of the “When an item or a file is modified” trigger action, from the Dynamic tab on the left side panel.

Create a string variable in Power Automate to delete an item
Create a string variable in Power Automate to delete an item

Set variable – GetUniqueID:


body('Get_item_from_List_A')?['CustomUniqueID']

Get Items from the source list

Add this action to get items from the source list (List A) based on the “CustomUniqueID” Column.

Get items from source list using filter query in Power Automate
Get items from source list using filter query in Power Automate

 

Get items from the source list using a filter query in Power Automate

Parameters:

Pass the below parameters in the “Get Items” action:

  • Site Address – Your site URL
  • List or Library Name – Your list or library name
  • Filter Query – Your “CustomUniqueID” column is equal to Select the Get unique ID variable from the previous step on the left side panel.

Apply to each control

Add this apply to each control to loop through the Get Items from the source list A.

Apply for each loop in Power Automate to delete items from two lists
Apply for each loop in Power Automate to delete items from two lists

Inside the foreach loop perform the below:

Get the “CustomUniqueID” column value from the source list (List A) for each item.

Extract the unique id column from the foreach loop  like below:

GetUniqueID


items('Apply_to_each')?['CustomUniqueID']

Note:

  • Basically, we will get only one unique ID column from this loop as we are performing this trigger on the current item. However, if we still get more than one item from this loop, we have used the Get Items from the Source List (List A).

Now, based on the “CustomUniqueID” column, from the source list we will query the target list (List B) from inside the loop.

In the above screenshot, we have added the “Get Items from ListB” action. In this action, we need to pass the below parameters:

  • Site Address – Your site URL
  • List or Library Name – Your list or library name
  • Filter Query – Your “CustomUniqueID” column from the target list (List B) is equal to Select the Get unique ID variable from the previous step from the left side panel variables section.

Now, add a condition control.

Condition Control

Add this condition control to check whether the desired item exists in the target list for deletion.

Check this value:


length(outputs('Get_items_from_List__B')?['body/value'])

Note:

  • If the length of the Get Items from the target list returns 0 (zero), that means the item does not exist in the target list otherwise it has and will perform the deletion.
  • First, the item will be deleted from the source list (List A), then it will be deleted from the target list (List B).

 

Conditionally delete items from two lists using Power Automate
Conditionally delete items from two lists using Power Automate

In the condition control no part, apply to each loop control will be added automatically, inside this, add two delete item action.

  • For the first delete action – select the source list URL, List Name and Item ID
  • For the second delete action – select the target list URL, List Name and Item ID

Demo: Conditionally delete items from two lists using Power Automate

Item in the List A

Conditionally delete items from two lists using Power Automate - Source List Demo
Conditionally delete items from two lists using Power Automate – Source List Demo

Item in the List B

Conditionally delete items from two lists using Power Automate - Target List Demo
Conditionally delete items from two lists using Power Automate – Target List Demo

Notes:

  • In the above two lists ListA & ListB has the same items – except the ID columns as ID cannot be same for the same item in the two lists.

Now, from the ListA, mark the item for “WantToDelete” flag as Yes.

Delete SharePoint list item based on another list using Power Automate Automatically
Delete SharePoint list item based on another list using Power Automate Automatically

Then, finally we could see that, both the items have been deleted from the two lists, ListA and ListB are empty now.

Automatically delete SharePoint list item based on another list using Power Automate
Automatically delete SharePoint list item based on another list using Power Automate

Summary: Delete SharePoint list item based on another list using Power Automate

Thus, in this article, we have learned how to delete SharePoint list item based on another list using Power Automate conditionally.

See Also: Power Automate Tutorial

You may also like the below Power Automate tutorials:

 

About Post Author

Do you have a better solution or question on this topic? Please leave a comment