Better Data with Validation Rules

01/28/2016 Posted by Brandon

Your database is only as good as the data it contains.

In most cases this is obvious. If you are calculating order totals, you’l need each order to have a number that can be added together. If you are running monthly reports you’ll need some valid dates to group by month.

Other cases are more subtle. You may want to only accept dates that are in the future, or define allowable ranges for a number.

Data that breaks these types of rules may not crash your database, but getting those values right can be just as critical to ensuring your data is meaningful and correct.

Knack has always helped by ensuring field types and providing options for values to be required or unique.

We recently added a feature that dramatically expands Knack’s validation capabilities: validation rules.

Validation Rules

To add validation rules, navigate to the field in your database and click on the Advanced Settings icon:

Click on the “Validation Rules” tab to add one or more rules to validate any values for this field. You’ll define when the value is NOT valid and then the message to display to the user in those scenarios:

If you add more than one criteria to a single rule then both will need to be true to trigger the value as invalid.

Criteria can be any other field in the object, so you can even add conditional rules based on values of other fields!

Filters

You can filter your values using all of the operators already available in Knack, for example:

  • greater than
  • does not contain
  • during the previous
We also included some new filters to add extra power:

  • string length: paragraphs must be less than 500 characters
  • domain validation: e.g. email must contain knack.com
  • file size and extension: file uploads must be PDF files

Regular Expressions

For you programmers out there we also included the ability to use regular expressions.

Regular Expressions use ranges and patterns to find specific matches in text. You can use regular expressions in Knack to validate whether the value is getting matched by the regular expression.

For example, the following regular expression will validate Social Security numbers (XXX-XX-XXXX):

^d{3}-d{2}-d{4}$