Email validation

[Fabian_Weller]

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?

[OptimiX_XcrY]

With their help too, I used  as a ValidIf ISNOTBLANK(EXTRACTEMAILS([ClientEmail]))

[Fabian_Weller]

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.

[SkrOYC]

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"
  )
)

[Fabian_Weller]

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 🙄

[SkrOYC]

---

@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
)

[Fabian_Weller]

Thank you very much @SkrOYC This is very nice.

Do you see any way to get this done also for EnumList type email?

[SkrOYC]

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

[AruananSignove]

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.

[AruananSignove]

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

[SkrOYC]

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]
    )
  )
)

[AruananSignove]

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.