In SharePoint there is an out-of-the-box way to enforce uniqueness of fields.

Let’s say you have a SharePoint list with events and dates and by design you would like to allow any combination of events per day as long as they don’t have the same name and date/time at once.

According to the requirements, this is not allowed:

• Team Lunch – 12/29/2018 12:00 PM (same name and date/time)
• Team Lunch – 12/29/2018 12:00 PM (same name and date/time)

And these combinations are allowed:

• Team Lunch – 12/29/2018 12:00 PM (same name, different date/time)
• Team Lunch – 12/29/2018 2:00 PM (same name, different date/time)
  OR
• Team Lunch – 12/29/2018 12:00 PM (different name, same date/time)
• Vendor meeting – 12/29/2018 12:00 PM (different name, same date/time)

If you make both Event and Date fields unique then you won’t be able to achieve that.

Would be great if we could create and use a calculated column (event + date combined); however, it’s not possible to check uniqueness of calculated columns.

Fortunately, a Microsoft Flow can be created that handles uniqueness verification once a list item is submitted.

What this workflow does in a few words – it runs when an list item is submitted, then it tries to find items using a Filter Query (items that have an exact match for the same event name and date/time), if it finds anything and it’s more than 1 (2,3,4, etc), then it considers it a duplicate which is then deleted and a user is notified.

Note, a Greater Than 1 is used for a purpose. When you submit a new list item, the flow finds the very item itself. So it’s 1 found already. If there is another one in the list already, then it will be 2 found.

The Filter Query for Get Items:

Title eq 'triggerBody()['Title']' and TEDate eq 'convertFromUtc(triggerBody()?['TEDate'],'Eastern Standard Time','MM/dd/yyyy h:mm tt')'

The Condition formula:

@greater(length(body('Get_items')?['value']), int('1'))

RESULT

Let’s test the code by adding events, that should be allowed:

When trying to add an event that would be a duplicate: