Set Unique value based on the (CreatedDate & CreatedBy) + only allow user to only see the items they have created except for a group of admins

Question

We want to create a SharePoint online custom list with these business logic:-

1. Users enter an entry each day to specify their location (which site they are working in)..
2. No one can see other entries, except for an admin group of users.
3. No one can submit twice per day, in other words, the list should enforce a uniqueness based on the CreatedDate + CreatedBy.

So I am planning to follow this approach:-

First step, is to create a custom list, then check these checkboxes:-

Second Step:- then create a SharePoint group , named "View/Edit all items", and create a custom permission level which is a copy of the built-in contribute, with this checkbox checked:-

so based on my knowledge, this should cover part of the requirements, for allowing users to only see and edit their own items + allow a group of users who are assigned the new custom permission level to view/edit all items.

But what abut preventing the same user from adding 2 entries inside the custom list twice on the same day? i know that using remote event receivers which runs on pre-add and pre-update events are no longer valid and no longer a future proof approach.. so the only option i can think of, is to have a power automate flow that runs when an item is created >> where the flow will get any item which was created on the same day and by the same user (of course excluding the current item), if so to send an email to the user that he added duplicate items + to force deleting the item.. but this will not be a 100% enforce uniqueness but rather a way to inform the user about a duplicate.. so is there a better approach to enforce uniqueness in our case, using calculated columns in a way or another?

Also using power automate, i am not sure if this will cover a scenario such as where the flow detect a duplicate >> delete the item >> but then the user restore the item from the recycle bin?

Thanks

Answer 1

Hello. I don't know how to create a unique index using multiple columns, but I was able to create a Flow that checks if the newly created item is a duplicate and simply delete the old version without sending any email to the user. However, I just tested your question about restoring the deleted items from the Recycle bin, and it seems that is possible, which would mean you could end up having duplicates if the users restore items from the Recycle bin.