— Computed Values in Forms
About Formulas
Dynamic Formulas in Widgets & Dashboards
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.
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 Name" and "Last Name" into a single "Full Name" field.
- Conditional Logic: Use
IF/ELSEstatements to categorize data (e.g., labeling a lead as "Cold," "Warm," or "Hot" based on a score). - Data Extraction: Pull specific information like day-of-the-week from a timestamp (Coming Soon).
Getting Started: Supported Operations
SwitchBase formulas support a wide range of functions similar to those you’d find in Excel or Google Sheets:
| Category | Capability | Example |
|---|---|---|
| Math | Basic arithmetic and calculations | [Subtotal] * 1.05 |
| Text | Combining or manipulating strings | [First Name] + " " + [Last Name] |
| Logic | Boolean operations and conditional checks | IF([Score] > 10, "High", "Low") |
| Dates | Date operations and extractions | (More operations coming soon) |
Important Considerations
Before you start building, please keep the following notes in mind:
- Field Support: Currently supports Number and Text fields.
- Platform Availability: Formula results are functional on Web Dashboards but are not yet available on the Mobile App.
- Automatic Updates: Formulas recalculate instantly whenever the source data in your record changes.
How to Set It Up
- Open Widget Settings: Navigate to the dashboard or widget where you want to add the calculation.
- Create or Edit a Field: Select the field you wish to power with a formula.
- Enable Formula Mode: Toggle the "Formula" option on within the field settings.
- Enter Your Expression: Reference your existing fields and apply your logic (e.g.,
[Field A] + [Field B]). - 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.
Formula Copy-Paste Examples
Use these templates to build your dynamic fields. Simply replace the bracketed text 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 Since Incident:
DAYS_BETWEEN([Today], [Incident Date])
🔵 Lead Management & Sales
Scenario: Clean up contact data and calculate potential deal values including tax or discounts.
- Full Name Formatter:
[First Name] + " " + [Last Name] - Total Contract Value (with 10% Discount):
([Unit Price] * [Quantity]) * 0.90 - Lead Temperature:
IF([Email Opened] == "Yes" AND [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:
([Tasks Completed] / [Total Tasks]) * 100
⌨️ Quick Syntax Rules
To ensure your formulas work every time, follow these formatting rules:
| Rule | Description | Example |
|---|---|---|
| Brackets | Always wrap field names in square brackets. | [Field Name] |
| Strings | Wrap plain text or labels in double quotes. | "Completed" |
| Math | Use standard operators: +, -, *, /. | [A] * [B] |
| Logic | Use AND, OR, and IF for conditional flows. | IF([A] == "Yes", 1, 0) |
Top 10 Essential Spreadsheet Formulas
These are the "Power Players" of Excel and Google Sheets. Mastering these ten formulas will allow you to handle roughly 90% of common data tasks, from simple math to complex data lookups.
| Rank | Formula | Description | Example |
|---|---|---|---|
| 1 | SUM | Adds a range of cells together. | =SUM(A1:A50) |
| 2 | AVERAGE | Calculates the arithmetic mean of a range. | =AVERAGE(B1:B10) |
| 3 | IF | Checks a condition; returns one value if TRUE and another if FALSE. | =IF(C1>100, "Over", "Under") |
| 4 | VLOOKUP | Searches for a value in a column and returns data from a corresponding row. | =VLOOKUP("ID_123", A1:D100, 3, FALSE) |
| 5 | COUNT / COUNTA | COUNT counts numbers; COUNTA counts all non-empty cells. | =COUNTA(A:A) |
| 6 | XLOOKUP | The modern, more flexible successor to VLOOKUP. | =XLOOKUP(E1, A:A, B:B) |
| 7 | CONCATENATE / & | Joins two or more text strings together into one. | =A1 & " " & B1 |
| 8 | SUMIF / SUMIFS | Adds cells that meet specific (or multiple) criteria. | =SUMIFS(A:A, B:B, ">50", C:C, "Paid") |
| 9 | PROPER / UPPER | Cleans up text casing (e.g., capitalizing the first letter of words). | =PROPER("john smith") |
| 10 | UNIQUE | Returns a list of all unique values in a range, removing duplicates. | =UNIQUE(D1:D500) |
How to Apply These in SwitchBase
While spreadsheet software uses cell references (like A1), SwitchBase Formulas use [Field Names].
- Excel:
=IF(A1 > 10, "Yes", "No") - SwitchBase:
IF([Score] > 10, "Yes", "No")
[!TIP] Pro-Tip: If you are migrating a complex report from Excel to a SwitchBase Dashboard, start by identifying your SUMIFS and IF logic—these are the most common formulas used to power our real-time widgets.
Combine and Nest multiple formulas to create a more advanced calculation
Integrating multiple formulas (known as nesting) allows you to handle complex logic in a single field. The most common use case is a Nested IF statement, which allows for more than two possible outcomes.
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"))
Combining Text and Math (Nesting CONCAT)
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:
[Last Name] + " - " + IF([Score] > 50, "Priority", "Standard") + " (" + [Score] + "pts)"
Result: Smith - Priority (85pts)
Visualizing Nested Logic
[Image of a nested if statement flow chart]
3 Golden Rules for Nesting
- Count Your Parentheses: Every
(you open must have a matching)at the end. In a double-nestedIF, you will usually end the formula with)). - Order Matters: SwitchBase (and Excel) reads formulas from left to right. It will stop at the first true statement it finds. Always put your most specific or highest-value criteria first.
- Keep it Readable: If a formula gets too long (more than 3 or 4 nests), it becomes difficult to troubleshoot. Consider breaking complex logic into two separate helper fields.
| Feature | Simple Formula | Nested Formula |
|---|---|---|
| Outcomes | 2 (Yes/No) | Unlimited (Tiers, Categories) |
| Complexity | Low | Medium to High |
| Maintenance | Easy | Requires careful syntax checking |