Skip to main content

Nested IF plus.... What else do I need? 😳

  • October 5, 2023
  • 3 replies
  • 17 views
Spruce
Forum|alt.badge.img+7

Hi All! I am still new to Airtable and writing formulas and (hopefully) this is an easy question.

I am trying to write a formula that will display text that matches the "checked" state of 3 different checkboxes, and also will display "New" if none of those checkboxes are checked.

This is what I have written so far, and it is working to display the state of the first checkbox, but because that is true, it's not moving on to the next thing I want it to test for. Is there an additional logic I could add to the formula that would accomplish this? Or a totally different way of going about it?

My current formula in the Status column: 

IF({Service Started}, "Service Started", IF({Ready for Pickup}, "Ready for Pickup", "New"))
 
Screenshot for context: 

Thank you so much!
Devon

1 Attachments
Devon

3 replies

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • October 5, 2023

Hi @Spruce ,

For example, if there is a possibility of more check fields, this might make it easier to manage

SWITCH( IF({Service Started},1,0)&IF({Ready for Pickup},1,0)&IF({Completed},1,0), "100", "Service Started", "110", "Ready for Pickup", "111", "Completed", "ERROR!" )

 

Spruce
Forum|alt.badge.img+7
  • Spruce
  • Author
  • Known Participant
  • October 6, 2023

Hi @Spruce ,

For example, if there is a possibility of more check fields, this might make it easier to manage

SWITCH( IF({Service Started},1,0)&IF({Ready for Pickup},1,0)&IF({Completed},1,0), "100", "Service Started", "110", "Ready for Pickup", "111", "Completed", "ERROR!" )

 


Ooh this is very neat, @Sho ! The behaviour is also different, and I haven't decided if in a useful way or not... For instance, in the IF version (which I got working by the way - see below) you can check "Complete" first and the Status will go straight to Complete.

In the SWITCH version all the checkboxes need to be ticked before it Results in Complete. Also, you don't get a result if you tick Ready for Pickup before Service Started. 

So help me understand this formula... What do the "100" "110" and "110" do in this formula?

Thanks for a great idea! I'll have to do some testing to determine which is better in my use case.

P.S. Working nested IF formula with order of the IF statements reversed: 

IF(Completed, "Completed", IF({Ready for Pickup}, "Ready for Pickup", IF({Service Started}, "Service Started", "New")))
Devon
S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • October 6, 2023

I suggested the SWITCH idea because it came to me, but usually it would be better to use IF.

Now I think this idea is not good!

This formula just replaces each checkbox field with 1 = Checked and 0 = No check.
So to show completed when completed is checked, you need to enter all the patterns...

"001","Completed", "101","Completed", "111","Completed",

 Congrats on your self-resolution.

Terms & ConditionsAccessibility statement