Chapter 16: Formula Reference
Chapter Objectives:
Master date formula syntax
Understand comparison operators
Learn validation formula patterns
Apply formulas to real scenarios
16.1. Date Formula Syntax
Basic Date Formula Structure
Pattern: [Direction][Number][Period]
Simple Date Formulas
Days:
+1D Tomorrow
+7D One week from base
+30D 30 days from base
-1D Yesterday
-14D Two weeks before base
Example in Workflow:
Due Date: [18:54]
Weeks:
+1W One week from base (7 days)
+2W Two weeks from base (14 days)
-1W One week before base
Example:
Due Date: [36:5792]
Months:
+1M One month from base
+6M Six months from base
-1M One month before base
Example:
Due Date: [18:54]
Years:
+1Y One year from base
+5Y Five years from base
-1Y One year before base
Example:
Critical Date: [36:5792]
Period End Formulas
Current Week (CW):
Current Month (CM):
Current Quarter (CQ):
Current Year (CY):
Complex Date Formulas
Multiple Offsets:
+1M+5D 1 month + 5 days from base
+2W-1D 2 weeks - 1 day from base
+1Y+1M 1 year + 1 month from base
-1M+15D 1 month before + 15 days
Example:
Due Date: [36:5792]+1M+5D
Order Date = 2024-03-15
+1M = 2024-04-15
+5D = 2024-04-20
Result: April 20, 2024
Evaluation: Left to right
[36:5792]
Period End Plus Offset:
Work Day Formulas
Work Days (WD):
+5WD 5 working days from base (excludes Sat/Sun)
+10WD 10 working days from base
-3WD 3 working days before base
Example:
Due Date: [36:5792]
Critical Limitations
Limitation 1: No Dynamic Calculation in Placeholders:
NOT Supported:
[18:54]+[18:5700]
(Due Date + Credit Limit - type mismatch and dynamic calc)
Supported:
[18:54]
Limitation 2: No Conditional Formulas:
NOT Supported:
IF [18:5700] > 10000 THEN +7D ELSE +3D
Workaround:
Use separate tasks with conditional release (Line 9999)
Task A: Due Date = [TODAY]+7D, Release if Credit Limit > 10000
Task B: Due Date = [TODAY]
Limitation 3: Limited Operators:
16.2. Comparison Operators
Equality Operators
Equal (=):
Syntax: Field = Value
Examples:
[18:59] = '' Blocked is empty
[36:5900] = 'Released' Status is Released
[18:29] = 'JSMITH' Salesperson is JSMITH
[18:21]
Not Equal (<>):
Syntax: Field <> Value
Examples:
[18:102] <> '' E-Mail is not empty
[36:5900] <> 'Open' Status is not Open
[18:59]
Comparison Operators
Greater Than (>):
Syntax: Field > Value
Examples:
[18:5700] > 10000 Credit Limit greater than 10,000
[36:179] > 5000 Order Amount greater than 5,000
[18:5702] > [18:5700]
Greater Than or Equal (>=):
Syntax: Field >= Value
Examples:
[18:5700] >= 10000 Credit Limit 10,000 or more
[18:54] >= [TODAY]
Less Than (<):
Syntax: Field < Value
Examples:
[18:5700] < 5000 Credit Limit less than 5,000
[18:54] < [TODAY] Due Date in past (overdue)
[36:5792] < [TODAY]
Less Than or Equal (<=):
Syntax: Field <= Value
Examples:
[18:5700] <= 10000 Credit Limit 10,000 or less
[18:54] <= [TODAY]
Pattern Matching Operators
Contains (@):
Syntax: Field @ 'Pattern'
Examples:
[18:2] @ '*ALPINE*' Name contains ALPINE
[18:102] @ '*@contoso.com' Email ends with @contoso.com
[18:5] @ '*MAIN*
Case-Insensitive: @ operator ignores case (Alpine = alpine = ALPINE)
16.3. Validation Formula Examples by Scenario
Date Validation
Overdue Check:
Scenario: Detect overdue customer payments
Formula:
[18:54] < [TODAY]
Logic:
Customer."Due Date" is before today
Workflow Use:
Task: Follow up on overdue payment
Line 9999 (Release):
Condition: [18:54] < [TODAY]Future Date Check:
Scenario: Ensure date is in future
Formula:
[36:73] > [TODAY]
Logic:
Sales Header."Shipment Date" is after today
Workflow Use:
Task: Schedule shipment
Line 9999 (Release):
Condition: [36:73] > [TODAY]Date Range Check:
Scenario: Date within next 30 days
Formula:
[36:5792] >= [TODAY]
AND
[36:5792] <= [TODAY]+30D
Logic:
Order Date between today and 30 days from now
Workflow Use:
Task: Confirm imminent orders
Line 9999 (Release):
Condition 1: [36:5792] >= [TODAY]
Condition 2: [36:5792] <= [TODAY]
Status Validation
Status Change Check:
Scenario: Workflow when status becomes Released
Trigger:
OnModify, Field: Status (5900)
Condition: [36:5900]
Status Exclusion:
Scenario: Task releases if NOT completed
Formula:
[36:5900]
Threshold Validation
Credit Limit Exceeded:
Scenario: Alert when balance exceeds credit limit
Formula (Using Linked Table):
Scenario:
Table ID: 18 (Customer)
Field No.: 1 (No.)
Filter String: [18:1]
High Value Order:
Scenario: Approval required for orders > \,000
Formula:
[36:179] > 50000
Logic:
Sales Header."Amount Including VAT" exceeds 50,000
Workflow Use:
Trigger: OnModify, Amount Including VAT field
Condition: [36:179]
Existence Validation
Required Field Check:
Scenario: Email address required
Formula:
[18:102] <> ''
Logic:
Customer."E-Mail" is not empty
Workflow Use:
Task: Verify contact info
Line 10000 (Completion):
Condition: [18:102] <> ''
Condition: [18:9]
Optional Field Populated:
Scenario: Task only if salesperson assigned
Formula:
[18:29] <> ''
Logic:
Customer."Salesperson Code" is not empty
Workflow Use:
Task: Notify salesperson
Line 9999 (Release):
Condition: [18:29]
Multi-Field Validation
All Fields Required:
Scenario: Customer setup complete
Formulas (ALL must be TRUE):
[18:21] <> '' (Customer Posting Group)
[18:91] <> '' (Gen. Bus. Posting Group)
[18:29] <> '' (Salesperson Code)
Logic: AND
All conditions must pass
Workflow Use:
Task: Verify customer setup
Line 10000 (Auto-complete):
Condition 1: [18:21] <> ''
Condition 2: [18:91] <> ''
Condition 3: [18:29]
Any Field Populated:
Scenario: At least one contact method
Formulas (ANY must be TRUE):
[18:102] <> '' (E-Mail)
[18:9]
16.4. Common Formula Patterns
Pattern 1: Overdue Detection
Use Case: Identify overdue items
Date Formula:
[TableNo:DateField] < [TODAY]
Examples:
[18:54] < [TODAY] Customer payment overdue
[36:73] < [TODAY] Shipment overdue
[5900:34] < [TODAY] Service order overdue
Workflow Application:
Task: Overdue Follow-up
Trigger: Daily job queue or manual review
Line 9999: Release if [DateField] < [TODAY]Pattern 2: Credit Limit Warning
Use Case: Warn when approaching or exceeding credit limit
Formulas:
Warning (90%):
[18:5702] > [18:5700] * 0.9
(Balance > 90% of Credit Limit)
Exceeded:
[18:5702] > [18:5700]
Pattern 3: Required Field Validation
Use Case: Ensure all required fields populated before proceeding
Formulas:
[TableNo:Field1] <> ''
[TableNo:Field2] <> ''
[TableNo:Field3]
Pattern 4: Status Progression Check
Use Case: Verify proper status flow (e.g., Open Released Shipped)
Trigger Formula:
[TableNo:StatusField] = 'TargetStatus'
Examples:
Order Released:
[36:5900] = 'Released'
Order Shipped:
[36:5900]
Chapter 16 Complete: Formula Reference
