If you use column type email, the only validation seems to be: It has to contain exactly one "@".
TestTest.com is not possible
Test@Test.com is working
But you can still put in invalid email addresses like:
. Te st@_Tesäüöß t.co m .
I learned from @Suvrutt_Gurjar and @Steve that we can use this valid_if expression for email column type:
ANY(EXTRACTEMAILS([_THIS]))=[_THIS]
And for EnumList type email:
ISBLANK([_THIS] - EXTRACTEMAILS([_THIS] & ""))
This is working very well, but you can still put in invalid email addresses like:
a@a.a or Test@Test.aaaaaaaaaaaaaaaaaaaaaaaaaaa
I don't know much about email syntax. So maybe someone already has an even more robust valid_if expression?
With their help too, I used as a ValidIf ISNOTBLANK(EXTRACTEMAILS([ClientEmail]))
Thank you for sharing. But with this expression you can still enter invalid stuff like:
test@test.com Blah Blah Blah
That's why I prefer ANY(EXTRACTEMAILS([_THIS]))=[_THIS]
This expression only allows a valid email address.
Maybe you could restrict to certain TLD by applying a list of the right ones.
Like this:
OR(
ENDSWITH([_THIS], ".COM"),
ENDSWITH([_THIS], ".CL"),
ENDSWITH([_THIS], ".ORG"),
ENDSWITH([_THIS], ".NET"),
ENDSWITH([_THIS], ".INFO"),
ETC
)
You would need to add those alongside the other validation rules. It's also a pain to write a whole expression using ENDSWITH(), it would be great to be able to pass a list of "allowed ends".
Anyway, this also could help:
IN(
INDEX(
SPLIT(
INDEX(
SPLIT(
[_THIS],
"@"
),
2
),
"."
),
2
),
LIST(
"COM",
"CL",
"ORG",
"NET",
"INFO"
)
)
Thank you for this hint. But there are too many different TLDs. Link
But as far as I know, it should has at least 2 characters.
So I came up with this:
AND(
ANY(EXTRACTEMAILS([_THIS]))=[_THIS],
LEN(INDEX(SPLIT(INDEX(SPLIT([_THIS],"@"),2),"."),2))>=2
)
With this you cannot enter test@test.c
But test@test.test.c (subdomain) is still possible 🙄
@Fabian_Weller wrote:With this you cannot enter test@test.c
But test@test.test.c (subdomain) is still possible 🙄
Try this to make sure that the Last on the list after spliting by "." is the one forced to be >= 2:
AND(
ANY(
EXTRACTEMAILS([_THIS])
)=[_THIS],
LEN(
INDEX(
SPLIT(
INDEX(
SPLIT(
[_THIS],
"@"
),
2
),
"."
),
COUNT(
SPLIT(
INDEX(
SPLIT(
[_THIS],
"@"
),
2
),
"."
)
)
)
)>=2
)
You could also limit the amount of subdomains with this:
AND(
ANY(
EXTRACTEMAILS([_THIS])
)=[_THIS],
LEN(
INDEX(
SPLIT(
INDEX(
SPLIT(
[_THIS],
"@"
),
2
),
"."
),
COUNT(
SPLIT(
INDEX(
SPLIT(
[_THIS],
"@"
),
2
),
"."
)
)
)
)>=2,
COUNT(
SPLIT(
INDEX(
SPLIT(
[_THIS],
"@"
),
2
),
"."
)
)>=3 # 2 for @Test.test; 3 for @Test.test.test; etc
)
Thank you very much @SkrOYC This is very nice.
Do you see any way to get this done also for EnumList type email?
That's a little bit tricky because it would need to check every item on the list.
Idk a way to do it without using a loop, wich is already overkill
Hello SkyrOCK.
Thank you very much for your teachings.
Using your last example, can you help me by showing me how I can limit the size of the email entered to 50 characters, all lowercase and, in addition, testing whether the email is already registered? Before asking you for support, I tried for a long time to make these changes, but unfortunately I couldn't.
I'm sorry for my command of the English language. If it weren't for Google Translate, I wouldn't even be able to send you this message.
Tks.
Hy, SkrOYC,
I believe I managed to adjust the expression so that it performs all the necessary validations.
Look:
AND(ANY(EXTRACTEMAILS([_THIS]))=[_THIS],LEN([_THIS])<51, FIND(LOWER([_THIS]),[_THIS])=1,LEN(INDEX(SPLIT(INDEX(SPLIT([_THIS],"@"),2),"."),COUNT(SPLIT(INDEX(SPLIT([_THIS],"@"),2),"."))))>=2,COUNT(SPLIT(INDEX(SPLIT([_THIS],"@"),2),"."))<=3,(not(in([_THIS], SELECT(GESTORAS TESTES[eMail Gestora], [Row ID] <> [_THISROW].[Row ID])))))
Anyway, please, when possible, tell me if I'm correct or if the expression can be improved to meet my requirements, right?
Best regards,
Aruanan
Well, I don't know your schema so I can't validate the Select expression, but it looks fine.
This would require the field to have 0 to 50 characters:
LEN([_THIS])<51,
And this other one should validate that the email is not in the corresponding list:
NOT(
IN(
[_THIS],
SELECT(
GESTORAS TESTES[eMail Gestora],
[Row ID] <> [_THISROW].[Row ID]
)
)
)
Hi, SkrOYC,
Tks a lot for your answer. I have learned a lot from your various posts on various topics related to the appsheet community.
Best regards.
Aruanan Avelino.