You’ve been working long and hard to get your Valid If formula correct, and finally it’s doing what it’s supposed to - preventing duplicates, wrong info, bad dates, etc. from being entered by your users.
But now you want to go a step further and provide the user with some info about WHY the data they entered is invalid.
This is where the “Invalid value error” field comes into play.
Utilizing this features is extremely easy, due to the fact that most the hard work has already been done when you completed your Valid If formula.
All you need to do is:
- Copy the formula you’ve got for your Valid if,
- Reverse is; and
- Insert it into an IFS() statement
- along with whatever actual error message you want to show to the user.
Let’s say I’m preventing users from entering the same [User_Name], and I have a Valid If formula of the following:
NOT(IN([_THIS], SELECT(Users[User_Name], [UserID] <> [_ThisRow].[UserID])))
Once I go into the form, and enter something that's already inside the table, I'll receive the default error message.
To create a customized error message
I’ll take the formula inside the Valid If, add it to an IFS() formula (as the condition) - reversing the logic - and add in my error message.
Here’s what my formula looks like when I’m done
IFS(IN([_THIS], SELECT(Users[Users_Name], [UserID] <> [_ThisRow].[UserID])), "This name is already in the system" )
Here’s the process and what the behavior looks like in the end
Bonus: How to add the value entered by the user directly into the error message
Sometimes it can be helpful for people to directly see the value they entered into the system used in the very message saying “this” isn’t valid - this way there is no confusion about what the message is talking about.
To accomplish this, take your invalid error message and create a CONCATENATE() that combines the column with your message:
IFS(IN([_THIS], SELECT(Users[Users_Name], [UserID] <> [_ThisRow].[UserID])), concatenate( '"', [Users_Name], '" is already in the system' ) )