# Artifact 3: Formula Compatibility Matrix

Generated from 19 migrated flows in the `nexuscrm` test database, 2026-04-17.

## Summary

- **19 flows** scanned
- **5 flows** contain formula elements (14 flows have zero formulas)
- **9 unique expressions** (16 total formula entries, with duplicates of `TODAY()`)
- **6/9 PASS** the formula engine as-is
- **3/9 FAIL** due to `{!$Record...}` merge-field wrapper syntax not being stripped by `normalizeFormulaExpression`

## Formula Matrix

| # | Flow | Expression (raw from metadata) | DataType | Engine Result | Status | Notes |
|---|------|-------------------------------|----------|---------------|--------|-------|
| 1 | Order_Expiration_Notice_90 | `TODAY()` | Date | `2026-04-17` | PASS | 3 instances in flow (formula_3, _4, _5) |
| 2 | Order_Expiration_Notice_90 | `TODAY()+30` | Date | `2026-05-17` | PASS | Rewritten to `DATE_ADD(TODAY(), 30)` by normalizer |
| 3 | Order_Expiration_Notice_90 | `TODAY()+60` | Date | `2026-06-16` | PASS | Rewritten to `DATE_ADD(TODAY(), 60)` |
| 4 | Order_Expiration_Notice_90 | `TODAY()+90` | Date | `2026-07-16` | PASS | Rewritten to `DATE_ADD(TODAY(), 90)` |
| 5 | Contract_PingCAP_Contracting_Entity | `today()` | Date | `2026-04-17` | PASS | Lowercase; normalizer uppercases to `TODAY()` |
| 6 | Collections_Invoice_Status_marketplace | `"Not Applicable"` | String | `Not Applicable` | PASS | String literal passthrough |
| 7 | Order_PingCAP_RevRec_Entity | `IF({!$Record.PingCAP_Contracting_Entity_EMEA_only__c}="",TEXT({!$Record.Account.PingCAP_Contracting_Entity__c}),{!$Record.PingCAP_Contracting_Entity_EMEA_only__c})` | String | ERROR | FAIL | `{!...}` merge-field braces not stripped; `$` rewritten to `__` but `{!` remains |
| 8 | Contract_PingCAP_Contracting_Entity | `if(text({!$Record.PingCAP_Contracting_Entity__c}) ="PingCAP Kabushiki-Kaisha", "Japan",if(text({!$Record.PingCAP_Contracting_Entity__c}) ="PingCAP (US), Inc.", "the United States", if(text({!$Record.PingCAP_Contracting_Entity__c}) ="PingCAP Kabushiki-Kaisha", "Japan",if(text({!$Record.PingCAP_Contracting_Entity__c}) ="北京平凯星辰科技发展有限公司", "China","Singapore"))))` | String | ERROR | FAIL | Same `{!...}` issue; nested IF+TEXT with CJK string literal |
| 9 | Collections_Region_PingCAP_Contracting_Entity_Marketplace | `IF({!$Record.Order__r.PingCAP_Contracting_Entity_EMEA_only__c}="",TEXT({!$Record.Account__r.PingCAP_Contracting_Entity__c}),{!$Record.Order__r.PingCAP_Contracting_Entity_EMEA_only__c})` | String | ERROR | FAIL | Same `{!...}` issue + `__r` traversal |

## Flows with No Formulas (10 of 19)

| Flow | Trigger Object | Notes |
|------|---------------|-------|
| Order_Account_Name | Order | Assignment-only |
| Order_Partner_Name | Order | Assignment-only |
| Order_Start_Date_End_Date | Order_Product__c | Assignment-only |
| Account_PingCAP_Contracting_Entity | Account | Assignment-only |
| Collections | Invoice_Collection__c | Decision + assignment |
| Collections_Cash_Collected | Invoice_Collection__c | Assignment-only |
| Invoice_Notice | Invoice_Collection__c | Assignment-only |
| Collections_Account_ID | Invoice_Collection__c | Assignment-only |
| Collection_Partner_Name | Invoice_Collection__c | Assignment-only |
| Order_Expiration_Alert_7 thru _90 | (Scheduled) | 5 scheduled-trigger flows, no formulas |

## Root Cause of Failures

All 3 failures share the same root cause: `normalizeFormulaExpression` in `backend/pkg/formula/engine.go` converts `$Record` to `__Record` (line 316) but does **not** strip the Salesforce Visual Workflow merge-field wrapper `{!...}`.

The normalizer rewrites:
- `$Record.Foo` -> `__Record.Foo` (correct)
- `{!$Record.Foo}` -> `{!__Record.Foo}` (broken -- `{!` and `}` remain)

### Suggested Fix

Add a pre-processing step in `normalizeFormulaExpression` to strip `{!` and `}` wrappers:

```go
// Strip Salesforce merge-field wrappers: {!expr} -> expr
mergeFieldPattern := regexp.MustCompile(`\{!([^}]+)\}`)
expr = mergeFieldPattern.ReplaceAllString(expr, "$1")
```

This should be inserted **before** the `$` -> `__` rewrite (line 316) to convert all 3 FAIL cases to PASS.

## Functions Used Across All Formulas

| Function | Count | Engine Support |
|----------|-------|---------------|
| `TODAY()` | 9 | Supported |
| `IF()` | 3 | Supported |
| `TEXT()` | 3 | Supported |
| `DATE_ADD()` (via `TODAY()+N` rewrite) | 3 | Supported |
| String literals | 1 | Supported |