# I need help with the Formula Field (If And, If Not)

Topic Labels: Formulas
Solved
901 2
cancel
Showing results for
Did you mean:
4 - Data Explorer

Hi there!

I’m trying to build out a formula that allows me to use checkboxes to update an application status column. As of now, this is my current formula:

IF(
AND({Reviewed}, {Accepted}),
‘Accepted’,
IF(
{Accepted},
‘Error!’,
IF(
NOT({Reviewed}),
‘Pending Review’,
‘Rejected’
)
)
)

My hope is to have a three-column system where if you check {Reviewed} it displays the formula column as ‘Reviewed’, {Reviewed} and {Accepted} as ‘Accepted’, {Reviewed} and {Rejected} as ‘Rejected’, and {Reviewed} and {Accepted} and {Rejected} as ‘Error!’. Please let me know how to go about this. Major thanks!

1 Solution

Accepted Solutions
13 - Mars

Hi @Julian_Aguilar - your screenshot is cut off I think. You’re saying you want to show the status based on 3 checkboxes - Reviewed, Accepted and Rejected, yes?

Can I suggest an alternative approach? Accepted and Rejected are part of the same thing, so ideally you’d cover this in a single field. The problem with having these as two separate fields is there’s many opportunities for them to conflict, leading to a lot of error states - not a big deal, but ideally they would be engineered out. In addition, this single field has the concept of “neither accepted or rejected as we haven’t got to that stage of the process”.

So, instead of 3 binary state fields, you might be better off with one binary state field (Reviewed/Not Reviewed) and a 3-state field (Accepted/Rejected/Neither). You’d end up with something like this:

where the status formula is:

``````IF(
AND(NOT({Reviewed}), NOT({Accepted})),
'Pending review',
IF(
AND({Reviewed}, NOT({Accepted})),
'Reviewed',
IF(
AND(NOT({Reviewed}), {Accepted} = 'accepted'),
'Error',
IF(
AND({Reviewed}, {Accepted} = 'accepted'),
'Accepted',
IF(
AND(NOT({Reviewed}), {Accepted} = 'rejected'),
'Error',
'Rejected'
)
)
)
)
)``````
2 Replies 2
13 - Mars

Hi @Julian_Aguilar - your screenshot is cut off I think. You’re saying you want to show the status based on 3 checkboxes - Reviewed, Accepted and Rejected, yes?

Can I suggest an alternative approach? Accepted and Rejected are part of the same thing, so ideally you’d cover this in a single field. The problem with having these as two separate fields is there’s many opportunities for them to conflict, leading to a lot of error states - not a big deal, but ideally they would be engineered out. In addition, this single field has the concept of “neither accepted or rejected as we haven’t got to that stage of the process”.

So, instead of 3 binary state fields, you might be better off with one binary state field (Reviewed/Not Reviewed) and a 3-state field (Accepted/Rejected/Neither). You’d end up with something like this:

where the status formula is:

``````IF(
AND(NOT({Reviewed}), NOT({Accepted})),
'Pending review',
IF(
AND({Reviewed}, NOT({Accepted})),
'Reviewed',
IF(
AND(NOT({Reviewed}), {Accepted} = 'accepted'),
'Error',
IF(
AND({Reviewed}, {Accepted} = 'accepted'),
'Accepted',
IF(
AND(NOT({Reviewed}), {Accepted} = 'rejected'),
'Error',
'Rejected'
)
)
)
)
)``````
4 - Data Explorer

Thanks! Just an FYI, my SS was just for what I had already but thanks for this tip.