How to avoid Duplicates of a combination of columns in SharePoint Lists

Question

I have a SharePoint List that contains 3 crucial columns:

1. [Application] - a lookup column from another table called 'Applications'
2. [Stakeholder] - a person or group data type
3. [Role] - a choice column

I would like to avoid duplicates of all 3 columns combined.

---

For example, an [Application] can have multiple Stakeholders, so [Application] by itself can be duplicated. Each [Stakeholder] can have multiple roles within an application, so [Stakeholder] and [Application] can be duplicated. There can also be multiple stakeholders within an application with the same role, so [Application] and [Role] can be duplicated.

Here is an example of accepted values:

| Application | Stakeholder | Role |
| VS | John Smith | Developer |
| VS | John Smith | BA |
| VS | Tim Brown | Developer |
| Red | Tim Brown | Developer |

I want to capture or alert users when they input the same combination of [Application], [Stakeholder], and [Role].

I've tried creating some Flows that captures the ID of each application in plain text, a flow that captures the stakeholder in plain text, and created a Role ID column with some IFS. I then made a flow to CONCAT each column [Application ID] + [Stakeholder] + [Role ID] and tried making the column unique, but I am not quite getting the results I want.

Answer 1

I don't think there is any way to do this BEFORE users actually create the item. That said, I think you could alert users that it happened or even alert the user AND delete the item, or even alert the user and hide the new item.

First, I would recommend doing all this in a single flow. It sounds like you may have been intending to use separate flows to get the various columns. I would recommend starting with a trigger for when an item is created or modified. Then, have an action that gets all the current items. It sounds like you already figured out how to concat [Application ID] + [Stakeholder] + [Role ID]. After you do that for all current list items, and separately do that for your new item, you should be able to filter the concatenated list with the concatenated new item. If your filter results are more than 1* item long, then there is a duplicate.

The question is what to do then. You could just alert users with an email. Or , you could create a new column in the SharePoint list to hold some sort of flag. For instance, it could be a Yes/No column without a required value with no default value. Then, if you remove it from the user entry view, users won't fill it out. Your flow could then update it to be Yes for this entry is ok and No for this entry is not ok and needs to be deleted ...or your flow could just delete the item in question.

• Remember that, when you get the current list of items, the newly created item will be on that list, so the newly created item will be returned in your filter results. That is why a filter results length more than 1 means a duplicate.