Working With Formulas - Advanced

[Navigation] Global Search > Utilities > Form Builder

[Accordion] Overview

Advanced formulas in Form Builder allow you to go beyond simple field logic and create context-aware, clinically meaningful custom forms. By combining CASE-based expressions, system data, and environment variables, clinics can automate calculations, highlight clinical risks, and adapt form behavior in real time, without manual intervention.

Using advanced formulas, you can:

• Reference client data: Access demographics, appointments, and other linked system information.
• Perform calculations: Execute date- and numeric-based calculations across records.
• Apply multi-condition logic: Use CASE statements to handle complex scenarios.
• Control form behavior dynamically: Adjust field values, visibility, required status, and visual styling in real time.
• Build reusable logic: Create formulas that automatically update as client data changes.

These capabilities are especially valuable for forms that must respond to clinical scores, client age, treatment history, or appointment activity in real time.

[Accordion] Why Clinics Eventually Need Advanced Formulas

In real-world workflows, form logic often grows over time.

What begins as a simple rule can expand into multiple conditions and calculations applied in stages. As forms start to incorporate clinical thresholds, historical client data, and visual indicators for risk awareness, the logic naturally becomes more complex.

At this point, relying on stacked basic rules can become hard to read, prone to errors, and difficult to maintain or troubleshoot.

Advanced formulas solve this by combining all logic into a single, well-structured CASE statement. Conditions are evaluated in a clear order, ensuring consistent and accurate results as complexity increases.

If your needs are limited to one condition and a fixed outcome, a basic formula may be enough. This article focuses on situations where more advanced, flexible logic is required.

[Accordion] What This Article Covers (and What It Doesn’t)

This article assumes that you already understand:

• How to apply a basic formula
• How to select fields and operators
• How to validate and save formulas

It focuses on advanced formulas rather than formula basics:

• Writing CASE-based logic
• Choosing advanced formulas intentionally
• Applying formulas across data, visibility, required, and color
• Using system data and environment variables safely

[Accordion] How to Decide Which Formula Type to Use

A good rule of thumb:

• If you can clearly express the logic in one sentence, use a basic formula.
• If you need “and then…”, “except when…”, or “otherwise…”, use an advanced formula.

[Accordion] Why Basic Formulas Break Down: Real-World Examples

The following examples illustrate where basic When > Criteria > Value > Then rules reach their limits, and why advanced CASE-based formulas become necessary in clinic workflows.

[Q] Example 1: Patient Age Calculation from Date of Birth

[A] A clinic wants to display patient age on an assessment form.

Attempt (Basic Formula):

• When DOB equals today, then Age = 0
• When DOB is before today, then  Age = ?

Why This Breaks Down

Basic formulas:

• Cannot calculate date differences
• Cannot convert DOB into an age value
• Cannot account for year boundaries or birthdays

At best, this results in manual entry or inaccurate logic.

Why Advanced Formula Is Required

Age calculation requires:

• Date arithmetic
• Reference to Patient Demographics
• Evaluation against today’s date

Advanced Data Formula Logic:

CASE
  WHEN Patient.DOB IS NOT NULL
  THEN DATEDIFF(year, Patient.DOB, {TODAY})
END

[Q] Example 2: Risk Score Classification (Low / Moderate / High)

[A] A screening tool produces a numeric risk score.

Attempt (Basic Formulas):

• Rule 1: When Score >= 15 - “High”
• Rule 2: When Score >= 8 - “Moderate”
• Rule 3: Else - “Low”

Why This Breaks Down

• Rule order becomes critical but not always obvious
• Overlapping conditions cause inconsistent results
• Adding new thresholds requires rewriting multiple rules
• Troubleshooting becomes difficult

Why Advanced Formula Is Better

Using CASE logic:

• Evaluates conditions top to bottom
• Makes priority explicit
• Keeps all logic in one place

Advanced Data Formula Logic:

CASE
  WHEN RiskScore >= 15 THEN 'High'
  WHEN RiskScore BETWEEN 8 AND 14 THEN 'Moderate'
  WHEN RiskScore < 8 THEN 'Low'
  ELSE 'Unknown'
END

[Q] Example 3: Days Since Last Appointment

[A] A clinic that wants to track client disengagement based on time since last appointment.

Attempt (Basic Formula):

• When Appointment Date exists → Show warning
• When Appointment Date is blank → Hide warning

Why This Breaks Down

Basic formulas:

• Cannot calculate elapsed time
• Cannot compare appointment dates to today
• Cannot support thresholds like 30, 60, 90 days

Why Advanced Formula Is Required

• Date-based clinical logic requires:
  • Date math
  • External appointment data
  • Numeric comparison

Advanced Data Formula Logic:

Case 

When  DateDiff(Day, [Patient Demographics Max Phase:LastKeptAppDate] ,  {Today}  )>0
 then
 'Patient was seen'
 + cast( DateDiff(Day,[Patient Demographics Max Phase:LastKeptAppDate],{Today})
 as varchar) 
  + ' days back.'
Else 'Patient was never seen!'
End

[Q] Example 4: Conditional Field Visibility Based on Multiple Inputs

[A] A clinic needs to display follow-up questions when multiple conditions are met. 

Show follow-up questions if:

• RiskScore is high
• AND the patient is under 18

Attempt (Basic Formula):

• When RiskScore >= 15 - Show field

Why This Breaks Down

Basic formulas:

• Cannot easily combine multiple dependent conditions
• Leads to duplicated or chained rules
• Become fragile as logic expands

Why Advanced Formula Is Required

Using CASE logic:

• Compound conditions  are handled cleanly
• Future conditions can be added without breaking existing rules
• Keeps all logic in one place

Advanced Visibility Formula Logic:

CASE
  WHEN RiskScore >= 15 AND PatientAge < 18 THEN TRUE
  ELSE FALSE
END

[Accordion] Best Practices for Advanced Formulas

[Accordion] Common Pitfalls to Avoid

• Missing ELSE conditions
• Circular dependencies between fields
• Overusing formulas where validations are better
• Hardcoding values that may change clinically
• Any missing or errant character can cause a formula to fail