Calculated Filters

Calculated Filters enable dynamic filter expressions that automatically insert runtime values such as the current user ID, today's date, or the work date. This powerful feature allows you to create context-aware validation rules in Event Rules without hardcoding specific values.

Overview

Unlike static filter expressions that use fixed values, Calculated Filters replace placeholders with values determined at runtime. This makes validation rules flexible and reusable across different contexts, users, and time periods.

Key capabilities:

User-specific validation: Filter based on the current user's ID
Date-based validation: Use today's date or the company work date in filters
Dynamic text values: Insert predefined text values in filter expressions
Reusable logic: Create filters once and use across multiple Event Rules
Flexible expressions: Combine calculated values with standard filter operators

Tip

Calculated Filters are particularly useful when validation rules need to adapt based on who is performing the action or when the action occurs.

When to Use Calculated Filters

Calculated Filters are ideal for scenarios requiring dynamic validation:

User responsibility: Validate that users only modify records assigned to them
Date restrictions: Ensure dates are in the future or within a specific range from today
Time-based rules: Apply different validation based on the current work date
Department access: Restrict actions to users' departments (using User ID lookup)
Temporal data: Validate against dates calculated from the work date

Example scenarios:

Only allow users to modify orders assigned to them
Ensure delivery dates are at least 3 days from today
Validate that documents are created on or after the work date
Restrict status changes to documents from the current period

Calculated Filter Types

Calculated Filters support four value types:

Type	Description	Common Use Cases
Text	Static filter expression (no substitution)	Fixed filter values, constant comparisons
User ID	Current user's ID (substituted into filter)	User assignment validation, responsibility checks
Today	Current system date (substituted into filter)	Date range validation, future date checks
Work Date	Company work date (substituted into filter)	Period validation, fiscal date checks

Note

The Work Date is the accounting date set by the company, while Today is the current system date. These may differ when posting historical or future-dated transactions.

Create a Calculated Filter

To create a new calculated filter:

Search for and open the Calculated Filter List page.
Click New to create a new calculated filter.
Fill in the fields:
- Code: Enter a unique code (up to 20 characters) to identify this filter
- Description: Provide a clear description of what the filter does
- Type: Select the value type (Text, User ID, Today, or Work Date)
- Filter Text: Enter the filter expression with %1 as placeholder
Click OK to save the calculated filter.

Filter Text Syntax

The Filter Text field uses standard Business Central filter syntax. For User ID, Today, and Work Date types, use %1 as a placeholder for the calculated value:

Basic format:

%1

This inserts the calculated value (for User ID, Today, and Work Date types).

Comparison operators:

>=%1          # Greater than or equal to calculated value
..%1          # Up to and including calculated value
%1..          # From calculated value onwards
>%1&<=%1+7D   # Greater than value and up to 7 days after

Exclusion:

<>%1          # Not equal to calculated value

Range:

%1..%1+30D    # From calculated value to 30 days later

Important

Text type: The Filter Text is used exactly as entered without any placeholder substitution. Use this for static filter expressions.

User ID, Today, Work Date types: The %1 placeholder is replaced with the calculated value. If Filter Text is empty, it defaults to %1 (equals the calculated value).

Examples by Type

Text Type

Use case: Validate that a field matches a specific text value

Configuration:

Code: STATUS-ACTIVE
Type: Text
Filter Text: ACTIVE

Result: Filter expression is ACTIVE (field must equal "ACTIVE")

Validation scenario: Ensure a status field is set to "ACTIVE" before allowing an action

Note

Text type uses the Filter Text exactly as entered. The %1 placeholder is not replaced. Use this type for static, unchanging filter expressions.

Use case: Create a reusable filter for a specific status range

Configuration:

Code: ACTIVE-STATUSES
Type: Text
Filter Text: 10|20|30

Result: Filter expression is 10|20|30 (field must be 10, 20, or 30)

Validation scenario: Ensure a document is in one of the active status codes before allowing modification

Tip

Text type is useful for creating reusable, static filters that you want to reference from multiple Event Rules without hardcoding the same filter expression in each rule.

User ID Type

Use case: Validate that the assigned user matches the current user

Configuration:

Code: CURRENT-USER
Type: User ID
Filter Text: %1

Result: If current user is "JSMITH", filter becomes JSMITH

Validation scenario: In an Event Rule, check that the "Assigned To User ID" field equals the current user before allowing status changes.

Use case: Exclude records assigned to the current user

Configuration:

Code: NOT-CURRENT-USER
Type: User ID
Filter Text: <>%1

Result: If current user is "JSMITH", filter becomes <>JSMITH

Validation scenario: Validate that documents are not assigned to the user making the change (enforcing segregation of duties).

