Formula to update text based on checkbox

[Holli_Younger]

I have multiple checkboxes in my base, showing only 1 checkbox per view. Can someone please help with putting together the correct formula to update my “Status” field based on the checkboxes.

The closest I can find is support when 1 box is checked at a time, however, once this step is complete the checkbox for that view is hidden and the new view reveals step 2 - showing only step 2 checkbox. So as you carry on through the task management process multiple checkboxes will get checked and I don’t want the “status” field to show all fields, just the one on the current view.

Typing that to explain it is harder than I thought. So in a nutshell- think of the checkboxes as status dropdowns - and manually changing the status drop down during each phase of your project. That’s what I need to accomplish but with checkboxes.

Thank you

[Justin_Barrett]

The concept you describe is pretty clear. Could you give us a more specific example of your setup; i.e. names of your checkboxes, and the output you’d like to see when each is checked? That will make it a lot easier to assemble a sample formula.

[Holli_Younger]

Thank you Justin.

Checkbox Name	Status	Notes*
#1 Chkbox	Incoming Request	only #1 Chkbox is marked
#2 Chkbox	Pending Scope	#1 Chkbox & #2 Chkbox is marked
#3 Chkbox	Quote Verified	#1, #2 & #3 Chkbox is marked
#4 Chkbox	In Processing	#1, #2, #3, & #4 Chkbox is marked
#5 Chkbox	Pending Approval	#1, #2, #3, #4 &#5 Chkbox is marked
Etc…	Etc…	Etc.

[Justin_Barrett · Accepted solution]

Thanks for the breakdown, Holli. Here’s what I suggest.

Make a formula field that does nothing but check the status of those checkboxes, outputting an appropriate number as a string for each one, with all the results concatenated into a single long string. That may sound confusing, but it’s actually pretty straightforward:

IF({#1 Chkbox}, "1") &
IF({#2 Chkbox}, "2") &
IF({#3 Chkbox}, "3") &
IF({#4 Chkbox}, "4")

If the first checkbox is checked, the output will be “1”. If #1 and #2 are both checked, it will be “12”, and so on. I considered slightly simpler formulas, but they left room for possible misreading of the results. This is as compact as I could get this part of the system while also trying to prevent future errors. I named this field {Chkbox Output}. Expand as needed to add more checkboxes.

The status would then be created in another formula field based on the output of {Chkbox Output}, using the SWITCH() function:

SWITCH(
    {Chkbox Output},
    "1", "Incoming Request",
    "12", "Pending Scope",
    "123", "Quote Verified",
    "1234", "In Processing",
    "12345", "Pending Approval"
)

As above, expand as needed to add more status levels based on how many checkboxes you’re using. One option I considered was only testing the last digit in the number string, but that leaves room for error (i.e. a checkbox was missed somewhere in the middle). By testing for the full sequence, it ensures that every checkbox in the chain has been checked.

[Holli_Younger]

@Justin_Barrett Wow! This is amazing! I’ve played around with the switch option but, like many others, tried cramming too many formulas into one field to eliminate so many extra fields. But this works in amazing ways and I can do so much with it throughout my base!!

Little extra credit question for you: If I needed to backtrack in the process, (change mid-project) I’m thinking another Checkbox with the ratio of all checkboxes to cover no matter which previous options (step) we were in. Am I on the right track?

Again, thank you so much for not only providing the details but explaining it as well. Knowing “how-to” in layman’s terms helps me understand it better. I read over the formula options so many times, but they are so generic and confuse me more.

[Justin_Barrett]

Holli_Younger:

If I needed to backtrack in the process, (change mid-project) I’m thinking another Checkbox with the ratio of all checkboxes to cover no matter which previous options (step) we were in. Am I on the right track?

I can’t say whether or not you’re on the right track because I don’t understand what you mean by “another checkbox with the ratio of all checkboxes.” Could you go into a bit more detail about this addition to the system and how it would operate?

[Holli_Younger]

Well, when I wrote the extra question it made perfect sense, then I re-read it with your comment and I see my own confusion. While thinking out my question I think I figured it out, instead of needing the ratio - I can just add something like this:

My attempt at an example:
The process is currently on step 5 (#5 chkbox) and the customer requires a change/update
Since all fields are not available on every view and I need to update details on view 3
(#3 chkbox would be on view 3 - views show our workflow steps)

IF({#1 Chkbox}, “1”) &
IF({#2 Chkbox}, “2”) &
IF({#3 Chkbox}, “3”) &
IF({#4 Chkbox}, “4”)&
IF({Change Chkbox}, “00”)

SWITCH(
{Chkbox Output},
“1”, “Incoming Request”,
“12”, “Pending Scope”,
“123”, “Quote Verified”,
“1234”, “In Processing”,
“123456”, “Pending Approval”,
"00", "Updating Record"
)

[Justin_Barrett]

First off, I discovered a hiccup in my original SWITCH() formula from earlier, I jumped from “1234” to “123456” (6 shouldn’t be there yet). I’ll fix that above.

Looking at your modified formula, checking {Change Chkbox} in the middle of step 5 wouldn’t give you just “00” on its own. It would add “00” to the end of the current string “12345” because you have the other five checkboxes checked, which would give you “1234500”. If you make a change after step 3, it would be “12300”. Does that make sense? With the “00” added on the end, the SWITCH() function wouldn’t output anything because it can’t make an exact match.

It sounds like you want {Change Chkbox} to override any prior status until you make the needed change and uncheck it, at which point it would revert to the original status. If that’s correct, here’s how to tweak the second formula to create that result:

IF(
    {Change Chkbox},
    "Updating Record",  
    SWITCH(
        {Chkbox Output},
        "1", "Incoming Request",
        "12", "Pending Scope",
        "123", "Quote Verified",
        "1234", "In Processing",
        "12345", "Pending Approval"
    )
)

This effectively says: if {Change Chkbox} is checked, display “Updating Record”; otherwise display the proper status based on what the other checkboxes are doing.

[Holli_Younger]

100% what I was needing and trying to explain. Excellent work Justin! I really appreciate it!