Case-sensitive check of USEREMAIL() against list

AppSheet AppSheet Q&A
,
1

Recently I came across a thread related to checking USEREMAIL() against a list of email records, and it was mentioned that the check may fail due to difference between uppercase and lowercase characters.

Has anybody dealt with this issue before? Is there a robust solution? My opinion is that it would be helpful a list-expression that converts into LOWER-case or UPPER-case all the text records in the list. But it doesn’t seem to exist. Alternatively, one should implement an hack like forcing user inputed emails to be lowercase or uppercase, but I’d prefer the first solution.

Using such a function, I could write:

IN(LOWER(USEREMAIL()), LOWER(SELECT(Users[Email]), [Role] = "Manager")))

Any ideas? Should I raise a feature request?

2

perissf:

My opinion is that it would be helpful a list-expression that converts into LOWER-case or UPPER-case all the text records in the list. But it doesn’t seem to exist.

Maybe you are looking for something else. In general, the following expression will create a list with all lower case elements?

SPLIT(LOWER(Table Name[Text Column with Upper Lower Case Contents]), " , "))

1 Like
3

Thanks for your answer. However I set up a test-case, and unfortunately I haven’t been able yet to reproduce the fail of my formula (which was only hypothetical). I am investigating further…

4

Could you please elaborate on what you are trying to achieve with the expression? I mean where you would like this expression to use? In the valid if , for example?

5

Correct, I am using it in the valid if expression

6

Thank you. So, if user enters a mixed case email, then it should be invalidated ? And it needs to be validated only if he enters the email either in upper case of lower case that exists in the list?

7

No, sorry I see that my first post was all but clear. I was wondering if I needed to update my formula below, in order to apply a security filter based on roles. The formula should be valid if the email differs only by case: e.g. it should be valid if USEREMAIL() = abc@de.fg and the table contains Abc@de.fg.
Apparently, my formula is working well, but according to my expectations, it should not:

IN(USEREMAIL(), SELECT(Users[Email], [Role] = "Manager"))
8

Okay, got it. Thank you. I believe your formula will work in that case. I misread your requirement that you wanted to validate emails only in one of the cases-lower or higher.

2 Likes
9

Just for the sake of completeness, also this formula is valid when comparing abc@de.fg with AbC@de.fg :

USEREMAIL() = [Sales Person].[Email]

I think the AppSheet Team did some nice trick behind the scenes, in order to make it work!

1 Like
10

as far as I know the expression working based on “case sensitive” is FIND() expression.
Rest of expressions will treat the large/small text equally for years.
I believe there has been no change in terms of this.

2 Likes
11

perissf:

Recently I came across a thread related to checking USEREMAIL() against a list of email records, and it was mentioned that the check may fail due to difference between uppercase and lowercase characters.

Incorrect. Uppercase and lowercase are equivalent.

tsuji_koichi:

as far as I know the expression working based on “case sensitive” is FIND() expression.

Yep.

tsuji_koichi:

Rest of expressions will treat the large/small text equally for years.

Yep.

2 Likes
12

Thank you @Steve !
For curiosity, if I compare the above mentioned strings in other situations, does the equality operator consider them equivalent or not?

13

In each of these cases, upper- and lowercase do not matter:

perissf:

> USEREMAIL() = [Sales Person].[Email]> >

perissf:

> IN(USEREMAIL(), SELECT(Users[Email], [Role] = "Manager"))> >

Is that what you’re asking?

1 Like