How to Set up One Item / Record Per User in Sharepoint List and Power App

[Lijun_Chen]

Hello you all.

I am quite new to Power Apps. Recently I am tasked to build an Power App form tool that collect current staff members' highest degree, certificates, years of experience in certain work fields, and a bunch of other qualifications. The process is for each staff member in my organization to use the tool to add a new record for their information and later upgrade their own record as their situations change. All item records from the App form will be captured / connected to a SharePoint List file. 

I have several questions I would like to have your suggestion/advice. 

1. We need to restrict one use to have only one item/record, so there is no duplicate records for any user. Can I set this up in SharePoint, like set user's name/email as unique value ('person' field type from 365 User AD profile)? 

2. We want each user to update/edit their own records instead of others' records. I think this can be set up in SharePoint's item settings. Will this restriction automatically apply in the Power App tool? 

3. If a user left the organization, I would like the SharePoint List and App to automatically exclude/delete their items / records. How can this be achieved, either in SharePoint list or Power App? 

4. I would like a user who just joined our organization/have an email account to be automatically notified (through) to fill in the form? Is this possible to set up in the SharePoint List? 

Any suggestions/advice would be appreciated. Thanks.

[FLMike]

Hello,

Please see responses below.

1. We need to restrict one use to have only one item/record, so there is no duplicate records for any user. Can I set this up in SharePoint, like set user's name/email as unique value ('person' field type from 365 User AD profile)?

-Michael: Yes absolutely, in the Canvas App you can specifically pull up the currently logged in users Email/Information based on the User() function.

Then just make sure that ALL Patch statements (updates to the list) have a filter that essentially says "Where Email = User().XXX whether its user name or email etc.

2. We want each user to update/edit their own records instead of others' records. I think this can be set up in SharePoint's item settings. Will this restriction automatically apply in the Power App tool? 

-Michael: I am going to answer and then suggest what you want to do. Yes, if they only have access to 1 row based on security, then when they query they will only get that row or none, or an error based on what you allow them to do.

But you should make sure that in any UI, just as with #1 Patching/Updating, you should ONLY query rows based on the Column User Email or whatever you decide so it's always just that row.

If you have another list that is linked to the Primary, and it has multiple rows, just make sure to only query the ones they can see.

3. If a user left the organization, I would like the SharePoint List and App to automatically exclude/delete their items / records. How can this be achieved, either in SharePoint list or Power App? 

How. Thats not possible. Let me explain. SharePoint can't do that itself, neither can a Power App, but you could do it with a Power Automate flow. You'd have to either have a reoccurring job that runs every night, looking for people to update, is probably the best way. Easy to do, just not with the previous 2 you asked about.

4. I would like a user who just joined our organization/have an email account to be automatically notified (through) to fill in the form? Is this possible to set up in the SharePoint List? 

Hi, again SharePoint won't, but you could use Power Automate to look for new people and notify them sure.

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[Lijun_Chen]

Hi Michael,

Thanks for your very helpful advice. I have little experience with setting flows with power Automate, especially with the Office 365 User directory. I think another way may be to generate an dynamic sharepoint list of active users from the Office 365 User directory. Then the sharepoint can be used as a lookup. Do you have an idea how to create a Sharepoint List of the active users? 

[FLMike]

Hello,

While you could essentially replicate the AAD to SharePoint I don't think it's a good idea. It's too easy for it to be out of sync.

And I have no idea how many users you have.

But let's say that you since you know how to query the users using Power Automate you would do the following.

Step 1: Create a list in SharePoint. If you don't know how to create a list. 

Set up your SharePoint site with lists and libraries | Microsoft Learn

Step 2: In Power Automate, still create a scheduled flow

Step 3: Retrieve your list of users (you have to watch out for Delegation and it may not give you all users, either up to 999 or 2,000 based on the connector limits)

Step 4. Create an Apply to Each to loop through Step 3.

Step 5. Use the Get Items (SharePoint) , with a Filter (in the Action) of the User Name or Email from Step 4, set your Top Count to 1

Step 6. Condition Action

-Check if you receive a row or NOT. You do this by checking the length(the get items Step 5 body). You are checking the array length

Step 7a. If NO you didn't find one, then in the no branch

-use the Create Item (SharePoint)

-no more steps just keep looping

Step 7b. If Yes you did find one then in the Yes branch

-Use either an Apply to Each to loop the single record to get the SharePoint ID, or you can use a Compose action to directly read the ID from the array at the 0 index of the array

Step 8(continued from 7b)

-Use the Update Item to update the row using the ID you got from 7B for that row

Please let me know if you have any questions.

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey