How do I validate a new Email address while still allowing Edit of that row later?

[WillowMobileSys]

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?

[Jonathan_S]

You would need to check your valid if with the currant row that is being edited and create 2 separate formulas with if being edited or if new entry.

If its a new entry, check to make sure none exist.
If its being edited, make sure we don’t check the id of the edited row?? Not sure how this would be implemented though.

[WillowMobileSys]

That is precisely the problem and I should have mentioned that. Once the form is launched it doesn’t know if it’s operating on a new entry or editing an existing one.

As far as I know there is no property to inspect to give the expression this information. That’s why the two forms described above so the expression can “know” if it’s dealing with new or edit.

I just feels like there must be an easier way. Maybe not.

[Jonathan_S]

Well to be honest,

I think you would need to do it with an action set to trigger multiple actions that is shown as an action for all rows Called “Edit”

First action the main action triggers is the get the id of the row being edited and put that in a sheet somewhere, than the next action would be to edit the form, and than you could use my verbal expression with this id that you have captured.

Don’t think there is an easier way to go about it, not hard just cumbersome.

[WillowMobileSys]

Wonderful! You are looking ahead at HOW to call the Custom Edit Form. Actually it is simpler than you are thinking. You can hide the system Edit Action and replace it with a custom one that navigates to our desired Custom Edit Form. Then everything still looks tidy.

Of course, the simpler way is to have a function/property that our expressions can inspect to determine if the Form is in new or edit. Then no need for multiple forms. I guess a Feature Request is needed.

[Marc_Dillon · Accepted solution]

NOT( IN( [_THIS] , SELECT( Table[Column] , [key] <> [_THISROW].[key] ) ) )

[Jonathan_S]

and look at that. insane and beautiful. So simple.

[WillowMobileSys]

Yes, that works!! Thank you!! I probably would have never thought of testing for existence of the key.

[Marc_Dillon]

I can’t claim the solution as my own, was just easier to re-type the expression than lookup the previous thread.

[Steve]

This is the pattern I use:

(
  ISBLANK(
    FILTER(
      "table",
      ([_THISROW].[column] = [column])
    )
  )
  - LIST([_THISROW])
)