Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Help with Switch formula based on Checkbox field

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