When adding new users to an app, we want to prevent duplicate entries from being made…especially where the Login Email is concerned.
I can add a Valid_If expression like this:
NOT(IN(USEREMAIL(), <list of Login Emails>)
This prevents the Email from being added if it already exists. However, when an existing row is Edited, a Valid_If expression like that above invalidates the email address. I cannot figure out for the life of me how to adjust the expression to work when adding a new user or editing an existing one. The validation is the opposite for these two use cases UNLESS the email is changed during and edit!!
I do know I can create two Forms - one for new users and a second for editing users. Then I can use a CONTEXT() function to decide which validation is performed on the column.
But it seems it should be simpler than this. Are there any other suggestions?