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

Transform your business operations with Microsoft Dynamics 365 Business Central

Experience the transformative power of Microsoft Dynamics 365 Business Central for yourself! Request a free demo today and see how our solutions can streamline your operations and drive growth for your business.

Our team will guide you through a personalized demonstration tailored to your specific needs. This draft provides a structured approach to presenting Qualia Tech's offerings related to Microsoft Dynamics 365 Business Central while ensuring that potential customers understand the value proposition clearly.

First Name*

Last Name*

Company Name*

Business Email*

Phone Number

Number Of Employees

Current Business Management System

Additional Information

Please read and confirm the following:

I consent to Qualia Tech processing my personal data to respond to my demo request and provide information about Microsoft Dynamics 365 solutions. I understand that I can withdraw my consent at any time. *

I agree to receive newsletters and marketing communications from Qualia Tech about products, services, and events. I understand I can unsubscribe at any time.

*Note: Fields marked with * are mandatory for processing your request.