Custom data rules
Custom data rules allow you to validate Quick Base 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, any error messages you write as part of your custom data rule only display once all other errors are fixed, such as missing required values or not entering a unique value in a field that requires it. An error message will be returned for each record that fails. In grid edit, 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 not violate 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,
"Enter a discount of 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.
Did this help you? Give us a rating:
© 1999-2020 QuickBase, Inc. All rights reserved. Legal Notices.