Working With Formulas – Advanced (Reference Templates)

[Navigation] Utilities > Form Builder

[Accordion] Overview

Advanced formulas in Form Builder allow clinics to create context-aware, logic-driven custom forms by combining values from:

• Fields on the current custom form
• Linked system data sources (such as patient demographics or appointments)
• Environment variables (such as today’s date or logged-in user context)

These formulas are written using CASE-style logic in the Custom Formula Editor and are commonly used to calculate clinical values, classify scores, highlight risk, and dynamically control form behavior.

This article provides reference-style formula templates to help administrators understand how advanced formulas are structured and applied in real mental health clinic workflows.

[Warning] The formulas in this article are examples only. They are not copy-and-paste ready and may not work as-is in your environment.

Before using any formula:

• Replace field references with the actual fields available in your form
• Ensure referenced tabs, labels, and data sources exist
• Validate all formulas before saving

Field references are shown in a descriptive format to help you understand where the data comes from, not as literal values.

[Accordion] Understanding Field References in Examples

The examples in this article use the following reference styles:

Fields from the Current Custom Form

[Tab Name: Field Label]

Example:

[Tab 1: Date of Birth]

Linked Data Source Fields

[Patient Demographics Max Phase: DateOfBirth]

Environment Variables

[Environment Variables: Current Logged in User Type]

Your actual field names may differ.

[Accordion] Example 1: Calculating Patient Age

Use Case: A clinic wants to calculate the patient’s current age on a custom assessment form by referencing Date of Birth and today’s date.

This age value can later be used for:

• Age-based screening logic
• Conditional visibility
• Risk classification

Fields Referenced

Date field on the current custom form

[Tab 1: Date of Birth]

System variable representing the current date

[Environment Variables: Today]

Example Data Formula (Reference Only)

CASE
 WHEN [Tab 1: Date of Birth] IS NOT NULL
 THEN DATEDIFF(year, [Tab 1: Date of Birth], [Environment Variables: Today])
 ELSE NULL
END

Why This Requires an Advanced Formula

• Requires date arithmetic
• Evaluates data dynamically over time
• Cannot be reliably handled using basic rule-based formulas

How Clinics Typically Extend This

• Once age is calculated, clinics often:
  • Use it in visibility formulas
  • Combine it with clinical scores
  • Apply conditional requirements for minors vs adults

[Accordion] Example 2: Days Since Last Appointment (Linked Data Source)

Use Case: Display how many days have passed since the patient’s most recent appointment, using appointment data pulled from the system.

This is commonly used to:

• Identify disengaged patients
• Trigger follow-up workflows
• Highlight gaps in care

Fields Referenced

[Patient Appointments Max Phase: Last Appointment Date]
[Environment Variables: Today]

Example Data Formula (Reference Only)

CASE
 WHEN [Patient Appointments Max Phase: Last Appointment Date] IS NOT NULL
 THEN DATEDIFF(
      day,
      [Patient Appointments Max Phase: Last Appointment Date],
      [Environment Variables: Today]
 )
 ELSE 0
END

Why Advanced Formula Is Required

• Uses linked appointment data
• Performs date arithmetic
• Supports dynamic follow-up logic

Common Extensions

• Flag values over 30 / 60 / 90 days
• Pair with color or visibility formulas
• Trigger supervisor review

[Accordion] Example 3: Classifying a Screening Score (Low / Moderate / High)

Use Case: Convert a numeric screening score into a clinical classification to ensure consistent interpretation across staff.

Fields Referenced

[Tab 2: Total Screening Score]

Example Data Formula (Reference Only)

CASE
 WHEN [Tab 2: Total Screening Score] >= 15 THEN 'High'
 WHEN [Tab 2: Total Screening Score] BETWEEN 8 AND 14 THEN 'Moderate'
 WHEN [Tab 2: Total Screening Score] < 8 THEN 'Low'
 ELSE 'Unknown'
END

Why Advanced Formula Is Preferred

• Explicit priority order
• Easy to extend with new thresholds
• Clear clinical intent

Common Extensions

• Feed classification into color formulas
• Use as criteria for follow-up forms
• Lock forms when “High”

[Accordion] Example 4: Highlight High-Risk Scores Using a Color Formula

Use Case: Visually emphasize clinical risk by changing the background color of a field based on a calculated score.

Fields Referenced

[Tab 2: Total Screening Score]

Example Color Formula (Reference Only)

CASE
 WHEN [Tab 2: Total Screening Score] >= 15 THEN '16766160'
 WHEN [Tab 2: Total Screening Score] BETWEEN 8 AND 14 THEN '6881294'
 ELSE '16724787'
END

Why Advanced Formula Is Required

• Prevents overlapping conditions
• Ensures one visual outcome
• Scales cleanly with additional thresholds

Common Extensions

• Combine with visibility rules
• Highlight only on locked forms
• Apply across multiple fields

[Accordion] Example 5: Conditional Visibility Based on Age and Score

Use Case: Show additional assessment questions only when:

The patient is a minor AND the risk score exceeds a threshold

Fields Referenced

[Tab 1: Calculated Age]
[Tab 2: Total Screening Score]

Example Visibility Formula (Reference Only)

CASE
 WHEN [Tab 1: Calculated Age] < 18
      AND [Tab 2: Total Screening Score] >= 10
 THEN 'Y'
 ELSE 'N'
END

Why Advanced Formula Is Required

• Combines multiple dependent conditions
• Maintains readability
• Avoids chained basic rules

Common Extensions

• Make follow-up fields required
• Trigger guardian consent sections
• Apply different logic for adults

[Accordion] Example 6: Environment-Aware Logic Based on Logged-In User

Use Case: Display a field or message only when the logged-in user is a Supervisor.

