What's wrong in my validation for duplication check?

[Evendo]

Hi, for checking user's duplicate input at some specific fields on input form, I used formulas at "Valid if" with error messages.

For example, to check when user input duplicate email or not, at Email column, I added "Valid if" formula as below:

ISBLANK(FILTER("User", AND([UserID] <> [_THISROW].[UserID], ISNOTBLANK([Mail]), [Mail] = [_THISROW].[Mail])))

My intention for using the above formula is that when user input an email address, it will

I. Filter rows from table User which:

1. Have UserID different than UserID auto-generating at the current input form, AND

2. Have Mail column is not NULL/BLANK, AND

3. Have Mail column with content is exactly same with user currently inputting Mail value at form

II. Then, do check, if the result return no row, it's a valid data, otherwise (if not), it will display/throw error message

However, the real result became a state where error message displayed for every Mail context inputted in the form whether it's duplicate or not (except when it's blank/the Mail text-field is inputted nothing-at-all).

I wonder is there any mistake in my understanding with the formula or expression using at here?

As I'm still unfamiliar with Appsheet expression (and can't test the select/filter result easily like using a SQL statement on SQL server), it's great help if you can show me the reason for any mistake I had and a solution to solve it.

Thanks so much!

[Evendo]

It still display error message for everything inputted when I change the above formula from ISBLANK(...) to ISNOTBLANK(...) which made me confused is it a problem with memory cache or not. (Even though I already save the Appsheet project after every change I made).

Furthermore, when I tried inputting an formula like this at Valid if (NOT Show if), the Mail text-field suddenly disappear from the input form/screen

SELECT(User[Mail], AND([UserID] <> [_THISROW].[UserID], ISNOTBLANK([Mail]), [Mail] = [_THISROW].[Mail]))

[Evendo]

I've figured out that it had nothing wrong with the used formula at 1st post itself. It just because the field type is Email, and I used the above formula in combined with other complex validation checks and Appsheet can't differentiate the valid cause (like it's because email format incorrect or because of my customized validation) and the thrown error message is the same.

For the SELECT expression at the 2nd post, I don't know it's Appsheet setting (or error?) or not but I've found some tips from an Appsheet Guidebook that the book author also use SELECT syntax at "Valid if" to show and hide the field in specific condition (defined by the SELECT result). Hope someone could explain me more about this as I didn't see any mention about it at the below "Valid if" official article.

https://support.google.com/appsheet/answer/10107949?hl=en