Advanced Formula Building

This section provides comprehensive guidance on building sophisticated validation formulas that leverage the full power of QUALIA Rule Engine's expression syntax. You will learn advanced techniques for complex conditions, mathematical calculations, date arithmetic, text pattern matching, and aggregate functions.

19.1 Formula Syntax Deep Dive

QUALIA Rule Engine formulas use Business Central's native filter syntax with specific extensions for placeholder resolution and calculations.

Core Syntax Rules:

• Placeholders use square brackets: [TableID:FieldID]

• Calculations use curly braces: {expression}

• The is keyword performs comparisons (there is NO = operator)

• Multiple values use pipe separator: 'Value1'|'Value2'

• Ranges use double-dot: 10..100

• Wildcards: * (zero or more characters), ? (exactly one character)

Comparison Operators:

• is value - Equals

• is <>value - Not equals

• is >value - Greater than

• is <value - Less than

• is >=value - Greater than or equal

• is <=value - Less than or equal

19.2 Complex Conditions with Logical Combinations

While QUALIA Rule Engine does not support AND/OR operators within a single formula, you can achieve complex logic through combinations of Scenarios and Conditions.

Pattern: AND Logic (All Conditions Must Be True)

Use multiple Scenarios to require ALL conditions:

Scenario 1: [18:39] is ''  (Customer not blocked)
Scenario 2: [18:20] is >0  (Customer has credit limit)
Condition: {[36:61] + [18:59]} is >[18:20]

All three must be TRUE for the condition actions to execute.

Pattern: OR Logic (Any Condition Triggers Action)

Use multiple Conditions with the same action:

Condition 1: [37:15] is <=0  (Quantity invalid)
  └─ Error: "Invalid quantity"
  
Condition 2: [37:22]

Either condition triggers an error.

19.3 Using Aggregate Functions with Linked Tables

When Linked Tables are configured, you can use aggregate functions to calculate values across multiple related records.

Aggregate Function Syntax:

• MIN[TableID:FieldID] - Minimum value

• MAX[TableID:FieldID] - Maximum value

• SUM[TableID:FieldID] - Sum of values

• FIR[TableID:FieldID] - First value

• LAS[TableID:FieldID] - Last value

Example: Sum of All Line Amounts

Trigger Table: Sales Header
Linked Table: Sales Lines (multiple lines per header)

Condition: SUM[37:61]

Calculates total of all line amounts for the order.

19.4 Mathematical Expressions in Formulas

Use curly braces {} to perform calculations within formulas.

Arithmetic Operators: + (addition), - (subtraction), * (multiplication), / (division)

Example Calculations:

{[37:15] * [37:22]}  (Quantity × Unit Price)
{[36:61] - [36:45]}  (Amount - Discount)
{[18:59] / [18:20]}  (Balance ÷ Credit Limit = utilization %)
{([37:22] * [37:15]) * (1 - [37:27]

Using Calculations in Conditions:

Condition: {[37:15] * [37:22]

Compares calculated line amount to threshold.

19.5 Date Arithmetic and Comparisons

QUALIA Rule Engine supports date comparisons and calculations.

System Date Placeholders:

• [TODAY] - Current system date

• [W] - Work date (BC work date setting)

• [CDT] - Current date and time

Date Comparison Examples:

[36:20] is <[TODAY]  (Order Date is before today)
[36:71] is >={[TODAY] + 7}  (Ship Date is 7+ days from now)
[27:50] is <={[TODAY]

Date Range Validation:

[36:71] is >=[36:20]

19.6 Text Pattern Matching with Wildcards

Use wildcards for partial text matching.

Wildcard Operators:

• * matches zero or more characters

• ? matches exactly one character

Examples:

[18:2] is 'CUST*'  (Customer No. starts with CUST)
[27:3] is '*SPECIAL*'  (Item Description contains SPECIAL)
[36:2]

Excluding Patterns:

[27:3] is <>'*OBSOLETE*

19.7 Formula Examples Library

Example 1: Percentage Within Range

Validate discount is between 0% and 25%:

[37:27]

Example 2: Credit Utilization Check

Alert when customer using >80% of credit limit:

{[18:59] / [18:20]

Example 3: Multi-Field Required Check

INCORRECT EXAMPLE - DO NOT USE: The example below shows a common mistake. Using scenarios to check for blank fields is backwards.

❌ Wrong Approach (DO NOT USE):

Scenario 1: [18:2] is ''  (Name blank)
Scenario 2: [18:5] is ''  (Address blank)
Scenario 3: [18:9]

Why This Is Wrong: When Name IS blank, the filter is '' MATCHES, so processing CONTINUES. This is the opposite of what you want for required field validation.

✅ Correct Approach for Required Field Validation - Use Conditions:

Condition 1: [18:2] is ''
  └─ Error Action: "Customer Name is required"
  
Condition 2: [18:5] is ''
  └─ Error Action: "Customer Address is required"
  
Condition 3: [18:9]

✅ Alternative - Use Scenarios to Filter Complete Records Only:

Scenario 1: [18:2] is <>''  (Name populated - process only complete records)
Scenario 2: [18:5] is <>''  (Address populated)
Scenario 3: [18:9] is <>''  (Phone populated)

Condition: [18:20] is >[18:39]

This means: Only process customers where ALL required fields are populated. Skip incomplete customer records.

Example 4: Date Range Validation

Shipment must be within 90 days of order:

{[36:71] - [36:20]

Example 5: Dynamic Price Threshold

Price must not exceed 10% above standard cost:

[37:22] is >{[27:18]