Today Type

Use case: Ensure a date is today or in the future

Configuration:

Code: TODAY-OR-LATER
Type: Today
Filter Text: %1..

Result: If today is 2025-11-15, filter becomes 2025-11-15..

Validation scenario: Validate that a delivery date is not in the past.

Use case: Restrict dates to within the next 30 days

Configuration:

Code: NEXT-30-DAYS
Type: Today
Filter Text: %1..%1+30D

Result: If today is 2025-11-15, filter becomes 2025-11-15..2025-12-15

Validation scenario: Ensure planned delivery dates are within the next month.

Use case: Validate that a date is not today

Configuration:

Code: NOT-TODAY
Type: Today
Filter Text: <>%1

Result: If today is 2025-11-15, filter becomes <>2025-11-15

Validation scenario: Prevent scheduling activities for the current day (require advance notice).

Work Date Type

Use case: Ensure posting date is on or after the work date

Configuration:

Code: WORKDATE-OR-LATER
Type: Work Date
Filter Text: %1..

Result: If work date is 2025-11-01, filter becomes 2025-11-01..

Validation scenario: Prevent backdating documents before the current accounting period.

Use case: Validate dates are within the current work period

Configuration:

Code: CURRENT-PERIOD
Type: Work Date
Filter Text: %1..%1+30D

Result: If work date is 2025-11-01, filter becomes 2025-11-01..2025-12-01

Validation scenario: Ensure transaction dates fall within the current accounting period.

Use in Event Rules

To use a calculated filter in an Event Rule validation:

Create or open an Event Rule (see Event Rules).
Add a validation step with:
- Validation Type: Calculated Filter
- Field Caption: Select the field to validate against the filter
- Calculated Filter Code: Choose your calculated filter
- Action Type: Error, Message, Notification, or Function
- Message Template: Define the validation failure message

Example validation step configuration:

Scenario: Ensure delivery date is at least 3 days from today

Create Calculated Filter:
- Code: MIN-3-DAYS-OUT
- Type: Today
- Filter Text: >%1+3D
Event Rule Validation Step:
- Validation Type: Calculated Filter
- Field Caption: Delivery Date
- Calculated Filter Code: MIN-3-DAYS-OUT
- Action Type: Error
- Message: Delivery date must be at least 3 days from today. Current value: {Value}

Result: If today is November 15, delivery dates must be November 19 or later.

Practical Examples

Example 1: User Ownership Validation

Business requirement: Users can only modify orders assigned to them

Setup:

Create Calculated Filter:
- Code: MY-ORDERS
- Type: User ID
- Filter Text: %1
Event Rule on WMS Document Header:
- Trigger: Modify on any field
- Validation Step:
  - Type: Calculated Filter
  - Field: Assigned User ID
  - Calculated Filter: MY-ORDERS
  - Action: Error
  - Message: You can only modify orders assigned to you. This order is assigned to {Value}

Example 2: Future Date Validation

Business requirement: Shipment dates must be at least tomorrow or later

Setup:

Create Calculated Filter:
- Code: TOMORROW-OR-LATER
- Type: Today
- Filter Text: >%1
Event Rule on WMS Document Header:
- Trigger: Field Validation on Shipment Date
- Validation Step:
  - Type: Calculated Filter
  - Field: Shipment Date
  - Calculated Filter: TOMORROW-OR-LATER
  - Action: Warning
  - Message: Shipment date {Value} is today or in the past. Consider scheduling for tomorrow or later.

Example 3: Work Date Period Validation

Business requirement: Documents can only be created on or after the work date

Setup:

Create Calculated Filter:
- Code: ON-OR-AFTER-WORKDATE
- Type: Work Date
- Filter Text: %1..
Event Rule on WMS Document Header:
- Trigger: Insert
- Validation Step:
  - Type: Calculated Filter
  - Field: Document Date
  - Calculated Filter: ON-OR-AFTER-WORKDATE
  - Action: Error
  - Message: Document date {Value} cannot be before the work date. Please use {ExpectedValue} or later.

Example 4: Advance Notice Requirement

Business requirement: Appointments must be scheduled at least 7 days in advance

Setup:

Create Calculated Filter:
- Code: MIN-7-DAYS-NOTICE
- Type: Today
- Filter Text: >=%1+7D
Event Rule on Appointment table:
- Trigger: Field Validation on Appointment Date
- Validation Step:
  - Type: Calculated Filter
  - Field: Appointment Date
  - Calculated Filter: MIN-7-DAYS-NOTICE
  - Action: Error
  - Message: Appointments require 7 days advance notice. Earliest available date: {ExpectedValue}

Date Filter Formulas

When using Today or Work Date types, you can apply date formulas in the Filter Text:

