Unique Bio

[Billie Kjergaard]

Manycommentators have objected to the comparison or modification (as by somewhat or very) of unique, often asserting that a thing is either unique or it is not. Objections are based chiefly on the assumption that unique has but a single absolute sense, an assumption contradicted by information readily available in a dictionary. Unique dates back to the 17th century but was little used until the end of the 18th when, according to the Oxford English Dictionary, it was reacquired from French. H. J. Todd entered it as a foreign word in his edition (1818) of Johnson's Dictionary, characterizing it as "affected and useless." Around the middle of the 19th century it ceased to be considered foreign and came into considerable popular use. With popular use came a broadening of application beyond the original two meanings (here numbered senses 1 and 2a). In modern use both comparison and modification are widespread and standard but are confined to the extended senses 2b and 3. When sense 1 or sense 2a is intended, unique is used without qualifying modifiers.

When i look at how the emails are tracking I see "unique opens' then "Total Opens". Total opens is always higher than unique opens but I can not tell who opened the total opens, only the unique opens. I am not seeing in some cases what looks to appear as twice as many opens and no email adrress. What is the differance?

Total Opens is the total number of times your email has been opened. If you sent your email to me and I opened it six times, this would be six total opens; one unique open.

If you sent the same email to six separate individuals, each of whom opened it once, this would be six total opens; six unique opens.

I think it is crazy. I might have 20 unique opens then I see 50 + opens. I dont think the info that I am sending or selling is so important that some unique opener passes it on to 50 others. But hey more the marry er that see my email. Just wish I knew who sent it to so many people. Thats a good person and want to know who it is.

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?

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.

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.

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.

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.

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.

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.

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.

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.

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:

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.

You cannot get a lookup field to display only unique values. However, you can get a rollup field to show only unique values. Rollup fields are very similar to lookup fields, except that you can use formulas and you loose some formatting.

Edit 1: To answer your bonus question, the optimal way to test a set of GUIDs for uniqueness is to just assume that they are all are unique. Why? Because, given the number of GUIDs you're generating, the odds of a GUID collision are smaller than the odds of a cosmic ray flipping a bit in your computer's memory and screwing up the answer given by any "accurate" algorithm you'd care to run. (See this StackOverflow answer for the math.)

I have calculated a few representative collision probabilities. With 122-bit UUIDs as specified in the Wikipedia article, the probability of collision is 1/2 if you generate at least 2.71492e18 UUIDs. With 10^19 UUIDs, the probability is 0.999918. With 10^17 UUIDs, 0.000939953.

Some numbers for comparison can be found on Wikipedia. So you can safely assign a UUID for each human that has lived, each galaxy in the observable universe, each fish in the ocean, and each individual ant on Earth. However, collisions are almost certain if you generate a UUID for each transistor humanity produces in a year, each insect on Earth, each grain of sand on Earth, each star in the observable universe, or anything larger.

3a8082e126