Fields Referenced

[Environment Variables: Current Logged in User Type]

Example Visibility Formula (Reference Only)

CASE
 WHEN [Environment Variables: Current Logged in User Type] = 'Supervisory'
 THEN 'Y'
 ELSE 'N'
END

Why Advanced Formula Is Required

• Uses environment context
• Cannot be replicated with basic formulas
• Supports role-aware workflows

[Accordion] Example 7: Flag Patients Without an Appointment in the Current Phase

Use Case: Identify patients who do not have any appointments scheduled or completed in the current treatment phase. This is commonly used to flag gaps in care during transitions between phases.

Fields Referenced

[q_numKeptAppointmentsByPatPhase : numKeptApps]

Example Data Formula (Reference Only)

CASE
 WHEN [q_numKeptAppointmentsByPatPhase : numKeptApps] =0
 THEN 'No Appointment in Current Phase'
 ELSE 'Appointment Exists'
END

Why Advanced Formula Is Required

• References phase-specific appointment data
• Cannot be evaluated using only current-form fields
• Supports phase-aware clinical monitoring

Common Extensions

• Trigger care coordinator follow-up
• Highlight patient on dashboards
• Lock phase completion until appointment exists

[Accordion] Example 8: Days Since Last Appointment in Current Phase

Use Case: Calculate how long it has been since the patient was last seen within the current phase, ignoring appointments from prior phases.

Fields Referenced

[Patient Demographics With Phase : LastKeptAppDate]
[Environment Variables: Today]

Example Data Formula (Reference Only)

CASE
 WHEN [Patient Demographics With Phase : LastKeptAppDate] IS NOT NULL
 THEN DATEDIFF(
      day,
      [Patient Demographics With Phase : LastKeptAppDate],
      [Environment Variables: Today]
 )
 ELSE 'NULL'
END

Why Advanced Formula Is Required

• Combines phase-aware data with date math
• Enables phase-specific engagement tracking
• Prevents cross-phase logic errors

Common Extensions

• Color-code values above thresholds
• Trigger re-engagement workflows
• Compare against treatment standards

[Accordion] Example 9: Highlight Phase Compliance Using a Color Formula

Use Case: Visually indicate whether a patient is compliant with appointment expectations within the current phase.

Fields Referenced

[q_numKeptAppointmentsByPatPhase]

Example Color Formula (Reference Only)

CASE
 WHEN [q_numKeptAppointmentsByPatPhase : numKeptApps] =0 THEN '16766160'
 WHEN [q_numKeptAppointmentsByPatPhase : numKeptApps] > 0 AND [q_numKeptAppointmentsByPatPhase : numKeptApps] < 3 THEN '6881294'
 ELSE '16724787'
END

Why Advanced Formula Is Required

• Evaluates numeric thresholds
• Applies priority-based logic
• Ensures consistent visual cues

[Accordion] Example 10: Require Supervisor Notes for High-Risk Scores

Use Case: Require a Supervisor Review Notes field only when a patient’s screening score indicates high clinical risk.

Fields Referenced

[Tab 2: Total Screening Score]
[Tab 3: Supervisor Review Notes]

Example Required Formula (Reference Only)

CASE
 WHEN [Tab 2: Total Screening Score] >= 15
 THEN 'Y'
 ELSE 'N'
END

Why Advanced Formula Is Required

• Required status depends on a calculated value
• Threshold-based logic must be explicit
• Avoids multiple conflicting basic rules

Common Extensions

• Pair with supervisor signature requirement
• Lock form after completion
• Trigger alerts for high-risk cases

[Accordion] Example 11: Require Guardian Consent for Minors

Use Case: Require a Guardian Consent Signature field only when the patient is a minor.

Fields Referenced

[Tab 1: Calculated Age]
[Tab 4: Guardian Consent Signature]

Example Required Formula (Reference Only)

CASE
 WHEN [Tab 1: Calculated Age] < 18
 THEN 'Y'
 ELSE 'N'
END

Why Advanced Formula Is Required

• Depends on derived data (age)
• Cannot be reliably enforced with static rules
• Automatically adapts as the patient ages

Common Extensions

• Hide guardian fields for adults
• Combine with patient signer logic
• Lock consent forms when completed

[Accordion] Example 12: Require Clinical Justification When Backdating Forms

Use Case: Require a Clinical Justification field when a form is completed on a date other than today.

Fields Referenced

• [Tab 1: Assessment Date]
• [Environment Variables: Today]
• [Tab 3: Clinical Justification]

Example Required Formula (Reference Only)

CASE
 WHEN [Tab 1: Assessment Date] <> [Environment Variables: Today]
 THEN 'Y'
 ELSE 'N'
END

Why Advanced Formula Is Required

• Compares form data with system date
• Prevents undocumented backdated entries
• Supports audit and compliance workflows

Common Extensions

• Lock form after supervisor sign-off
• Log audit metadata
• Display warning messages

[Accordion] Example 13: Require Additional Assessment Based on Phase and Score

Use Case: Require completion of a Secondary Assessment field when:

• The patient is in a specific phase
• AND the risk score exceeds a threshold

Fields Referenced

[Patient Demographics with Phase: Phase_ID]
[Tab 2: Total Screening Score]
[Tab 4: Secondary Assessment]

Example Required Formula (Reference Only)

CASE
 WHEN [Patient Demographics with Phase: Phase_ID] = 1
      AND [Tab 2: Total Screening Score] >= 10
 THEN 'Y'
 ELSE 'N'
END

Why Advanced Formula Is Required

• Combines phase context and clinical scoring
• Cannot be modeled with single-condition rules
• Keeps forms phase-relevant and efficient

[Accordion] Need Help, Contact Us!