Email field accepting non-email values

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Acknowledged
Is there no way to ensure that an email address in entered in the correct format in an email field?

We have users accidentally creating records where they enter only the name portion of the email address but forget the domain.

Shouldn't a field of type "email" at least ensure that the values are entered in the correct format (containing the "@[domain]" portion)?
Photo of Juan

Juan

  • 74 Points
  • frustrated

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
You could use a form rule to validate, but yes this is frustrating
Photo of Juan

Juan

  • 74 Points
Hi Matthew,

Thanks for your reply.  A form rule may work for records entered via the form, but not for records created either uploads or grid edit unfortunately.

Additionally, there does not seem to be an option for validating email addresses even via a form rule, as the only options for comparison are:

Is equal to...
Is not equal to...
<
< or =
>
> or =

I would think that there would be a "contains" option to test for the presence of "@" but that's not the case.
As Matthew suggested you can make a boolean field to validate the email and use that field in form rules. But you need to invent the formula code yourself. put the formal field on the form to be sure that the form rules "see" it.  Forms tend to not recalculate fields which are not on the form explicitly.  You can put them in a section and hide the section using form rules.  If i get a chance I will look for examples of my formuals which validate, or maybe others will post some.  Like exactly 1 @ and at least 1 dot.
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
formula checkbox - [Contains @]

If(Contains([Email], "@"), true, false)

then use that checkbox in form rule.
Trim([Contact email])<>"" and Contains([Contact email],"@") and Contains([Contact email],".")
Photo of Juan

Juan

  • 74 Points
Thank you both, this is very helpful. I guess at this point what remains is the core issue, which is that this approach would not work if users were creating or updating records via grid edit (unless this option is disabled) or uploads.
Quite true.  In the future, I would expect that QuickBase would support field level validation rules so that we would not have to rely on form rules which don't work in grid edit.

But until then you can do a safety net Subscription report back to the [last modified by] of any invalid email addresses entered yesterday.

Just set up a daily 5 days a week Subscription report where the validation is false and the [Last Modified by] is the "current user".
Or you could use the image on load technique with some modified script as per this post by dan.
https://community.quickbase.com/quick...
Photo of Alex Wilkinson

Alex Wilkinson

  • 1,166 Points 1k badge 2x thumb
Additional suggestions:

1. To trap embedded spaces within an invalid entry:
 and not Contains(Trim([Contact Email]," "))

2. To ensure that a "." comes after the "@":
and Contains(Trim(Part([Contact Email], 2, "@" )),".")

3. Instead of a boolean, create a text-formula field that explains the problem or is "" if no problem is found. Then display the text-formula on the form if it's not "". That way, if the user has erred, they get some feedback. Here's an example that captures the most common problems and displays one-step-at-a-time feedback:

var bool outerSpace = Trim( [Registrant Email] ) <> [Registrant Email];
var bool innerSpace = Contains( Trim( [Registrant Email] ), " " );
var bool missingAt = not Contains( [Registrant Email], "@" );
var bool missingSuffix = Trim( Part( [Registrant Email], 2, "." ) ) = "";
var bool missingName = Trim( Part( [Registrant Email], 1, "@") ) = "";
var bool missingCompany = Trim( Part( Part( [Registrant Email], 2, "@" ), 1, "." ) ) = "";
var text warnMessage = If( 
      $outerSpace, "Has extra spaces at the beginning or end. Please remove them.",
      $innerSpace, "Has a space inside the email address.",
      $missingAt, "Missing @ symbol.",
      $missingSuffix, "Missing .com or other suffix.",
      $missingName, "Missing name-text before the @ symbol.",
      $missingCompany, "Missing company-name between @ and .com or other suffix.",
      "" );
If( [Registrant Email] = "" or $warnMessage = "", "", "Invalid email: " & $warnMessage )
(Edited)