Enforce unique values in Power Apps in User Email column

[Becca_Hayes]

Hello,

I've seen some similar topic posts on what I'm about to ask, and have tried using them for my own purposes, but unfortunately to no avail. Most likely due to my own inexperience with Power Apps.

Anyway, I'm needing a way to enforce unique values for a User Email column on the Power Apps side, but not the SharePoint list itself. I need to be able to make it so users can't put their email in for more than one item in the Power App but can from the SharePoint list side. Is this possible and if not, any suggestions for how I might go about getting a similar result?

TIA!

Also, for reference on why I need it set up this way: I essentially need 2 different forms of input for the SharePoint list because all but one group of the users that will be inputting to this list need to be restricted to only having their email allotted for one item at a time. So, this one group will be the only ones that will be allowed to have multiple entries under their one email. My thinking for this would be that all the other users would be given the Power App for inputting purposes while the ones allowed multiple entries would have sole access to the list itself.

And for clarification, I do not need each user to be restricted to one entry total, because some users may be inputting items for other users and listing said other users' email for that item. In other words, one user could have several entries they created but each one still has a unique email. I started another topic question with a different approach to this issue which I will link below if anyone is interested. I started this new post just to expand my options. Thanks again.

Re: Enforce Unique Values for Every User Except On... - Power Platform Community (microsoft.com)

[poweractivate]

@Becca_Hayes 

Yes it's possible @Becca_Hayes 

Let me tell you the difference between using the SharePoint List way and not using it, to enforce uniqueness.

To enforce unique values in a SharePoint list:

1. Go to your SharePoint List and then, click on the name of the column that you want to enforce unique values on (in this case, it's the User Email column).

2. Click on "Column settings" and then "Edit".

3. In the column settings, you should find a checkbox saying "Enforce unique values".

Check it and save your changes.

This should enforce unique values for that column in the SharePoint list, meaning no two items in the list can have the same value in this column.

However, you've mentioned that you do not want to enforce this uniqueness constraint on the SharePoint list itself as there's one group of users who should be able to enter multiple items with the same email. This is why enforcing the constraint on the Power Apps side, not on the SharePoint list itself, would be more suitable for your scenario.

Enforcing unique values in Power Apps:

1. In your Power App, select the 'Submit' button that users use to submit the form.

2. In the OnSelect property of the button, write a formula to check whether the email a user entered already exists in the SharePoint list. If it does, show an error message and prevent the form from being submitted:

If(
IsBlank(
LookUp(
YourSharePointList,
EmailColumnName = TextInput1.Text
)
),
SubmitForm(YourFormName), // submit the form if email is unique
Notify("This email has already been used. Please enter a different email.", NotificationType.Error)
)

This formula uses the IsBlank function to check if the result of the LookUp function returns a record (i.e., no item in the SharePoint list has the same email). If it is blank, the formula submits the form. If it's not, the formula shows a notification to the user.

Remember to replace 'YourSharePointList', 'EmailColumnName', 'TextInput1', and 'YourFormName' with your actual SharePoint list name, the email column name, the actual TextInput control where users input their email, and the actual form name, respectively.

With this approach, you're implementing the uniqueness constraint only in your Power App, not in your SharePoint list. Users who use the Power App to submit data will be constrained by this rule, but users who have access to the SharePoint list itself will not be constrained.

Hope it helps @Becca_Hayes 

[Becca_Hayes]

@poweractivate 

Thank you for such a detailed reply! I definitely think this is a step in the right direction as the formula is actually more complete than my previous attempts. However, something still seems to be wrong. I tried plugging in the formula you gave and when I try to submit the form to test it, it doesn't seem to do anything. No loading or any indication that the item is processing, or even getting the error message that is supposed to appear when the email is a duplicate.

I applied all this to OnSelect property of the submit button of the form as you stated and made sure to replace all the filler text of the formula with my own information. I have attached below what the formula looks like for me currently if you wouldn't mind taking a look. The only information that isn't shown is my list name as it needs to remain private.

As you can see in the photo, the .Text portion is underlined in red, but I have had this happen on another App and the formula for that one still worked, so not sure if this is part of the issue. I would like to note in case it makes a difference that the User Email column is a person lookup, not just a plain text column. If this is the issue, then I can just change it to a text column if that is the quickest solution.

[poweractivate]

@Becca_Hayes 

From what you've described, the issue could indeed be related to the 'User Email' column being a person lookup. When you're dealing with a person lookup column, you have to reference it slightly differently in the LookUp function because the data structure of a person lookup is different from that of a simple text field.

Here is a modification to the formula that accounts for the person lookup:

If(
   IsBlank(
      LookUp(
         redacted,
         'User_x0020_Email'.Email = DataCardValue11.Text
      )
   ),
   SubmitForm(EditForm), // submit the form if email is unique
   Notify("This email has already been used. Please enter a different email.",NotificationType.Error)
)

Here, 'User_x0020_Email'.Email is used instead of User_x0020_Email.

This is because a person lookup column is a record that contains several fields, one of which is 'Email'.

So, you actually need to specify that you're checking the 'Email' field of the 'User Email' column.

I hope this helps, @Becca_Hayes 

[poweractivate]

@Becca_Hayes 

You may alternatively change it to a text field if it is simpler for you as well, but you may also check my previous response to see if you can get it to work with your Person lookup column.

[Becca_Hayes]

@poweractivate 

It still doesn't seem to want to work. I tried just creating a text column for the email and used the original formula you gave me, and it worked, so I may have to just go that route for now. It isn't ideal though because the organization I work for is big and it may be troublesome to have to manually type in everyone's emails.

If you have any other ideas to try to get the person lookup column to work, I'd be very grateful. If not, no problem, you have already been a huge help to me, and I at least have some direction to go in for now.

Thank you.

[poweractivate]

@Becca_Hayes 

Based on this, it sounds like the issue may still be with correctly referencing the 'User Email' person lookup column. There are a few things that might be going wrong:

1. Incorrect Column Reference: Ensure that the 'User_x0020_Email'.Email is the correct way to reference the email field in your person lookup column. The names used to reference columns and fields in Power Apps are sometimes different than the names displayed in the SharePoint list. You can check this in the app by using the formula: First(redacted).'User Email'. This should return a record with the data structure of the 'User Email' column.

2. Data Types: Ensure that the data you're comparing is of the same type. For example, if 'User_x0020_Email'.Email is a text field, ensure that DataCardValue11 is also a text field where DataCardValue11.Text really gives you text. Power Apps is case-sensitive and type-specific, so make sure your comparisons match in both case and data type.

3. Presence of Email: Make sure that every person in the 'User Email' column has an email associated with them. If some of the entries are blank or have invalid emails, this could potentially cause issues with the lookup.

I hope this helps you @Becca_Hayes 

[Becca_Hayes]

As far as I know, the column referencing is all correct. And the people I'm using for testing are on my team, so I know they have proper emails associated with their lookup profiles.

So, I'm assuming based on what you've said that it would be the data types that is the issue, then. The User Email column is a Person or Group lookup in the SharePoint list and the input field (DataCardValue11) in the Power App is a combo box, by default. So, not sure if that's a proper match-up, but I would've assumed the combo box by design is meant for things like person or group fields so not sure that's really the issue. Not sure if this is what you mean by the data types, either, but I tried playing around with the settings of the combo box anyway to see if that would help and so far, nothing.