Data - How to enforce unique values (or prevent duplicate values) in one or more columns
A common task is to enforce unique values (or to prevent duplicate records). To demonstrate, this post takes the example of a SharePoint list of employee details. The details in this list include include name, surname, email address, and other employee details.
As a starting point for this post, we'll build an auto generated app that's based on this SharePoint list. As this post progresses, we'll walk through how to enforce unique email addresses, and a unique combination of firstname and surname values for each record in the list.
As a starting point for this post, we'll build an auto generated app that's based on this SharePoint list. As this post progresses, we'll walk through how to enforce unique email addresses, and a unique combination of firstname and surname values for each record in the list.
Best practice - Enforcing uniqueness at the data source
Whenever we want to avoid duplicates, a best practice is to enforce uniqueness at the data source level, in addition to any checks that we make in Power Apps. This prevents users from creating duplicating records outside of Power Apps.
To carry out this task in SharePoint, we select the settings of a column and click the 'enforce unqiue values' radio button.
A notable limitation of SharePoint is that it's not possible to define a unique constraint that spans multiple columns. However, SQL Server and Dataverse databases do not have this limitation. In SQL Server, we can create unique constraints and with Dataverse, we can define alternate keys to specify uniqueness across multiple columns.
How Power Apps deals with duplicate values
When we define unique columns at the data source, Power Apps displays an error message when a user attempts to add a duplicate record.
As an example, here's the error that appears when a user attempts to create or update a record that contains a duplicate email address.
Although the SharePoint has prevented the user from creating a duplicate value, there are two issues with this built in behaviour. The first is the error message is not user friendly, and the second is that the form clears the duplicate value that the user enters before displaying the error.
Detecting duplicate values from Power Apps when adding a record
To better handle this behaviour, we can write formula to detect the presense of a duplicate record before the call to SubmitForm (or Patch).
The formula beneath highlights the basic syntax that we can add to the submit button to detect a duplicate email address when a user attempts to create a new record.:
If(IsBlank(LookUp(Employee, Email=DataCardValue17.Text)),
SubmitForm(EditForm1),
Notify("Email must be unique",NotificationType.Error);
SetFocus(DataCardValue17)
)
Otherwise, the formula calls the Notify function to alert the user. Next, it calls the SetFocus function to set the focus to the email text input control.
The screenshot beneath highlights the notification that appears when the user enters a duplicate email address.
Enforcing unqiueness accross multiple columns
To enforce uniqueness across multiple columns, we can adapt our formula to reference the multiple columns in the call to LookUp. Here's the syntax to enforce a unique combination of firstname and surname for each record in the employee list.
If(IsBlank(LookUp(Employee, Firstname=DataCardValue11.Text
And Surname=DataCardValue19.Text))
,
SubmitForm(EditForm1),
Notify("Error - duplicate values detected",NotificationType.Error)
)
Similar to our previous formula, DataCardValue11 refers to the text input control for the first name, and DataCardValue11 refers to the text input control for the surname.
To include multiple rules, we can extend the If condition. As an example, here's the syntax to enforce both a unique email address, and a unique combination of firstname and surname for each record.
If(IsBlank(LookUp(Employee, Email=DataCardValue17.Text))
And
IsBlank(LookUp(Employee, Firstname=DataCardValue11.Text
And Surname=DataCardValue19.Text))
,
SubmitForm(EditForm1),
Notify("Error - duplicate values detected",NotificationType.Error)
)
Detecting duplicate values from Power Apps when creating and editing a record
Finally, it's important to note that when we edit a record, we must prevent the formula from detecting the current record as a duplicate.
With a SharePoint list, the ID field uniquely identifies each record. We can use this to identify whether any duplicate record is genuinely a duplicate, rather than the record that matches the existing record.
With a SharePoint list, the ID field uniquely identifies each record. We can use this to identify whether any duplicate record is genuinely a duplicate, rather than the record that matches the existing record.
To access the SharePoint ID value on a form, we can add a label to the form and set the text property to ThisItem.ID. To hide this label from user, we can set the visible property to false.
To check whether the user is entering a duplicate record, we can retrieve all records from the SharePoint list that match the first name, surname, and email address that user enters. If no matches are found, the record is valid. If a single match is found and the ID matches the ID of the current record, the record is also valid.
Here's the formula that carries out this logic.
Here's the formula that carries out this logic.
With({matchingRecords:Filter(Employee, Email=EditForm1.Updates.Email Or
(Firstname = EditForm1.Updates.Firstname And
Surname = EditForm1.Updates.Surname))}
,
If(IsEmpty(matchingRecords) Or
(CountRows(matchingRecords) = 1 And
First(matchingRecords).ID = Value(lblID.Text)),
SubmitForm(EditForm1),
Notify("Error - duplicate values detected",NotificationType.Error)
)
)
To simplify the formula, note how we refer to the values that the user enters by referencing EditForm1.Updates, as opposed to the text input controls.
When a user now runs our app, this formula will prevent the user from entering a duplicate email address, and a duplicate combination of firstname and surname whilst creating and editing existing records.
Conclusion
A common requirement is to enforce unique values, or to prevent duplicate values in a column (or set of columns).
This post walked through how to accomplish this task by demonstrating how
to enforce unique email address values in an employee table, and how to
enforce a unique combination of firstname and surname for each record.
