We have various sales/participation goals that change every year. My idea is to essentially store a unique formula for every goal, for every year, in a text field in a year table. Then in the main table, lookup that text field and somehow create a formula field based on that to do the actual calculation. Is that possible?
| Year | Goal Name | Criteria (single line text field) |
|---|---|---|
| 2024 | CP Sales Goal | IF({CP Sales Rollup} >= 50000, 1, 0) |
| 2025 | CP Sales Goal | IF({CP Sales Rollup} >= 60000, 1, 0) |
| Client | Year (link) | Criteria (lookup text field) | CP Sales Rollup | Points (formula) |
|---|---|---|---|---|
| ABC | 2024 | IF({CP Sales Rollup} >= 50000, 1, 0) | $35,500 | {Criteria} should return “0” |
| ABC | 2025 | IF({CP Sales Rollup} >= 60000, 1, 0) | $75,750 | {Criteria} should return “1” |
In the Points formula field, how do I write the formula? I’ve tried just putting in a reference to the Criteria lookup field: {Criteria}, but that doesn’t work. I assume there has to be some conversion from text into something Airtable can use in the calculation.
