Managing large lists is a hassle in SharePoint, and duplicate entries in lists can often lead to several issues.
Are you facing issues with duplicate entries in SharePoint? Well, this article will show you the methods to prevent and remove duplicate entries differently.
Let’s get started!
To prevent SharePoint lists and libraries from duplicate entries, you have to turn on the enforce unique values feature while creating a new column or working on an existing one. Simply select Add column, select More options, toggle on the enforce unique values feature, and click Save.
So, What is the enforce unique values feature, and how does it work?
Under the SharePoint metadata column, the enforce unique values is a feature or setting. While creating a new metadata column, users can enable enforce unique values by checking off the appropriate radio button. By default, the enforce unique values feature is turned off.
Enabling the feature ensures users can choose a value only once for a particular field in the entire Document library or a SharePoint list. It assures users cannot add duplicate values within that column.
You cannot enable and use the feature on a Title field in a SharePoint document library. Check the following instructions to enable the enforce unique values feature on a metadata column.
Here are the steps to avoid duplicate entries in SharePoint lists and libraries:
- Open your SharePoint site and click the List.
- Select Add column under the list name.
- Choose a column type and click Next.
- Select More options and locate Enforce unique values.
- Toggle on the Enforce unique values and click Save.
You can also turn on the Enforce unique values setting for your existing Title Columns in the SharePoint list. Click on the existing column, navigate to Column settings > Edit, and then turn on the Enforce unique values.
If you use Powerapps to add entries to a SharePoint list, you can add a submit button to avoid duplicates. All you have to do is change the OnSelect property of the submit button. It will ensure the submit button is enabled or disabled depending on the existing records.
I will use the LookUp feature to check if the context is available or not. If the Poweapps find the context in your list, the submit button will be disabled.
Contrarily, if the app doesn’t find any existing record against your new entry, the submit button will be available, and you can add new entries.
To use a lookup to find duplicate entries, paste the following code in the OnSelect property of the button.
If(LookUp(MyList, Title = TextInput1.Text, ID) = Blank() , DisplayMode.Edit,Disabled)Want to add something like !Blank() to ensure black fields are also counted? You should switch your two branches inside the if around or simply copy and paste the following code in the OnSelect property of the button.
If(!(LookUp(MyList, Title = TextInput1.Text, ID) = Blank()) , DisplayMode.Edit,Disabled)Suppose you want to prevent duplicate SharePoint list entries on two or multiple columns. In that case, you should use a jQuery code because the enforce unique values feature won’t work for multiple columns.
Enforcing Uniqueness in Column Values ensures it will prevent the same data from emerging under one column.
However, it won’t relate to other columns or make them unique. If you need to make multiple columns unique (two or three), use the REST API and jQuery code.
Copy the following code and use it for your columns:
<script type="text/javascript" src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.3.min.js"></script>
<script type="text/javascript">
//variable to hold Ajax result
var dataResults;
$(function(){
//adding javascript to button onclick event to call checkExist() function
var oc = $("input[value='Save']")[1].getAttribute("onclick");
$("input[value='Save']").attr('onclick','if (checkExist()){ alert("Already Exist"); }else{'+ oc +'}');
//Ajax to populate data from Employees list
$.ajax({
url: _spPageContextInfo.webAbsoluteUrl +"/_api/web/lists/getbytitle('HCM')/items",
type: "GET",
async: false,
headers: { "Accept": "application/json;odata=verbose" },
success: function(data) {
dataResults = data.d;
}
});
});
//function to compare user input and existing data
function checkExist(){
var Region =$("select[title='Region']").find("option:selected").val();
var Subsidiary = $("select[title='Subsidiary']").find("option:selected").val();
var Period = $("select[title='Period']").find("option:selected").text();
var c = false;
$.each(dataResults, function(i, item){
$.each(item, function(i, dt){
console.log(dt.RegionId + ' ' + dt.SubsidiaryId + ' ' + dt.Period );
if (dt.RegionId == Region && dt.SubsidiaryId == Subsidiary && dt.Period == Period ){
c = true;
return c;
}
});
});
return c;
}
</script>After enabling the above code, SharePoint will show a pop-up notification showing Already Exist message whenever someone tries to insert a duplicate input.
Using Power Automate, you can quickly remove duplicate entries from your SharePoint list. To do this: create a new flow, use the Compose option to get all the items, find the duplicates using the Filter array, and then remove the duplicate items using the item()?[‘ID’] expression.
For step-by-step instructions, consider the subsequent procedures.
Here’s how to delete duplicates from a SharePoint list using Power Automate:
1. Create a New Flow
- Go to Power Automate.
- Click Create and choose Automated cloud flow.
- Enter the Flow name, choose When a file is created or modified, and click Create.
- Add the Site Address and List name, and click Create.
- Search Get items and add it from Actions.
- Add all the properties. Next to the Order by properties, type Modified desc.
2. Get All the Items
- Search Select and add it from Actions.
- Add value in the From properties and Title in the Map properties.
- Click New step and add Compose.
- Add union(body(‘Select’),body(‘Select’)) in the Input properties.
3. Find the Duplicate Items
- Create a New step, search & choose Apply to each.
- Use a Filter array to find the duplicates.
4. Remove the Duplicates
- Create a New step, search & choose Apply to each.
- Use the skip(body(‘Filter_array’),1) expression to skip the first items.
- Delete the remaining items using their ID: item()?[‘ID’].
After performing all the above steps, all your duplicates will be removed from your SharePoint list.
Final Thoughts
SharePoint duplicate entries can lead to different unexpected situations and waste your valuable time. It’s essential to prevent duplicate entries while working with a team.
I have sowed you three different methods to prevent adding duplicate entries to your SharePoint list. Moreover, I have added the procedure to remove already existing duplicate entries.
For further queries, leave a comment below.
