How to Prevent Duplicate Date and Desk Entries in Microsoft Lists
Question details
The user wants to prevent duplicate records for a combined date and desk selection in Microsoft Lists.
- Product
- Microsoft Lists
- Device & OS
- not provided
- Scenario
- Managing desk bookings or scheduling where a specific desk on a specific date must remain a unique combination.
- Observed behavior
- Microsoft Lists natively allows enforcing unique values on a single column, but lacks built-in functionality to enforce uniqueness across a combination of two or more columns.
Ensure you have full edit permissions for your Microsoft List and access to Microsoft Power Apps within your Microsoft 365 environment to customize the submission form.
Use Power Apps to Validate Combined Date and Desk Fields
Since Microsoft Lists cannot natively enforce uniqueness across two columns, you must customize the list form with Power Apps to validate the date and desk combination before the item is saved.
This method intercepts the form submission process and checks the existing Microsoft List records to see if the chosen date and desk combination already exists.
Open your Microsoft List. In the top command bar, click on 'Integrate', select 'Power Apps', and then click 'Customize forms'.
Once Power Apps loads, locate the button or icon used to save the form (often a checkmark icon or a 'Save' button) and select it.
In the formula bar for the OnSelect property, write a Power Fx formula using the LookUp or Filter function to query the list. Check if a record exists where the Date column matches the selected Date and the Desk column matches the selected Desk.
Wrap your form submission function in an If statement. If the LookUp finds a match, trigger an error using Notify('This desk is already booked on this date.', NotificationType.Error). If no match is found, execute the SubmitForm(SharePointForm1) function.
Click 'File', then 'Save', and finally click 'Publish to SharePoint'. Return to your list and test the form by attempting to submit a duplicate entry.
Enforce Unique Values on a Single Column
If your workflow changes and you only need to prevent duplicates in one specific field (such as an Employee ID or a specific Booking ID), use the built-in List Settings.
Easily Manage Schedules and Prevent Duplicates with WPS Spreadsheet
If you are managing desk bookings, schedules, or rosters and prefer a flexible, offline-friendly spreadsheet environment, WPS Office is a robust alternative. With WPS Spreadsheet, you can easily use Data Validation formulas to prevent duplicate combinations without writing complex Power Apps code.
- 1. Download and Install WPS Office: Get WPS Office for free from the official website and open WPS Spreadsheet.
- 2. Set up your tracker: Create a new booking tracker or open your existing Microsoft Excel booking file (.xlsx) directly in WPS Spreadsheet.
- 3. Apply Data Validation: Select your input columns, navigate to the 'Data' tab, click 'Data Validation', and apply a custom formula to block duplicate entries.
Frequently Asked Questions
Can Microsoft Lists natively prevent duplicate entries across multiple columns?
No, Microsoft Lists only supports enforcing unique values for single columns natively. To enforce uniqueness across a combination of columns (like Date and Desk), you must use Microsoft Power Apps for form validation.
Why is the 'Enforce unique values' option grayed out in List Settings?
This option may be disabled if the column type does not support unique values (such as multi-line text or choice columns that allow multiple selections), or if the list already contains duplicate values in that specific column. You must remove existing duplicates first.
Does enforcing unique values apply to items edited in the Grid view?
Yes, if a single column has 'Enforce unique values' enabled in List Settings, users will receive a validation error if they try to enter a duplicate value, even when editing the list quickly in Grid view.
