Can I use the switch formula to choose between the values of two fields based on whether a checkbox field is checked?
I have 3 relevant fields in this table:
- {Contract Amount (distinct)} - a simple currency field showing the amount for this distinct record
- {Contract Amount (aggregate)} - a formula field that sums the first field + the remaining balance of this record's predecessor contract.
- {carry-forward?} - a checkbox field where Team 1 tells Teams 2 & 3 which of the first two fields they should use for their records.
When Team 1 looks up a contract in their interface, they only ever want to see the first field as the "true" contract total, but when Teams 2 & 3 look up the same contract in their own interfaces, they want to see either the distinct or the aggregate depending on whether or not the box is checked.
I tried the following switch formula in a 4th field and Airtable accepted it, but it's just returning empty values. Am I doing something wrong with my formula, or is it something to do with asking it to return values from other (sometimes calculated) fields?
SWITCH(
{Carry-forward $?},
1, {Contract Amount (Aggregate)},
0, {Contract amount (Individual)})