Formula	Description	Example (Today = 2025-11-15)
`%1`	Exact date	`2025-11-15`
`%1+1D`	One day after	`2025-11-16`
`%1-1D`	One day before	`2025-11-14`
`%1+1W`	One week after	`2025-11-22`
`%1+1M`	One month after	`2025-12-15`
`%1+1Y`	One year after	`2026-11-15`
`%1..%1+30D`	Range: date to 30 days later	`2025-11-15..2025-12-15`
`>%1`	After date	`>2025-11-15`
`<%1`	Before date	`<2025-11-15`
`>=%1-7D`	From 7 days before onwards	`>=2025-11-08`

Date formula abbreviations:

D = Day
W = Week
M = Month
Q = Quarter
Y = Year

Tip

Combine date formulas with range operators to create flexible date windows. For example, %1+3D..%1+10D creates a window from 3 to 10 days from the calculated date.

Combining with Event Rule Criteria

Calculated Filters work well with Event Rule Criteria to create sophisticated validation logic:

Example: Validate delivery dates only for specific document types

Event Rule Criteria (controls when validation runs):
- Criterion Type: Field
- Field: Document Type Code
- Filter: SHIPMENT
Validation Step (uses calculated filter):
- Type: Calculated Filter
- Field: Delivery Date
- Calculated Filter: NEXT-30-DAYS (today to 30 days out)
- Action: Error

Result: Delivery date validation only applies to shipment documents, and only allows dates within the next 30 days.

Best Practices

When creating and using calculated filters:

Descriptive codes: Use clear, self-documenting codes like TODAY-OR-LATER instead of CF001
Document intent: Write detailed descriptions explaining what the filter validates
Test with dates: Verify date-based filters work correctly across month/year boundaries
Consider time zones: Be aware that Today and Work Date are server-based
User ID format: Remember User IDs are uppercase in Business Central
Filter compatibility: Ensure the filter syntax is valid for the field type
Performance: Calculated filters are evaluated on every validation - keep them simple
Reusability: Create general-purpose filters that can be used across multiple rules

Troubleshooting

If a calculated filter isn't working as expected:

Filter Not Matching

Problem: Validation always fails even though value seems correct

Solutions:

Verify the filter expression syntax (use standard BC filter notation)
Check that %1 placeholder is used correctly
Test the filter manually on a table to see actual results
Ensure field types match (date field with date filter, text field with text filter)

Date Formula Issues

Problem: Date formulas produce unexpected results

Solutions:

Verify date formula syntax (+1D, +1W, etc.)
Check for month-end boundaries (e.g., January 31 + 1M = February 28/29)
Test across year boundaries
Ensure date formulas are placed correctly in filter expression

User ID Not Matching

Problem: User ID filter always fails

Solutions:

Verify current user ID format (usually uppercase)
Check for extra spaces or special characters
Confirm the field contains the expected User ID format
Test with a simpler filter first (%1 only)

Work Date vs. Today Confusion

Problem: Wrong date is being used

Solutions:

Confirm whether business logic requires Today (system date) or Work Date (accounting date)
Remember Work Date can be manually set differently from Today
Document which date type the filter uses and why

Limitations

Be aware of these calculated filter limitations:

Text type: No placeholder substitution - filter is used exactly as entered
Single placeholder: For User ID, Today, and Work Date types, only the %1 placeholder is supported (but it can be used multiple times in the filter expression)
No field references: Cannot reference other fields from the record being validated
Fixed types: Each calculated filter has one type - cannot dynamically switch types
Text length: Filter Text limited to 30 characters
No functions: Cannot execute AL functions within the filter expression
Runtime evaluation: Filters are evaluated when validation runs, not when defined

Note

For more complex scenarios requiring field comparisons or calculations, use validation steps with Related Field or Function Set types instead.

Event Rules - Configure automatic validation rules using calculated filters
Regular Expressions - Pattern matching validation for text fields
Status Templates - Workflow automation with validation support
Function Sets - Complex business logic and criteria evaluation

Table of Contents

Calculated Filters

Overview

Tip

When to Use Calculated Filters

Calculated Filter Types

Note

Create a Calculated Filter

Filter Text Syntax

Important

Examples by Type

Text Type

Note

Tip

User ID Type

Today Type

Work Date Type

Use in Event Rules

Practical Examples

Example 1: User Ownership Validation

Example 2: Future Date Validation

Example 3: Work Date Period Validation

Example 4: Advance Notice Requirement

Date Filter Formulas

Tip

Combining with Event Rule Criteria

Best Practices

Troubleshooting

Filter Not Matching

Date Formula Issues

User ID Not Matching

Work Date vs. Today Confusion

Limitations

Note

Related Information