- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎May 17, 2023 11:10 AM
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)})

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎May 23, 2023 06:19 AM
Hey there! I saw that this was since resolved via Support so I'll go ahead and leave our team's advice below in case it should help others with the same question. 😊
In the case of checkboxes, we recommend using the IF function instead of SWITCH. The IF function evaluates a logical argument and returns dynamic values based on a true or false result. Below is a formula that will accomplish what you are looking to do:
IF({Carry-forward $?},{Contract Amount (aggregate)}, {Contract Amount (distinct)})
