--- title: Cheat Sheet slug: cheat-sheet docTags: createdAt: 2026-03-03T19:00:26.156Z --- # ChartHop CQL (Carrot) Formula Cheat Sheet :::BlockQuote CQL (Carrot Query Language) is ChartHop's built-in expression language for searching, filtering, calculating, and displaying people data. The same logic works everywhere โ€” only the syntax wrapper changes. ::: ## ๐Ÿงฉ Where Are You Writing This? | Context | Syntax | Example | | ---------------------------------- | ----------------------------- | --------------------------------- | | Smart Calc / Smart Bucket | Plain expression | base \* fieldCode1 | | Data Sheet calculated column | Plain expression | diffYears(startDate, today()) | | Form content block | \{\{ }} | \{\{formatMoney(base)}} | | Form (live answer from same form) | formAnswers\['fieldCode'] | formAnswers\['fieldCode1'] \* 100 | | Document / letter template | \{\{ }} | \{\{name}} | | Markdown (profile tabs, home page) | \{\{ }} values ยท \{% %} logic | See Markdown section | | Dashboard chart (Advanced mode) | \{\{ }} | \{\{base / fieldCode1}} | ## โšก Operators | Symbol | Meaning | Example | | ---------------- | -------------------------------------- | -------------------------------------------------------- | | = | Exact match | department.func='Engineering' | | != | Not equal | status!='Inactive' | | : | Contains / fuzzy match | title:'Director' | | > \< >= \<= | Comparison | base>100000 | | && or and | AND (interchangeable in most cases) | department.func='Sales' && base>80000 | | \|\| | OR | department.func='Sales' \|\| department.func='Marketing' | | ! | NOT | !department:'Sales' | | fieldCode:\* | Has any value | fieldCode1:\* | | ? | Ternary โ€” if/then/else | base > 100000 ? 'High' : 'Low' | | ?: | Elvis โ€” value if exists, else fallback | fieldCode1 ?: 0 | | dateOf.fieldCode | Date the field was last written | dateOf.base | :::BlockQuote **&& vs and:** These work interchangeably in most filter and Smart Field contexts. If a formula behaves unexpectedly, try switching to &&. ::: ## ๐Ÿ“ Performance & Goals **Weight an individual goal's rating by its assigned weight** :::BlockQuote fieldCode1 \* (fieldCode2 ?: 0) ::: :::BlockQuote fieldCode1 = goal rating ยท fieldCode2 = goal weightage ยท ?: 0 prevents a blank weight from breaking the formula. Create one Smart Calc per goal, then sum them for the total. ::: **Total weighted goal score** :::BlockQuote round(fieldCode1 + fieldCode2 + fieldCode3, 2) ::: :::BlockQuote Each fieldCode = a per-goal weighted rating Smart Calc field. Add a term for each goal your org uses. Keeping this as its own field makes it easy to reference in forms and the final rating calc. ::: **Total performance rating (goals + values)** :::BlockQuote round((fieldCode1 \* 0.80) + (fieldCode2 \* 0.20), 2) ::: :::BlockQuote fieldCode1 = total weighted goal score ยท fieldCode2 = values/brand score. Adjust the 0.80/0.20 split to match your org's weighting โ€” multipliers must sum to 1.0. ::: **Average only the goals that were actually used (skip zeros and blanks)** :::BlockQuote mean( fieldCode1 > 0 ? fieldCode1 : null, fieldCode2 > 0 ? fieldCode2 : null, fieldCode3 > 0 ? fieldCode3 : null ) ::: :::BlockQuote Extend with fieldCodeN > 0 ? fieldCodeN : null for each additional goal. mean() skips nulls but not zeros โ€” this pattern ensures unused goals don't drag the average down. ::: **Combine multiple rating categories into one weighted score** :::BlockQuote ((fieldCode1 ?: 0) \* 0.50) +((fieldCode2 ?: 0) \* 0.30) + ((fieldCode3 ?: 0) \* 0.20) ::: :::BlockQuote Weights must sum to 1.0. ?: 0 ensures a missing rating doesn't nullify the entire formula. ::: **Count how many times an employee hit a specific rating in recent cycles** :::BlockQuote findHistoryValues(\{fieldCode1}).reversed.limit(6).count\{it='Meets Expectations'} ::: Full history (no limit): :::BlockQuote findHistoryValues(\{fieldCode1}).count\{it='Exceeds Expectations'} ::: :::BlockQuote .reversed = most recent first ยท .limit(N) = last N cycles ยท Always use it inside .count\{} โ€” not the original field code. Gives reviewers a consistency signal, not just the most recent result. ::: **Show a rating only if it was submitted before a review cutoff date** :::BlockQuote \{\{dateOf.fieldCode1\<='2024-11-10' ? fieldCode1 : null}} ::: :::BlockQuote Use in a form content block. Repeat for each rating field. Prevents backdated or late entries from surfacing during calibration. ::: ## ๐Ÿ’ฐ Compensation **Compa-ratio** โ€” where someone sits relative to their band midpoint :::BlockQuote base / fieldCode1 ::: :::BlockQuote fieldCode1 = band midpoint. Result of 1.0 = at midpoint. Format as percent for display. Foundation for pay equity analysis, merit planning, and outlier flagging. ::: **Salary range penetration** โ€” how far through the band (0 = min, 1 = max) :::BlockQuote (base - fieldCode1) / (fieldCode2 - fieldCode1) ::: :::BlockQuote fieldCode1 = band min ยท fieldCode2 = band max. Useful alongside compa-ratio when bands are wide. ::: **Total cash compensation** (base + target bonus) :::BlockQuote base + ((fieldCode1 ?: 0) \* base) ::: :::BlockQuote fieldCode1 = target bonus percentage field. ::: **New base after a merit increase** :::BlockQuote base + (base \* fieldCode1) ::: :::BlockQuote fieldCode1 = merit percentage field. Best used as a Data Sheet calculated column during comp planning โ€” no permanent field needed. ::: **Prorated salary for a mid-year hire** :::BlockQuote base \* (diffDays(startDate, '2026-01-01') / 365) ::: :::BlockQuote Replace '2026-01-01' with your fiscal year-end date. ::: **Prorated annual cost with start and end date logic (full fiscal year)** :::BlockQuote startDateJob > date('2026-01-01') ? (endDateJob ? monthlyCost \* 12 \* (endDateJob - startDateJob + 1) / 365 : monthlyCost \* 12 \* (date('2026-12-31') - startDateJob + 1) / 365 ) : (endDateJob && endDateJob \< date('2026-12-31') ? monthlyCost \* 12 \* (endDateJob - date('2026-01-01') + 1) / 365 : monthlyCost \* 12 ) ::: :::BlockQuote Use this for scenario planning and headcount cost forecasting. Handles mid-year starts, mid-year terms, and full-year employees in one expression. To show proration as a % of annual cost: proratedFyCost / (monthlyCost \* 12) \* 100. Update the year dates each cycle. ::: **Prior salary** โ€” what someone earned before their last increase :::BlockQuote asOfPrimary(dateOf.base - 1, \{base}) ::: :::BlockQuote dateOf.base = date of last base change. Subtracting 1 day retrieves the value just before it. No custom fields needed. ::: **Year-over-year base salary change** As a percentage: :::BlockQuote (base - asOfPrimary('2025-01-01', \{base})) / asOfPrimary('2025-01-01', \{base}) ::: As a dollar amount: :::BlockQuote base - asOfPrimary('2025-01-01', \{base}) ::: :::BlockQuote Replace '2025-01-01' with your baseline date. Use asOfPrimary โ€” not asOf โ€” to exclude draft scenario data. ::: **Total comp including equity vesting** :::BlockQuote cashComp + vestValue(today(), nextAnniversary(startDate)) ::: :::BlockQuote Built-in fields only. Adds total cash to the value of equity vesting in the next 12 months. ::: ## ๐Ÿ“… Tenure, Dates & Eligibility | Question | Formula | | ---------------------------------------------- | ---------------------------------------------- | | How long has someone been here? | diffYears(startDate, today()) | | How long in their current role? | diffDays(titleDate, today()) | | When is their next work anniversary? | nextAnniversary(startDate) | | How old is this employee? | diffYears(birthdate, today()) | | Did their manager change recently? | diffDays(dateOf.manager, today()) \<= 90 | | When were they last eligible for a raise? | dateOf.base | | When will they next be eligible? | dateOf.base + 365 | | How long has this req been open? | diffDays(openDate, today()) | | When did this person become a manager? | dateOf.directReports | | Did they become a manager in the last 30 days? | diffDays(dateOf.directReports, today()) \<= 30 | ## ๐Ÿชฃ Smart Bucket Templates :::BlockQuote Smart Buckets assign a color-coded label based on CQL conditions. Use them on the org chart, Data Sheet, and dashboards for instant visual grouping. ::: **Performance tiers** ยท fieldCode1 = weighted score field | Label | Expression | | ------------------ | -------------------------------------- | | High Performer | fieldCode1 >= 4.5 | | Strong Performer | fieldCode1 >= 3.5 && fieldCode1 \< 4.5 | | Meets Expectations | fieldCode1 >= 2.5 && fieldCode1 \< 3.5 | | Needs Improvement | fieldCode1 \< 2.5 | **Compa-ratio bands** ยท fieldCode1 = compa-ratio Smart Calc field | Label | Expression | | ---------- | ----------------------------------------- | | Below Band | fieldCode1 \< 0.80 | | In Range | fieldCode1 >= 0.80 && fieldCode1 \<= 1.20 | | Above Band | fieldCode1 > 1.20 | **Tenure bands** ยท Uses built-in startDate | Label | Expression | | ---------------------- | ------------------------------------------------------------------------ | | New Hire (\<1 yr) | diffYears(startDate, today()) \< 1 | | Early Career (1โ€“3 yrs) | diffYears(startDate, today()) >= 1 && diffYears(startDate, today()) \< 3 | | Established (3โ€“5 yrs) | diffYears(startDate, today()) >= 3 && diffYears(startDate, today()) \< 5 | | Veteran (5+ yrs) | diffYears(startDate, today()) >= 5 | **Merit eligibility** ยท fieldCode1 = performance rating field | Label | Expression | | -------------------- | ------------------------------------------------------ | | Eligible | diffMonths(startDate, today()) >= 6 && fieldCode1 >= 3 | | Ineligible โ€“ Too New | diffMonths(startDate, today()) \< 6 | | Ineligible โ€“ Rating | fieldCode1 \< 3 | **Flight risk** ยท fieldCode1 = band minimum field | Label | Expression | | ----------- | ------------------------------------------------------------ | | High Risk | diffMonths(titleDate, today()) >= 24 && base \< fieldCode1 | | Medium Risk | diffMonths(titleDate, today()) >= 18 \|\| base \< fieldCode1 | | Low Risk | diffMonths(titleDate, today()) \< 18 && base >= fieldCode1 | **Span of control** ยท Uses built-in directReports ยท Apply to Jobs | Label | Expression | | --------------------- | --------------------------------------------------------- | | Under-leveraged (1โ€“3) | length(directReports) >= 1 && length(directReports) \<= 3 | | Healthy (4โ€“8) | length(directReports) >= 4 && length(directReports) \<= 8 | | Over-leveraged (9+) | length(directReports) >= 9 | **Open req age** ยท Uses built-in openDate | Label | Expression | | ------------------- | ---------------------------------------------------------------------- | | Fresh (\<30 days) | diffDays(openDate, today()) \< 30 | | Active (30โ€“60 days) | diffDays(openDate, today()) >= 30 && diffDays(openDate, today()) \< 60 | | Aging (60โ€“90 days) | diffDays(openDate, today()) >= 60 && diffDays(openDate, today()) \< 90 | | Stale (90+ days) | diffDays(openDate, today()) >= 90 | ## ๐Ÿ“‹ Forms: Embedding Live CQL Data ### How to add a CQL content block to a form 1. Go to **People Ops Tools โ†’ Forms** โ†’ open or create a form 2. Click **+ Add Question / Block** โ†’ select **Content** block type 3. Type your text and embed CQL using \{\{expression}} syntax 4. Save and preview โ€” expressions render live against the employee the form is about :::BlockQuote Content blocks are read-only. They display data but cannot be edited by the reviewer. Use formAnswers\['fieldCode'] (instead of just fieldCode) when reading a value entered earlier in the same form session. ::: **Employee context card** โ€” put this at the top of any review or comp form :::BlockQuote Name: \{\{name}} | Title: \{\{title}} | Dept: \{\{department.name}}Tenure: \{\{formatRound(diffYears(startDate, today()), 1)}} years Current Base: \{\{formatMoney(base)}} Band: \{\{formatMoney(fieldCode1)}} โ€“ \{\{formatMoney(fieldCode2)}} Compa-Ratio: \{\{formatRound(base / fieldCode3, 2)}} Last Rating: \{\{asOfPrimary('2024-07-01', \{fieldCode4})}} ::: :::BlockQuote Replace fieldCode1โ€“4 with your band min, band max, midpoint, and rating fields. ::: **Compensation flag** โ€” auto-surface outliers to the reviewer :::BlockQuote \{\{(base / fieldCode1) \< 0.80 ? 'โš ๏ธ Below band minimum โ€” flag for discussion.' : 'โœ… Within compensation band.'}} ::: **Prior vs. current salary** :::BlockQuote Prior Base: \{\{formatMoney(asOfPrimary(dateOf.base - 1, \{base}))}}Current Base: \{\{formatMoney(base)}} ::: **YoY salary change** :::BlockQuote YoY Base Change: \{\{formatPercent((base - asOfPrimary('2025-01-01', \{base})) / asOfPrimary('2025-01-01', \{base}))}} ::: **Total weightage validation** โ€” confirm goal weights sum to 100% :::BlockQuote Total Weightage: \{\{(fieldCode1\*100)+(fieldCode2\*100)+(fieldCode3\*100)}}% ::: :::BlockQuote Extend with +(fieldCodeN\*100) for each additional goal weightage field. ::: **List a manager's direct reports inline** โ€” useful for manager attestation forms :::BlockQuote \{% assign directs = db.job.find\{it.manager=person && is\:person} %}\{% for direct in directs %} \{\{direct.name}} โ€” \{\{direct.title}} \{% endfor %} ::: :::BlockQuote Great for single manager attestation forms where you want the reviewer to see all their reports in one place without submitting separately per person. ::: ## ๐Ÿ“ Markdown: Conditional Display :::BlockQuote Use in profile tabs, the home page, and document templates. \{\{ }} renders a value. \{% %} controls show/hide logic. ::: **Show a block only if a condition is true** :::BlockQuote \{% if fieldCode1 >= 4 %}โญ High performer โ€” eligible for promotion discussion. \{% endIf %} ::: **Show different content based on a condition** :::BlockQuote \{% if diffYears(startDate, today()) >= 1 %}Eligible for annual review. \{% else %} Not yet eligible โ€” less than 1 year of tenure. \{% endIf %} ::: **Show content only if a field was set on or before a specific date** :::BlockQuote \{% if ((fieldCode1:\* && dateOf.fieldCode1='2024-11-15') ? asOfPrimary('2024-11-15', \{fieldCode1}) : null) %}Rating (as of Nov 15): \{\{asOfPrimary('2024-11-15', \{fieldCode1})}} \{% endIf %} ::: :::BlockQuote fieldCode1:\* confirms a value exists ยท dateOf.fieldCode1 confirms when it was written ยท asOfPrimary retrieves the locked value. ::: **Nested conditions** :::BlockQuote \{% if department.func='Engineering' %} \{% if fieldCode1 >= 4 %} High-performing engineer โ€” flag for promotion discussion. \{% endIf %} \{% endIf %} ::: ## ๐Ÿ” Common Filter Queries | Expression | What it returns | | ---------------------------------------------- | -------------------------------- | | is\:active | All active employees | | is\:manager | People managers only | | !is\:manager | Individual contributors only | | department.func='Engineering' | Specific department | | !department:'Sales' | Exclude a department | | startDate\<'2022-01-01' | Started before a date | | !fieldCode1:\* | Missing a specific field value | | fieldCode1>=4 | Field at or above a threshold | | (base / fieldCode1) \< 0.90 | Below 90% compa-ratio | | diffYears(startDate, today()) >= 3 | 3+ years tenure | | diffMonths(titleDate, today()) >= 18 | No title change in 18+ months | | diffDays(dateOf.manager, today()) \<= 90 | Manager changed in last 90 days | | diffDays(dateOf.directReports, today()) \<= 30 | Became a manager in last 30 days | | is\:open && daysOpen>90 | Stale open reqs | | !location:\* | Missing location | | fieldCode1 >= 4 && (base / fieldCode2) \< 0.90 | High performer, underpaid | | anniversary=today | Work anniversary is today | | endDateOrg=today+11 | Departing in exactly 11 days | ## โš™๏ธ Actions & Approval Chains :::BlockQuote Carrot powers both the filters that determine who receives an action and the conditional logic that controls whether an approval stage fires. These are some of the most common patterns. ::: **Trigger an action on a specific date relative to end date** :::BlockQuote endDateOrg=today+11 ::: :::BlockQuote Use = not \<= for scheduled actions. Using \<= causes the action to fire every day until the date arrives. Replace 11 with however many days of lead time you need. ::: **Trigger an action on work anniversary** :::BlockQuote anniversary=today ::: :::BlockQuote For orgs that use a custom hire date field instead of the built-in startDate, create a Smart Calc using nextAnniversary(customHireDateField) and filter on that field equaling today instead. ::: **Filter action audience to people who have not yet completed a form** :::BlockQuote findFormTasksByAssessment('Review Name', 'Form Name').filter\{with(person,jobFilter) && status\:pending}.count() > 0 ::: :::BlockQuote Use this as a scheduled action filter to send reminders only to people who still have outstanding tasks. Swap status\:pending for status\:done to target completers instead. ::: ### Conditional Approval Stage Expressions :::BlockQuote The expressions below go in the **"Only include stage if"** field on an approval stage. They control whether a given stage fires at all. Always test your expression as a filter on the scenario Changes tab first โ€” if it returns results there, it will fire in the approval chain. ::: **Trigger only if a specific field changed** :::BlockQuote change.before.base != change.after.base ::: :::BlockQuote Combine multiple fields with ||: (change.before.base != change.after.base) || (change.before.fieldCode1 != change.after.fieldCode1) ::: **Trigger only if manager changed** :::BlockQuote change.before.manager != change.after.manager ::: **Trigger based on total cost impact across all changes in a scenario** :::BlockQuote scenarioChanges.sum\{change.cost} > 500000 ::: :::BlockQuote Use when you want approval based on the combined salary impact of all changes, not just a single job. For example: require VP approval when the total cost of a scenario exceeds $500,000. ::: **Trigger if any change in the scenario affects a specific department** :::BlockQuote scenarioChanges.any\{department='Engineering'} ::: :::BlockQuote Use when at least one change in the batch needs to match โ€” even if others don't. Good for routing Engineering Director approval whenever any Engineering job is touched. ::: **Trigger only if every change in the scenario is in a specific department** :::BlockQuote scenarioChanges.all\{department='Engineering'} ::: :::BlockQuote Stricter than .any\{} โ€” the stage only fires if the entire scenario batch is within that department. Skip it if the batch is mixed. ::: **Trigger based on both scenario content and who is submitting** :::BlockQuote scenarioChanges.all\{department='Engineering'} && title='CEO' ::: :::BlockQuote Combines a condition about the changes with a condition about the submitter. In this example: only include this stage when all changes are in Engineering AND the person submitting is the CEO. Mix and match to build precise routing logic. ::: **Trigger only when a specific person submits a scenario** :::BlockQuote name:'First Last' ::: :::BlockQuote Set the condition type to **Custom** and use name: with the person's name. Confirmed working pattern for conditional routing based on the submitter's identity. ::: ## ๐Ÿ“Š Reporting & Survey Queries :::BlockQuote These functions are used in dashboard charts (Advanced mode) to report on form completion, survey responses, and headcount metrics. ::: **Form completion rate for a review cycle** :::BlockQuote findFormTasksByAssessment('Review Name', 'Form Name').countPercent\{status\:done} ::: :::BlockQuote One of the most common dashboard queries for performance and engagement reporting. If the form name contains a colon (:) or has a trailing space, use the form and assessment IDs instead of names โ€” special characters in names can break the query. ::: **Count responses above a threshold for a specific question** :::BlockQuote findAnswers('fieldCode').count\{value>=4} ::: :::BlockQuote Use for single-metric charts on engagement or pulse surveys. fieldCode is the field code of the question, not the form name. ::: **Count responses to a form question filtered by org** :::BlockQuote findResponsesByAssessment('Assessment Name', 'Survey Name').filter\{with(submitPerson,jobFilter)}.count() ::: :::BlockQuote Adding .filter\{with(submitPerson,jobFilter)} applies your current org chart filter so the chart respects department, location, or other slices. ::: **Survey completion rate with org filter** :::BlockQuote findFormTasksByAssessment('Assessment Name', 'Form Name').filter\{with(person,jobFilter)}.countPercent\{status\:done} ::: **Rollup a field value across a manager's entire team** โ€” apply to manager jobs :::BlockQuote cost + underJobs.sum\{it.cost} ::: Average a field across the team: :::BlockQuote underJobs.mean\{it.fieldCode1} ::: :::BlockQuote underJobs traverses the full org tree below a person, not just direct reports. Great for manager scorecards, team cost rollups, and org chart visualizations. Use directJobs instead if you only want one level down. ::: **Multi-level manager chain** โ€” display reporting levels as separate fields Level 3 (manager's manager's manager): :::BlockQuote manager.manager.manager ::: Level 4: :::BlockQuote manager.manager.manager.manager ::: :::BlockQuote Create one Smart Calc field per level. Built-in fields cover levels 1 and 2. Many employees at higher levels will return blank โ€” that's expected. Use ?: '' to suppress null display if needed. ::: **Count number of manager changes during tenure** :::BlockQuote findHistoryValues(\{manager}).count() - 1 ::: :::BlockQuote Subtracts 1 to exclude the original hire assignment. Counts across all jobs the person has held at the org. ::: ## ๐Ÿ”— Person Field Resolution **Assign a linked person (e.g., talent partner, buddy) based on conditions** Step 1 โ€” **Smart Bucket:** Each condition outputs a personId value Step 2 โ€” **Smart Calc:** Expression is just the bucket field code: :::BlockQuote smartBucketFieldCode ::: Step 3 โ€” Set **expected return type to Person** in the Smart Calc settings :::BlockQuote Use personId โ€” not email. Email does not reliably resolve to the Person type in ChartHop. To find a personId: use the ChartHop API, or reference manager.id as a pattern example. ::: ## ๐Ÿ•ฐ๏ธ asOf vs asOfPrimary | | asOf | asOfPrimary | | ---------------------------- | --------------------------- | --------------------------------------------------- | | Includes scenario/draft data | โœ… | โŒ | | Use for | What-if / scenario planning | Baselines, historical lookups, audit-safe reporting | :::BlockQuote asOfPrimary('2025-01-01', \{base}) base salary on Jan 1 (primary data only)asOfPrimary('2024-07-01', \{fieldCode1}) rating confirmed as of July 1 ::: ## ๐Ÿงฎ Utility Functions | Function | What it does | Example | | ---------------------------- | ------------------------------ | ---------------------------------------------- | | round(x, 2) | Round to decimal places | round(fieldCode1, 2) | | formatRound(x, 1) | Round + format as string | formatRound(fieldCode1, 1) | | formatMoney(x) | Format as currency | formatMoney(base) | | formatPercent(x) | Format as percent | formatPercent(fieldCode1) | | formatDate(d, 'pattern') | Format date as string | formatDate(startDate, 'MMMM d, yyyy') | | abs(x) | Absolute value | abs(base - fieldCode1) | | max(a, b) | Larger of two values | max(base, fieldCode1) | | min(a, b) | Smaller of two values | min(base, fieldCode1) | | mean(a, b, c) | Average, excluding nulls | mean(fieldCode1, fieldCode2, fieldCode3) | | length(list) | Count items in list | length(directReports) | | diffYears(d1, d2) | Years between dates | diffYears(startDate, today()) | | diffMonths(d1, d2) | Months between dates | diffMonths(startDate, today()) | | diffDays(d1, d2) | Days between dates | diffDays(openDate, today()) | | nextAnniversary(d) | Next anniversary date | nextAnniversary(startDate) | | asOf(date, \{expr}) | Value on date (incl. scenario) | asOf('2024-07-01', \{fieldCode1}) | | asOfPrimary(date, \{expr}) | Value on date (primary only) | asOfPrimary('2025-01-01', \{base}) | | findHistoryValues(\{field}) | All historical values as list | findHistoryValues(\{fieldCode1}) | | vestValue(d1, d2) | Equity vesting value in window | vestValue(today(), nextAnniversary(startDate)) | | distance(addr1, addr2, unit) | Distance between two addresses | distance(address, location.address, 'miles') | | db.job.find\{condition} | Query jobs across the org | db.job.find\{it.startDate >= today} | ## ๐Ÿ’ก Quick Reference: Common Mistakes | โŒ Mistake | โœ… Fix | | ------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------- | | Field is blank and breaks formula | Add ?: 0 โ€” e.g., fieldCode1 ?: 0 | | mean() averaging zeros as if they're real scores | Use fieldCode1 > 0 ? fieldCode1 : null | | Using field code inside .count\{} | Use it โ€” e.g., .count\{it='Value'} | | asOf pulling in draft scenario data | Switch to asOfPrimary for baselines | | Person field returning blank | Check Smart Bucket outputs personId, not email | | Formula breaks after renaming a field | Field codes don't auto-update โ€” fix references manually | | and not working in a specific context | Switch to && | | findHistoryValues returning oldest values first | Add .reversed before .limit() | | Dashboard query breaks when form name has a colon or trailing space | Use the form/assessment ID instead of the name | | Scheduled action fires every day instead of once | Use = not \<= for date-based action filters | | Approval chain stage fires even when condition isn't met | Test the expression as a filter on the scenario Changes tab first | | findFormTasksByAssessment chart breaks when adding a filter | Use .filter\{with(person,jobFilter)} โ€” not with(submitPerson,jobFilter) for task-based queries |