Custom data rules allow you to validate Quickbase data using our formula language to set custom conditions and custom error message output per table.
When you copy a table, your custom data rules are also copied.
Custom data rules are applied when:
- Moving cards on a Kanban report
- Moving events on a calendar report
- Importing records
- Refreshing records from a connected table. Note: We recommend you DO NOT reference connected fields (or fields derived from connected fields) in your custom data rules because a synced record that fails validation could result in a failed or partially failed refresh.
- Adding or editing records using a form or API
- Adding or editing records via grid edit. Only new or modified records will be validated; previously entered records will not be.
- Grid edit errors are only returned one type at a time. For example, if you’ve entered a non-unique value in a field and also entered more than the max characters in another field, only one of those errors will be returned. When you fix the first error, the other will come up.
- Similarly, custom data rules errors only come after all other errors, such as unique, max length, and required, are fixed. An error message will be returned for each record that fails, rows will be highlighted in red, and the first column will have a tool tip with the error message that the record failed custom data rules.
Custom data rules are applied only to added or modified data. If you add a custom data rule and there are existing records that violate this new rule, then these records do not change. To edit these records, they must meet the custom data rules.
Formulas used for custom data rules
Formulas used for custom data rules are editable and toggle-able on the Table Advanced Settings page
Error messages must be text enclosed in quotes.
By default, one message will be returned per record. Using variables, you can provide multiple errors for a single record.
[Discount %] > 0.15,
“Discount too large! Please adjust so discount is 15% or less.”,
[Opportunity $ Size] > 100000 and Length([Contract PDF]) = 0,
”Deals worth more than $100k require a contract. Please upload the contract to save this deal.”
Summary and lookup fields
Fields and app variables can be referenced in custom data rules. If custom data rules only reference fields within a single table (not lookup or summary fields or formula fields referencing those), they should work as expected. If a change on one record could invalidate a related record, custom data rules may not work as expected. In particular, custom data rules sometimes do not work properly when there are embedded, editable reports with validation on lookup and summary fields.
The time taken to run a table rule will show up in the Performance analyzer as: TableValidationRulesCheck. This can let you know how efficient the formula you’ve written is.