Require Certain Number of Characters
Is there a way to require a field to contain a certain number of characters? One of my fields is policy number and a policy number is 10 digits in length. I would like to require 10 characters be en...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendYou can make a formula checkbox field called [Policy Length Valid?]
IF(Length(Trim([Policy number]))=10, true)
Then have that be on the form in a field hidden by a form rule.
Then have a form rule that says.
When the record is saved
and
[Policy Length Valid?] is not checked
Action
Abort the save with message "Sorry, but Policy numbers must be 10 digits"
I'm assuming here that the Policy # is a text field. if its a numeric field then the formula will need to be
IF(Length(ToText([Policy number]))=10, true)
If data is entered by Grid Edit or by import, then you will need to use a Custom Data Rule.
IF(not [Policy Length Valid?], "Invalid Policy Number Length")
Here is some help on Custom Data Rules https://help.quickbase.com/user-assistance/custom_data_rules.html
I find that in this case the help text is not very helpful as they leave out the most important part that says how to enable Custom Data Rules. You go to the Advanced Properties for the table and here is a checkbox at the bottom. The also don't explicitly say that the save will be blocked when the error message calculates to non blank.
I did report this to Quick Base just now and they have told me that they will update the documentation :)
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
IF(Length(Trim([Policy number]))=10, true)
Then have that be on the form in a field hidden by a form rule.
Then have a form rule that says.
When the record is saved
and
[Policy Length Valid?] is not checked
Action
Abort the save with message "Sorry, but Policy numbers must be 10 digits"
I'm assuming here that the Policy # is a text field. if its a numeric field then the formula will need to be
IF(Length(ToText([Policy number]))=10, true)
If data is entered by Grid Edit or by import, then you will need to use a Custom Data Rule.
IF(not [Policy Length Valid?], "Invalid Policy Number Length")
Here is some help on Custom Data Rules https://help.quickbase.com/user-assistance/custom_data_rules.html
I find that in this case the help text is not very helpful as they leave out the most important part that says how to enable Custom Data Rules. You go to the Advanced Properties for the table and here is a checkbox at the bottom. The also don't explicitly say that the save will be blocked when the error message calculates to non blank.
I did report this to Quick Base just now and they have told me that they will update the documentation :)
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
Would there be a similar solution to ensure that an email address in an email address field meets standard email conventions (ends in ".com") ?
------------------------------
Stephanie Stephanie
------------------------------
------------------------------
Stephanie Stephanie
------------------------------
- MarkShnier__YouThis is my current formula for validating email addresses. It is possible that others out there have a more comprehensive one.
var text email = Trim([Quoted Dealer Email]); // Substitute in your own email field name here.
var text Warning =If($email="", "Missing email address",
List( "<br>",
If( not Contains($email,"@"), "Missing @ symbol" ),
If( not Contains(Part($email, 2, "@" ),"."), "Missing .com or similar at the end" ),
If( Contains($email, " "), "Has a space inside the email address" ),
//If(Part($email, 1,"<>[]{},")<>"", "Bad characters in in the email address"),
If(Part($email, 2,"<>[]{},")<>"", "Bad characters in in the email address"),
If(Begins($email,"<"), "Bad characters in email address"),
If(Ends($email,">"), "Bad characters in email address"),
If(Contains($email, "mailto"), "Bad email address"),
If(Contains($email, ":"), "Bad email address" ),
If(Ends($email, "."), "email ends in a period - that can't be right" ),
If(contains($email, ")"), "email contains a bracket ( or ) - that can't be right" )
));
If($Warning<>"", "<font color=red>" & $Warning)
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------