Forum Discussion
Set Unique value based on the (CreatedDate & CreatedBy)
We want to create a SharePoint online custom list with these business logic:-
- Users enter an entry each day to specify their location (which site they are working in)..
- No one can see other entries, except for an admin group of users.
- 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
I wouldn't let users anywhere near the list at all (although they would have to have contribute permissions on it). I would build an app in Power Apps with the SharePoint list as the datasource. Also add the Office365Users connector. When the app starts get the items submitted by the current logged in user and display those in a gallery. Then have a form on the same screen where new items can be submitted but, by setting the DisplayMode of the "Submit" button it won't allow the button to be clicked if that date already exists in the gallery. The app can be used on a smartphone, tablet or laptop. Let me know if you would like an example of this to be posted up.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)but we need to keep in mind that Power apps will still allow the users to modify the list through API or using the SharePoint list view.. i were implementing those requirements using pre-edit and pre-add remote event receivers, but unfortunelty we no longer have those options, to run server-side code on pre-edit and pre-add
No, Power Apps doesn't allow that., certainly not for end users who are not co-owners. If users don't know the site the list is on then it will be highly unlikely that they will find it. So they won't see the list view and they won't know what to put in an HTTP request in a flow or some other api. With 70,000 staff I have never yet had a case where a list we have buried somewhere has been found and changed. It's a list to report the site a user is on, not the codes for nuclear missile launches, so I don't see why staff would want to waste time looking for the list then trying to change entries on it? Don't you trust your staff just to fill it in?
My bowls match was cancelled today so I spent an hour building a fully-working example of the app. Although you'll see that there are other users who have completed entries, only my entries are shown in the app and if the gallery already has a date for me then it won't allow me to save a existing or new item with that date. And there is no way that a user can guess what the site or list has been used as the datasource.Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)
Don't forget the custom content type to the list. Because if the users actually find the list, they might go around the Power App.
but custom content type has nothing to do with this... at the end i can customize the list form to show blank page, so users will have to use Power Apps,, but still users can modify the list using the API.. my question is about having a robust solution similar to using remove event receivers which run on pre-add and pre-edit ,, but those are no longer valid options
Hello,
I suggest that you create a custom content type for your data within the list.
Edit the +New button on the list to not allow creation of that new content type (preventing users to create items from the list)
Create a Power App that allows users to add data to the list (with the right content type).
Add functionality to the Power App to see if the current user has an item with the same date as today.
If yes, add custom status message and disable the create button.
If no, allow the user to add the information and save to the list.Yours sincerely,
Aref HalmstrandIf this was helpful, please mark as resolved 🙂
Feel free to like this post as well 🤩
but this will not prevent users from modifying the list through the api or using their own power apps
You are over-thinking this. Why on earth would users build their own app to change a list they don't know the address of, and why would they be using an api? You are making this so much harder than it needs to be.
