Help

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

Topic Labels: Formulas
Solved
Jump to Solution
1211 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Julian_Aguilar
4 - Data Explorer
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’
)
)
)

Screen Shot 2020-08-24 at 11.51.51 AM

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
JonathanBowen
13 - Mars
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:

Screenshot 2020-08-24 at 17.37.58

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

See Solution in Thread

2 Replies 2
JonathanBowen
13 - Mars
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:

Screenshot 2020-08-24 at 17.37.58

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

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