An interesting situation happened a few weeks ago. I wanted to validate a number field, but the number field could be blank. It takes a little different validation formula to handle this kind of a situation. So I decided to share the solution.

The problem with validating a number field is that Notes will give you the "cannot convert text to number" error, which might be all right. But it would be nice to be able to give a friendlier error message. But simply checking for a number isn't sufficient because a blank value is not a number. So here's the input validation formula we came up with:

@If(@IsError(MyNumberField); @Failure("Please enter a number"); @IsNumber(MyNumberField); @Success; MyNumberField= ""; @Success; @Failure("Please enter a number"))

The first part of the statement checks for the "cannot convert text to number" error situation. So if the user puts in some text instead of a number, the first part of the @If statement will trap and give the appropriate error message. (Note that the error message above isn't much friendlier than the "cannot convert text to number" Notes error message - the error message used in the application I was building told the user the field label).

The next part of the statement checks for a valid number. If the value is a number, then the validation succeeds. Note that this only checks for a number - in a minute I'll talk about checking a number in a certain range.

The third part of the statement handles the situation where the value is blank. An empty value is allowed in this situation. If an empty value is not allowed, you can switch the @Success to an @Failure or rearrange the statement to check for !@IsNumber.

The final part of the statement is the default. It actually will never happen - the first three checks will take care of every situation. But the @If block needs to have the final statement.

What if you want to check for a range of values? Well, the statement will start out checking for the error situation as before. But then the next statement should check for a number and the range. Then there will be a new check for a number that will cause a failure. This is needed because if the value was a number and was in the range, it would have exited. Here's the statement:

@If(@IsError(MyNumberField); @Failure("Please enter a number"); @IsNumber(MyNumberField) & MyNumberField >= 1 & MyNumberField <= 100; @Success; @IsNumber(MyNumberField); @Failure("Please enter a number between 1 and 100"); MyNumberField= ""; @Success; @Failure("Please enter a number"))

For this example, the valid range is 1 to 100, inclusive. If the value is a number and is in that range, the formula returns a success. If the formula didn't return, then it checks for the value being a number. If it's a number, then it didn't return from the previous statement, so the value must be out of range. The rest of the checks proceed as before.