Forum Discussion

StephanieHarris's avatar
StephanieHarris
Qrew Assistant Captain
4 years ago

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 entered in [Policy Number] before the custom [Submit] button appears.

------------------------------
Stephanie Stephanie
------------------------------
Formulas and functions
roles and permissions

5 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    4 years ago
    You 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
    ------------------------------
    • StephanieHarris's avatar
      StephanieHarris
      Qrew Assistant Captain
      4 years ago
      This works perfectly!


      Thanks Mark!

      ------------------------------
      Stephanie Stephanie
      ------------------------------
    • StephanieHarris's avatar
      StephanieHarris
      Qrew Assistant Captain
      4 years ago
      ​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
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        4 years ago
        This 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
        ------------------------------
    • LinhTran's avatar
      LinhTran
      Qrew Member
      4 years ago
      Hi Mark,

      We just updated the help topic on custom data rules to include this info. Thanks again for your feedback!

      ------------------------------
      Linh Tran
      Sr Manager, Customer Education and Content Strategy
      Quick Base