Formula fields — data types, calculations, and the AI workflow
Common transformations belong in the app, not in every report. Data-type overrides for dates and currency. SQL formula fields for calculations and date parts. An AI workflow that does most of the writing for you.
By the end of this lesson
- Sensible defaults on existing fields (dates, currency, display)
- Custom formula fields for the calculations your reports keep needing
- An AI workflow that writes most of the SQL for you
You'll need
- App Builder role on the app
- A working app with categories and tables (from Build your first app)
- Which database platform and version you're on (Postgres, MySQL, SQL Server, etc.)
Background
When a Composer needs a derived value — a month name pulled from a date, age in days between two timestamps, a numeric column displayed with a dollar sign — there are three places to put that logic, and the choice matters.
Where does this formula belong?
A two-question test before you write any formula:
- Is this just a display change? Date shown as date-only, number shown as currency, percent symbol. → Tier 1, data-type override. Fast path: this lesson's step 1 for the App-Builder side that affects every report; Module 7's report-formulas step 1 for the Composer-only version that affects just one report.
- Will more than one report or one Composer need this same formula? Yes → Tier 2, App Builder formula field (this lesson, steps 2–7). No → Tier 3, Composer formula (Module 7). Rule of thumb: 1–2 reports stay in Composer; 3+ get promoted to the app.
Promotion costs five minutes; recurring duplication costs hours. When in doubt, talk to your App Builder.
This lesson covers tiers 1 and 2 — the App Builder side. Tier 3 (the Composer's per-report formula box) is covered in Module 7's report-formulas lesson when you get there.
Two UI surfaces — they look similar, they aren't
Two places in the product expose formula tools. Knowing which one you're in is half the battle:
- Composer's Formula box — the "Add Formula" dialog inside the report builder. Has helper menus (Aggregate, String, DateTime, Math, Other) and the Insert Report Field option that gives you the exact
alias.columnreferences you need. This is where you'll prototype any formula before promoting it. - App Builder's Edit Field → SQL Name field — where promoted formulas live permanently. Accepts a raw SQL expression. No helper menus; you bring the formula in finished.
The workflow this lesson teaches: use Composer to discover the right field references and to prototype, then bring the working formula back to App Builder for permanent registration. You'll appreciate the speed of this loop the first time you skip it and have to round-trip through App Builder to debug a typo.
The one rule that bites every promotion: no ? in App Builder's SQL Name field.
Composer's Formula box accepts ? as a shortcut for "this card's field" — there's always a current card, so the token resolves cleanly. App Builder's SQL Name field has no card context (the formula is the field definition), so ? doesn't resolve. When you promote a Composer formula, replace every ? with the explicit [alias].[field] reference before pasting into App Builder. Step 2 below shows how to discover the right reference; the same value works in both places. Module 7's report-formulas lesson covers the Composer side of this rule.
Vocabulary you'll see in this lesson
SQL Name — the underlying expression DashboardFox sends to the database. Defaults to alias.column; you overwrite it with a SQL formula to register a derived field.
Display Name — the user-facing label. Always rename.
Data Type — how the value renders and behaves. Must match the formula's output (more on this below).
Formula box — Composer's per-report formula dialog. Where you prototype.
Insert Report Field — a menu item under the Formula box's function-type menu. Drops a properly-formed alias.column reference into your formula. Pure gold for non-technical users.
The data-type cheat sheet
The Data Type dropdown on every field controls rendering and which downstream operations work. Most fields auto-detect correctly; the ones that don't are usually dates and money columns. The codes you'll use most:
The dropdown has more codes than these for specialized use cases. The reference list lives at DashboardFox Data Types Explained in the help center; come back here for the workflow.
One organizational decision matters before you start: time-zone conversion is a policy, not a personal preference. Pick dat/dpo (browser-local, the default) or doz/dtz (no conversion) and apply it consistently across your app. The first pitfall below covers what happens when you don't.
Stuck on which path to take or what data type a formula should be? Email team@dashboardfox.com with the formula and what you're trying to display. We'll point you at the right code.
Do it
-
Set sensible defaults on existing fields
Start with the easy wins — fields that need a different render but no SQL. Open App Builder → your app → category → Report Tree. For each date field, click the action menu → Edit. Set the Data Type dropdown to your time-zone policy choice (
dozordtzfor no-conversion;datordpofor browser-local). Save.Same pattern for money columns: set them to
cur. Percentage columns:per. These changes flow into every existing report on app reload — Composers stop having to do per-report formatting.Reload the app from Composer (or have a Composer refresh their browser) and confirm the field renders the way you expect before moving on. If it doesn't, the second pitfall below covers the most common cause.
-
Discover the alias.column references in Composer
Before you write any SQL formula, you need the exact
alias.columnreferences for the fields you'll combine. Don't type them by hand from memory — DashboardFox auto-generates the table aliases (something likeservice_8066) and one wrong character means a SQL error.The trick: open Composer in your second tab, pick your app and category, and add the source fields to a scratch report. Then click Add Formula on any field's formula column. In the Formula box, type
@— a function-type menu appears with Aggregate, String, DateTime, Math, Other, and at the bottom, Insert Report Field. Pick that, then pick a field from the list. DashboardFox drops the alias-qualified reference into the box, formatted for your database:- MSSQL:
[service_8066].[ticket_open_time](square brackets) - PostgreSQL:
"service_8066"."ticket_open_time"(double quotes) - MySQL:
`service_8066`.`ticket_open_time`(backticks)
Composer picks the right quoting style automatically. Copy whatever it emits — that's the exact form you'll paste into App Builder's SQL Name field later.
Repeat for every field your formula will use. Copy the references — you're about to paste them into AI.
This trick is reusable far beyond formulas. Anytime you need an exact
alias.columnreference, Composer's Insert Report Field is faster than navigating App Builder's database view to find it. - MSSQL:
-
Generate the SQL with AI
Open your AI tool. Paste this template and fill in the bracketed parts:
I'm using [your database platform and version]. Output the formula expression only — no SELECT statement, no AS alias. One expression per line so I can copy and paste each one. Field references I have available (use these exactly as written, they're already quoted for my database): [paste the [alias].[column] references from Composer, one per line] What I want: [describe each formula you need, e.g.: - Month name from open time - Month number from open time - Day of week from open time - Minutes between open time and close time] Rules: - Preserve the field reference syntax exactly as shown above — don't reformat brackets/quotes/backticks. - For divisions, wrap the divisor with NULLIF to prevent divide-by-zero: numerator / NULLIF(divisor, 0). - For integer division that should return decimals, CAST one operand to DECIMAL/NUMERIC before dividing. - Use COALESCE to handle nulls where appropriate.Submit. AI returns one SQL expression per line. If it asks clarifying questions (some "deep thinking" models do — about UTC vs. server time, edge cases, etc.), answer them or just say "go with your recommendation" and re-run.
If a returned expression doesn't work in step 4, paste the error back to AI and ask for a fix. The iteration loop is fast — you're not the one writing SQL, you're reviewing it.
For recipes that go beyond what AI tends to nail on first try — safe-divide percentages, integer-to-decimal casting with per-DB syntax, NULL-safe CONCAT, dialect differences between MSSQL and Postgres — the SQL formula reference in Module 7 has the patterns written out.
-
Test the formula in Composer first
Before you save anything to App Builder, test the formula in Composer. In the same scratch report from step 2, paste one of AI's expressions into the Formula box (replacing what's there from step 2). Set the Data Type to match what the formula returns —
chafor a month name,numfor a calculation,dozfor a date. Click OK.If the column renders sensibly, the formula works. If it errors or shows weird values, fix the data type first (90% of issues), then iterate the SQL with AI if needed.
Testing here saves time. App Builder requires a save + app reload + Composer refresh to see results — slow loop. Composer's Formula box gives you immediate feedback on the same data.
-
Promote the working formula to App Builder
Now register it permanently. In App Builder, open the category's Report Tree and the folder where the new field belongs. Click Add Field. Pick any source field — it doesn't matter which, you're going to overwrite the SQL Name. Click Edit on the new field. Three things to set:
- Display Name — what users see. "Month opened", "Minutes open", "Customer name (capitalized)" — friendly, descriptive.
- SQL Name — paste the formula expression here, replacing the default
alias.columnvalue. - Data Type — match the formula's output.
cha/num/cur/per/dozas appropriate.
Save. The field is now part of the app — no further work needed.
-
Reload the app and verify in Composer
Switch to your Composer tab and reload the app (the icon at the top of the field picker, or just refresh the browser). Build a quick test — your new formula field should appear in the folder you put it in. Drop it into a report. If you set the data type to
num, try aggregating it (Average, Sum). Ifcha, try grouping by it. If a date type, try filtering by date range.If anything looks wrong, go back to step 4 and iterate. The Composer test loop is your acceptance test.
-
Batch the rest of your category
The AI prompt is reusable. One conversation can produce a dozen formula fields for the same category — date parts, common calculations, currency reformats. Generate them all in one batch, then promote them to App Builder one at a time (each gets its own Add Field + Edit + paste cycle). Twenty minutes of focused work and a category that took two hours to set up has all the polish your team will need for months.
If your formula field collection grows large enough to need its own organization, drop them into a
CalculationsorDate Partssubfolder per category. The bulk-move tool covered in App report tree makes the reorganization fast.
You have a working connection. Below is what to tighten before real users see it. Skip if you're just testing.
Make it real
Match the data type to what the formula returns
This is the most common silent breakage. A formula that returns a string (a month name, a concatenated label) needs cha. A formula that returns a number (a DateDiff, a ratio) needs num. A formula that returns a date needs dat, doz, dpo, or dtz — your call on the time-zone behavior.
Mismatches sometimes work in preview and break later — sorting goes alphabetic on what should be numeric, AVG fails silently, dates display oddly. The fix is always the same: edit the field, change the data type, save, reload. Two seconds when you catch it; minutes of confusion when a Composer hits it months later.
The AI prompt is the reusable artifact
The prompt template in step 3 isn't a one-time thing. Save it somewhere you'll find it again — a doc, a snippet manager, a pinned message. Every new category, every new data source, you re-run the same prompt with different field references and different "what I want" lines.
The pattern is forgiving: be specific about your platform, output format, and rules; vague about everything else. AI models handle SQL generation well even at smaller model sizes. Don't overthink which model to use.
Always test in Composer before promoting
App Builder's Edit Field SQL Name field has no preview. Once you save there, your only feedback is to reload the app from Composer and find out whether it worked. Composer's Formula box, by contrast, shows you the result on real data immediately.
The discipline is simple: prototype every formula in Composer's Formula box first. Confirm it renders. Then copy the working SQL into App Builder. Skipping this saves no time — debugging an App-Builder-saved formula is always slower than catching the same issue in Composer.
Time-zone is an organizational decision
Browser-local conversion (dat/dpo) and no conversion (doz/dtz) are both valid — they answer different questions. Browser-local: "what time was it for me when this happened?" No conversion: "what time was it on the server when this happened?" Both can be right; what's wrong is letting different fields in the same app behave differently.
Document the policy in a place your team will find it (a wiki page, a pinned doc, the app description). New formula fields added later should match. If you ever migrate from one to the other, do it as a planned change with notice — users notice when dates shift by a day.
Build a Calculations or Date Parts subfolder per category
Once you've added a handful of formula fields, group them. A Calculations subfolder for derived numbers (durations, ratios, ages). A Date Parts subfolder for month names, quarter labels, day-of-week breakdowns. Composers know exactly where to look.
Three tiers of nesting is fine — Tickets → Dates → Date Parts → Open month name reads cleanly. The 20-field-per-folder guidance from app report tree still applies; subfolder when you cross it.
Currency and percent rendering — small thing, big payoff
Setting a money column to cur renders the dollar sign automatically — no stylesheet hack, no per-report format string. Same for per on percentages. Trivial to do, eliminates a recurring per-report task that Composers find friction-y.
If your organization uses a non-USD symbol (£, €, ¥), the symbol can be styled at the instance level — covered in Module 5 branding. The Data Type code stays cur; only the rendered glyph changes.
Handle divide-by-zero and nulls in the prompt itself
SQL divide-by-zero is the classic broken-formula scenario. Don't catch it after the fact — bake the safety into your AI prompt. The template in step 3 already includes "Always wrap divisions with NULLIF to prevent divide-by-zero" and "Use COALESCE to handle nulls where appropriate". Keep them there.
If your dataset has known oddities — sentinel values like -1 for "unknown", strings like "N/A" in numeric columns — mention those in the prompt too. AI generates better SQL when it knows the data shape.
For the patterns that recur — safe divide, integer-to-decimal coercion, percentage calculations, NULL-safe CONCAT — the SQL formula reference in Module 7 has them written out per database. Bookmark it; come back when the next formula problem looks the same shape as one someone has already solved.
Use Composer to discover any alias.column reference
The Insert Report Field trick from step 2 is reusable far beyond formula authoring. Any time you need to know how a field is exposed to SQL — for a Direct SQL report, a stored-procedure parameter mapping, a row-level-security tag, or just to remember a table alias — open Composer, pick the field, type @ in any Formula box, and Insert Report Field hands you the answer.
This is faster than scrolling App Builder's database view, especially in apps with dozens of tables.
AI assistance is being built into App Builder
The external-tool workflow in this lesson is the current state. AI authoring is being built directly into App Builder — when it ships, the prompt-and-paste flow becomes a button. Until then, the manual workflow is the path. We'll update the lesson when the in-product version is available.
Reorganize fields with bulk move
Once you've built ten or twenty formula fields, you'll want to reorganize. Don't move them one at a time — App Builder's Report Tree supports bulk selection and a single move action. The mechanics are covered in App report tree → Bulk move and reorganize; the same checkboxes and move icon work whether the fields are formulas, source columns, or a mix.
If you're stuck
The classic stumbles, in roughly the order they show up.
My month-name (or other text) field shows a date error or weird values
The Data Type is still set to a date type. A formula returning a string needs cha. Edit the field, change the Data Type, save, reload. The formula itself is probably fine — it's the rendering that's wrong.
My DateDiff result won't aggregate — Average and Sum are missing
Same issue, opposite direction. The Data Type is set to character (cha) — DashboardFox doesn't offer numeric aggregations on text. Change to num. Aggregation options reappear immediately on app reload.
Some users see dates a day off, others don't
Default dat/dpo applies the user's browser time zone. East Coast and West Coast users will see different dates for the same timestamp. If your reporting needs everyone to see the same calendar date — month-end close, daily ticket counts, anything where consistency matters more than localization — use doz or dtz instead. This is an organizational policy decision, not a per-field one; document it and apply consistently.
AI gave me SQL that errors on my database
Dialect mismatch. DATENAME(month, ...) works on SQL Server but not PostgreSQL; TO_CHAR(..., 'Month') works on PostgreSQL and Oracle but not SQL Server. Tell AI exactly which platform and version ("Microsoft SQL Server 2019", "PostgreSQL 14") in the first line of the prompt. Re-run; the SQL will match your database.
My field references aren't resolving — "field not found" or similar
Two common causes:
- You used
?in App Builder's SQL Name field. The?token is a Composer-only convenience — it means "this card's field," but App Builder has no card context, so the token doesn't resolve. Replace every?with the explicit[alias].[field]reference (use Composer's Insert Report Field to discover the exact reference, as in step 2). The rest of the formula is unchanged. - You typed an
[alias].[column]reference by hand and got it wrong. Auto-generated aliases likeservice_8066are easy to mistype. Use Composer's Add Formula → @ → Insert Report Field to drop the exact reference into the formula. Don't transcribe by eye.
For either case, Insert Report Field in Composer is the source of truth — it emits the reference in the right format for your database (brackets on MSSQL, double quotes on Postgres, backticks on MySQL).
My formula divides by something and the report breaks on certain rows
A row has a zero in the divisor. Wrap the division with NULLIF — for example, numerator / NULLIF(divisor, 0). The result is null instead of an error, and reports keep running. Add this rule to your AI prompt permanently so future formulas come back safe.
Composers are still writing their own per-report formulas for things I thought I handled
Either the formula you promoted doesn't actually cover their case, or they don't know it exists. Talk to them. If their case is genuinely different (a calculation specific to one report), the per-report Composer formula is the right place — promotion only makes sense for genuinely common transformations. If your formula does cover it, the field's location or display name might be unhelpful — fix that. The app report tree guidance on naming and folder structure applies.
I changed a formula in App Builder and existing reports broke
Renaming the SQL Name (the formula expression) is fine — existing reports recalculate on next run. Changing the Data Type can break reports that depended on the old type — for example, switching cha to num on a field that's used as a group-by may now require rework. When changing data types on fields used in production reports, give Composers a heads-up. When in doubt, add a new field with the new type instead of mutating the old one — the old field can be deleted later once nothing references it.
None of these match my situation
Email team@dashboardfox.com with the formula you're trying to write, your database platform, and what error or wrong result you're seeing. We'll help you trace it. Same business day.
