Send an email when a duplicate value is found in a specific column

[BenBennett321]

Hi I've spent a long time trying to get this flow to work.

i want an email to be sent when a sharepoint list is updated, if there is a duplicate value in a specific column on the sharepoint list. (column : 'Account_x0020_number'). I want the email(s) to put the Title column and account number of the sharepoint list in the body of the email.

I've tried numerous ways of doing this, but nothing seems to work. another thing that complicates matters is there are null values in the column and ne null doesn't seem to work as a filter query for some reason. I suppose I need to do a filter array on this, but I can't get that to work either. 

Does anyone have any ideas on how to get this to work. 

Thanks,

[creativeopinion]

@BenBennett321 Have you considered toggling on Enforce unique values in your SP column. This will prevent duplicate values from being entered.

You might be interested in this YT Tutorial: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows

In this tutorial I cover:

✅ How to troubleshoot a false Condition action result

✅ How to get dynamic content when it isn’t selectable from the list of dynamic content

✅ How to troubleshoot an Apply to Each action that isn’t looping through

✅ How to troubleshoot a skipped Apply to Each action

✅ How to troubleshoot a Filter Query

✅ How to use a SharePoint yes/no column in a Filter Query

✅ How to use Compose actions to troubleshoot a Power Automate flow

✅ How to troubleshoot multiple emails being sent

✅ How to troubleshoot multiple Teams messages being sent

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! 

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[BenBennett321]

Hi Creative Opinion, thanks for that, but i only want to flag if there is a duplicate, I don't want to necessarily prevent them. I just need to do an additional check if there is a duplicate in that field. 

Thanks, 

[creativeopinion]

@BenBennett321 No worries. Tip: Remember to rename your actions to keep your flow organized!

Trigger Condition (Optional)

If you'd like to only trigger your flow when an item is modified—you can add a trigger condition to your flow. When a New Item is Created the Version is 1.0.

You can use that to differentiate between when an item is created or when it's modified.

To only trigger an item when it's modified, you'll need to compose an expression. First, add a Compose action to your flow and insert the Version number dynamic content from your flow trigger.

Click on the three dots and select Peek Code.

Copy the expression after the @ symbol and before the closing double quotes to your clipboard.

Click on the three dots of the trigger conditions and select Settings. 

Scroll down to the Trigger Conditions. You'll need to include an @ symbol at the start of a trigger condition expression. 

First insert the not() function.

In between the not() function insert the equals() function. 

In between the equals() function paste the expression from your clipboard.

Add a comma, then insert a 1.0 between single quotes. This expression will only trigger the flow if the version is not equal to 1.0 (aka the item was not just created).

Run a test. First, create a new item in your SP list. The flow should not trigger. Then modify an existing item. The flow should trigger. 

Get Changes for the SP Item

Add a Get changes for an item or a file (properties only) action to your flow. In the ID field, insert the ID dynamic content from the flow trigger. In the Since field, insert the Trigger Window Start Token dynamic content from the flow trigger.

You've indicated in your original post that you only want to send a notification a specific column has changed. To do this you'll need to check if the column has changed before continuing on with your flow. 

For testing purposes while building the flow, add a Compose action to your flow. Insert the Has Column Changed: [Your Account Number Column] dynamic content. For my SP List, it's the Has Column Changed: Custom ID dynamic content. 

Run a test. Make a change to the Account Number Column. Review the output of the Compose action. The Compose action should output true. 

Condition Check

Add a Condition action to your flow. Insert the output from the Compose action above into the first value field. This is why naming your actions is important. Especially when you have multiple instances of the same action. It makes it easy to identify which dynamic content belongs to which action. 

Leave the operator to is equal to. Enter true into the second value field. 

Add the rest of your actions into the YES branch. The flow will only perform those actions if the Account Number column has changed. If not, then nothing will happen.

Send an Email (V2)

Whenever you are building a flow that sends an Email or Teams message, always use your email address in the recipient field until you've confirmed your flow is working as expected. Once your flow is ready to go you can replace your email address with the email address of the actual recipient. 

Personally, I like to insert the email address of the actual recipient into the Subject line or Email/Message Body while I'm building and testing my flow. 

Compose your subject line and insert any dynamic content from your flow trigger if that suits your requirements.

Add any static text and dynamic content into the email body.

Run a test. Review the outputs of your email and ensure it's what your expecting. Adjust your email format until you are happy with it. Once you've confirmed everything looks good and your flow has been tested a few times. You can replace the recipient with the appropriate recipient.

If you are looking for a way to send multiple SharePoint List items that have been assigned to a user in an email—check out this YT Tutorial: How to Send a SINGLE EMAIL ✉️ with multiple SharePoint list items | Build THIS Power Automate Flow

In this tutorial I cover:

✅ How to send multiple list items in a single email with a Power Automate Flow

✅ How to create a dynamic date range

✅ How to use the Convert Time Zone action

✅ How to use a Filter Query in the Get Items action

✅ How to count number of items in an array

✅ How to use the Select action to extract a users display name and email address

✅ How to create a unique list of email addresses

✅ How to use the Create HTML Table action

✅ How to customize the HTML Table with CSS styles

✅ How to use the Send an email (V2) action

✅ How to use the Append to String Variable action

✅ How to create a custom list of items for an email

✅ How to use the Send an email (V2) action

✅ How to display singular or plural text based on the number of items returned

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️  Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[BenBennett321]

Thanks for this detailed response, but I can't see in your flow where it looks for duplicates? it seems to be only looking for those account numbers that have changed? 

[BenBennett321 · Accepted solution]

Anyway, I worked it out. 

When an item is modified on my sharepoint list it checks that the value in the specified column matches already on the sharepoint list, (using the filter array) .

The Select part just gets the certain columns from the list to identify the duplicates, turns it into a Html table which is put into the email.  The Compose action is just to add some CSS to make the html table more attractive.

Thank you to myself.