Forum Discussion

RubyKapil2's avatar
RubyKapil2
Qrew Captain
5 years ago

Restrict data entry in numeric field to be less than a certain value

Without using a Multi-Choice text field, is there any way to limit the values entered by user to be whole numbers between 0-11. We have users who are entering total inches into a field that is meant for "remaining" inches (20 ft, 5 in some users are entering 245 in the inches field).
I could set up rules that display message if entered incorrectly but some users use Grid Edit and not the Form.
Or check if Ft is left blank but I am looking for something that prevents the error in the first place versus QCing data later.

------------------------------
Ruby Kapil
rubykapil@wi-finity.com
Wi-Finity, Inc
------------------------------
Custom Data Rules
  • JordanMcAlister's avatar
    JordanMcAlister
    Qrew Captain
    5 years ago
    Is it a matter of reading the data, or does the data get exported? If it's the former - you could always have the feet and inches field for editing and a separate field for viewing that combines feet and inches no matter if they decide to put the total amount of inches or not.
    What I use is:
    ToText([Measured Pole Ht. (Feet)]) & " ft " & [Measured Pole Ht. (Inches)] & " in (" & ([Measured Pole Ht. (Feet)]+Round((0.0833333*[Measured Pole Ht. (Inches)]),0.01)) & " ft)")

    If 245 inches were inputted into the inches field, it would display: 0 ft 245 in (20.42 ft)
    That way I know what was inputted and then the equivalent in feet and we convert everything to decimal

    Other than that I don't know of a way to limit the values in a numeric field - that would indeed be very helpful.

    ------------------------------
    Jordan McAlister
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    This should be easy with Data Validation.

    Go to the Advanced Properties  of the Table  and enable Data Validation.  It's bullet proof as it limits data entry errors regardless of if the data entry is by form or grid edit or import or API

    Make a formula which calculates to the error message words or blank.  Blank means no error, but if there are words, then the save will fail and the words will display.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      5 years ago
      Austin was using better terminology.
      Advanced Settings
      custom data rules.

      ------------------------------
      Mark Shnier (YQC)
      Quick Base Solution Provider
      Your Quick Base Coach
      http://QuickBaseCoach.com
      mark.shnier@gmail.com
      ------------------------------
  • AustinK's avatar
    AustinK
    Qrew Commander
    5 years ago
    Could you use the custom data rules feature on the table instead of a form rule? I believe that works in grid edit reports as well, it won't allow records to be saved if they do not match your data rules. You could make sure that the field is only under a certain number with that but number validation can be tricky. In this case at least it should only be 2 digits and does not have any separators.

    I think the simplest version would be something like this.

    if(
    [Remaining Inches] > 11,
    "Remaining inches cannot be more than 11"
    )

    That should stop anything from being saved that is more than 11 in that box but it may not solve for decimals if they enter those. That could possibly be done at the field level settings though.