Formulas & Computed Values

About Formulas

Calculated fields have arrived in SwitchBase! You can now create fields powered by formulas that automatically calculate values based on other data in your records.

Think of it as spreadsheet-style power directly within your workflow—ensuring your data stays clean, consistent, and updates itself in real-time.

For the complete list of functions, operators, and syntax details, see the Formula Language Reference.

---

Why Formulas Matter

Manual calculations are prone to error and quickly become outdated. By using formulas, you eliminate the need for duplicate fields and manual entry—your data stays clean and updates itself.

Common use cases include:

• Automatic Totals: Instantly sum up costs, quantities, or scores.
• Text Combinations: Merge fields like first and last name into a single "Full Name" field.
• Conditional Logic: Use IF / IFS / SWITCH to categorize data (e.g., labeling a lead as "Cold," "Warm," or "Hot" based on a score).
• Date Calculations: Compute differences between dates, format timestamps, or convert timezones (note: NOW() and TODAY() are not supported in computed fields).
• Data Transformation: Filter, map, and aggregate arrays of records.

---

Getting Started: Supported Operations

SwitchBase formulas support a wide range of functions. See the full reference for details.

---

Important Considerations

Before you start building, please keep the following notes in mind:

• Automatic Updates: Formulas recalculate instantly whenever the source data in your record changes.
• Date Limitations: Dynamic date functions like NOW() and TODAY() are not supported in computed fields. You can still use date math functions like DATE_DIFF, DATE_ADD, and FORMAT_DATE with date fields on your records.

---

How to Set It Up

1. Open Widget Settings: Navigate to the dashboard or widget where you want to add the calculation.
2. Create or Edit a Field: Select the field you wish to power with a formula.
3. Enable Formula Mode: Toggle the "Formula" option on within the field settings.
4. Enter Your Expression: Reference your fields using @ syntax and apply your logic (e.g., @field_a + @field_b).
5. Save & Refresh: Once saved, the widget will begin calculating values for all existing and new records.

---

[!TIP] Pro-Tip: Keep it Clean Use formulas to create "Summary Display Fields." Instead of cluttering your dashboard with multiple data entry points, use one formula to combine them into a single, easy-to-read summary widget.

---

Quick Syntax Rules

To ensure your formulas work every time, follow these formatting rules:

For the complete syntax and operator reference, see the Formula Language Reference.

---

Formula Copy-Paste Examples

Use these templates to build your dynamic fields. Simply replace the @field references with your specific field names.

---

Incident Reporting & Safety

Scenario: Automatically calculate a "Risk Level" based on severity scores to prioritize responses.

• Risk Level Category: IF(@severity_score >= 8, "Critical", IF(@severity_score >= 5, "Elevated", "Low"))
• Days Between Dates: DATE_DIFF(@end_date, @start_date, "days")

---

Lead Management & Sales

Scenario: Clean up contact data and calculate potential deal values including tax or discounts.

• Full Name Formatter: CONCAT(@first_name, " ", @last_name)
• Total Contract Value (with 10% Discount): (@unit_price * @quantity) * 0.90
• Lead Temperature: IF(@email_opened == "Yes" && @demo_scheduled == "Yes", "Hot", "Cold")

---

Project Tracking & Operations

Scenario: Monitor budget health and task status automatically.

• Budget Remaining: @total_budget - @actual_spend
• Over Budget Warning: IF(@actual_spend > @total_budget, "OVER BUDGET", "On Track")
• Completion Percentage: ROUND((@tasks_completed / @total_tasks) * 100, 1)

---

Data Transformation

Scenario: Work with arrays and collections of records.

• Active User Names: JOIN(MAP(FILTER(@users, $.active), $.name), ", ")
• Total Order Value: SUM(MAP(@orders, $.total))
• Categorize by Score: IFS(@score > 90, ‘A’, @score > 80, ‘B’, @score > 70, ‘C’, true, ‘F’)

---

Advanced Logic: Nesting Formulas

When a simple TRUE or FALSE isn’t enough, you can "nest" one formula inside another. This is most commonly used to create multiple tiers of data categorization.

---

The "Nested IF" Logic

A standard IF statement has three parts: IF(test, if_true, if_false). To add a third or fourth option, you simply replace the if_false section with a second IF statement.

Scenario: Categorizing a Project Budget status.

• Over 100%: Over Budget
• 80% to 100%: Warning
• Under 80%: On Track

The Formula: IF(@spend > @budget, "Over Budget", IF(@spend > (@budget * 0.8), "Warning", "On Track"))

[!TIP] For formulas with many branches, consider using IFS or SWITCH instead of deeply nested IF statements. See the Formula Language Reference for details.

---

Combining Text and Math

You can also nest math operations inside text strings to create dynamic summary labels.

Scenario: Creating a "Status Summary" field for a lead. The Formula: CONCAT(@last_name, " - ", IF(@score > 50, "Priority", "Standard"), " (", @score, "pts)")

Result: Smith - Priority (85pts)

---

3 Golden Rules for Nesting

1. Count Your Parentheses: Every ( you open must have a matching ) at the end. In a double-nested IF, you will usually end the formula with )).
2. Order Matters: Formulas are read left to right. The first true condition wins. Always put your most specific or highest-value criteria first.
3. Keep it Readable: If a formula gets too long (more than 3 or 4 nests), consider using IFS or SWITCH, or breaking complex logic into two separate helper fields.

---

▲ Top

---