I have encountered what appears to be a significant logical flaw in the calculation engine for custom formula fields, specifically concerning the handling of `NULL` (or blank) values in conditional arithmetic. After a detailed support ticket and escalation, the engineering team's final response was that the behavior is "by design," which I find to be a deeply unsatisfactory resolution that contradicts both mathematical convention and the principle of least surprise in system design.
The issue manifests in a formula intended to calculate a weighted score, where some components may be optional. Consider the following simplified example:
```
IF(AND({Component_A}, {Component_B}),
({Component_A} * 0.7) + ({Component_B} * 0.3),
BLANK()
)
```
The intuitive expectation is that if either `Component_A` or `Component_B` is blank, the `AND` condition fails, and the field returns blank. However, the actual observed behavior is that the formula proceeds to evaluate the arithmetic branch *even when the `AND` condition is false*, leading to erroneous calculations where a blank value is treated as zero in the subsequent multiplication and addition.
This results in nonsensical outputs such as:
* `Component_A` = 100, `Component_B` = `BLANK()` → Expected: `BLANK()`, Actual: **70**
* `Component_A` = `BLANK()`, `Component_B` = 100 → Expected: `BLANK()`, Actual: **30**
The support team's assertion that "all blank values are coerced to zero within numerical operations, regardless of enclosing conditional guards" is the cited design rationale. This creates a critical pitfall for any business logic that depends on conditional presence of data.
From a systems architecture perspective, this is a clear violation of expected evaluation semantics. The `IF` function's predicate should act as a guard clause, and its dependent expressions should only be evaluated *after* the predicate is resolved to `TRUE`. The current implementation appears to be performing a naive pre-processing substitution (blank → 0) across the entire formula parse tree before evaluating the conditional logic. This is reminiscent of early, poorly-optimized query planners that would evaluate scalar functions on all rows before applying `WHERE` clause filters.
I am seeking input from the community on two fronts:
1. **Workarounds:** Has anyone devised a robust pattern to enforce true conditional evaluation? My current, cumbersome solution involves nested redundancy:
```
IF(AND(NOT(ISBLANK({Component_A})), NOT(ISBLANK({Component_B}))),
IF(NOT(ISBLANK({Component_A})),
{Component_A} * 0.7,
0
) +
IF(NOT(ISBLANK({Component_B})),
{Component_B} * 0.3,
0
),
BLANK()
)
```
This is verbose, difficult to maintain, and computationally wasteful.
2. **Platform Implications:** If this evaluation model is indeed a core design tenet of Granola's formula engine, it raises concerns about the reliability of more complex logic involving `SWITCH()`, `CASE()`, or recursive functions. Are there other undocumented evaluation quirks the community has documented?
This behavior undermines data integrity for calculated fields. A platform positioning itself as enterprise-grade should adhere to strict, predictable evaluation models. I am compiling a formal benchmark to compare this behavior against other leading SaaS platforms; any anecdotal or tested comparisons would be valuable.
Oh wow, that's a classic one. The "by design" response for a broken-feeling conditional is so frustrating 😕
What you're describing is a lazy vs. eager evaluation problem. The engine seems to be calculating the entire formula first, then applying the IF, which is backwards. I've hit similar walls with scoring formulas.
Have you tried nesting the logic to force the check? Something like:
IF(NOT(ISBLANK({Component_A})), {Component_A} * 0.7, 0) + IF(NOT(ISBLANK({Component_B})), {Component_B} * 0.3, 0)
It's a messy workaround, but it might get you the right math while you keep pushing them on the core bug.
Docs save time
Exactly. The AND condition isn't short-circuiting. It evaluates the whole expression first, which is why your blank becomes a zero in the math. The workaround is to avoid the conditional branch for the calculation entirely.
Instead of wrapping the whole formula in an IF, build the result to handle blanks directly. This forces the engine to evaluate each component independently.
Try:
```
(IF({Component_A}, {Component_A}, 0) * 0.7) + (IF({Component_B}, {Component_B}, 0) * 0.3)
```
If both are blank, it'll return 0. If you need a true blank, wrap that entire thing in another IF to check for two blanks. Annoying, but it works.
YAML all the things.