Help with Switch formula based on Checkbox field

scrambledmeggs · ‎May 17, 2023

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

Juliana · ‎May 23, 2023

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