Help

Help with Switch formula based on Checkbox field

610 1
cancel
Showing results for 
Search instead for 
Did you mean: 
scrambledmeggs
5 - Automation Enthusiast
5 - Automation Enthusiast

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:

  1. {Contract Amount (distinct)} - a simple currency field showing the amount for this distinct record
  2. {Contract Amount (aggregate)} - a formula field that sums the first field + the remaining balance of this record's predecessor contract. 
  3. {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)})
 
1 Reply 1
Juliana
Airtable Employee
Airtable Employee

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)})