Understanding validation rules

This page explains how validation rules work in ActivityInfo and what to review when you find invalid records.

How validation rules work

Validation rules in ActivityInfo define whether a record is valid at any moment in time.

As a result:

  • A record that was valid when it was created can become invalid later
  • Validation errors may appear even if no one has edited the data

This behavior is always present in ActivityInfo, but it becomes visible in the interactive table, where many records are displayed at once and invalid cells are highlighted immediately.

Validation rules using dates

When validation rules depend on the current date or time.

Functions such as:

These functions return different values as time passes. Validation rules that use these functions will therefore change their outcome over time.

Example

SESSION_DATE >= ADDDATE(TODAY(), -7) &&
SESSION_DATE < ADDDATE(TODAY(), 7)

This rule requires the session date to be within seven days of today. The rule may be satisfied when the record is first added, but weeks later the same record may fall outside this range and become invalid.

When viewing data in the interactive table, this can result in many records showing validation errors at the same time.

Validating data entry versus long-term validity

Review each validation rule and determine whether it is intended to:

  • Always be true for the lifetime of the record.
  • Apply only when the data is first entered.

Rules that should always be true

Some validation rules describe conditions that must remain true at all times, such as:

  • Required fields
  • Logical relationships between fields (for example, a start date before an end date)
  • Numeric limits that should never be exceeded

These rules are well suited to continuous validation and behave the same in forms and in the interactive table.

If required fields are introduced after records already exist in the form, those records may become invalid if they do not contain values that satisfy the new requirements.

Rules that should apply only when data is entered

If a rule is intended to validate data only at the moment it is entered, this must be made explicit in the formula.

A common approach is to apply the validation rule only while the record is new.

IF(ISBLANK(_lastEditTime), START_DATE < TODAY(), TRUE)

With this rule:

  • New records must satisfy the date requirement
  • Existing records remain valid as time passes

This prevents historical data from becoming invalid when viewed in the interactive table.

Default values in the interactive table

In ActivityInfo, validation rules and default values both influence data entry in forms and the interactive table, but they serve different purposes and behave differently.

Default values help streamline data entry. They automatically populate a field when a new record is created. This reduces repetitive work and ensures commonly used values are prefilled. However, default values are applied only once, at the moment of record creation.

Validation rules, protect data quality. They define conditions that records must satisfy before they can be saved or submitted. Validation rules are evaluated continuously and can flag records as invalid if they do not meet the defined criteria.

Default values are applied when a new record is created. They are not continuously re-evaluated after the record has been saved.

When using the interactive table, this difference is important:

  • Default values are filled once, at creation time
  • Validation rules are evaluated continuously

Default values using TODAY() or NOW()

Default values may also use functions such as TODAY() or NOW().

When used as a default value, this expression sets the field to the current date at the moment the record is created. The value does not change afterward.

This makes default values suitable for capturing entry-time information, such as:

  • The date a record was created
  • The reporting period at the time of entry

However, if a validation rule also references the same field, it may later evaluate differently as time passes.

Before enabling the interactive table

To avoid unexpected validation errors:

  1. Review all validation rules that reference TODAY() or NOW()
  2. Review default values that use TODAY() or NOW()
  3. Confirm whether each rule should apply permanently or only at data entry
  4. Update entry-time rules to include conditional logic where appropriate

Summary

The interactive table does not change how validation rules or default values work. It makes records that infringe validation rules visible.

Validation rules determine whether a record is valid at any point in time. Default values determine what is filled in when a record is created. Designing both with this distinction in mind ensures a smooth transition and avoids confusion for users.

Next item
Adding a folder