Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

# AutoStatus Column based on Team Assignments

Topic Labels: Formulas
Solved
812 9
cancel
Showing results for
Did you mean:
6 - Interface Innovator

I’m guessing this scenario has already been covered, but I couldn’t find it anywhere. Feel free to point me to the relevant article if so.
I’ve got 5 status fields, one for each of 5 teams.

When a new project is added, the person adding needs to identify whether or not each team has any tasks to do in association with the project. If so, they pick “Yes”; if not, “No”.
Then each team is responsible to update the status in the field from there.
I want to create an AutoStatus column that put out the following:

• “In Progress”: if any of the team statuses is “In Progress”
• “On Hold”: if any of the team statuses is “On Hold”
• “Complete”: if all 5 statuses are either “Complete” or “No” (ignore "NA "- I’m taking that one out of the list)
• “Not Started”: if all 5 statuses are either “Yes” or “Reviewing”

This level of complexity in formulas is beyond me at this point, but I’m confident that someone here will know how to do what I’m looking for!

This will be an awesome step forward from what we’re doing now, using a manual status column, which allows someone to mark the whole project as “Complete” without getting confirmation from each of the teams involved.

Joel

1 Solution

Accepted Solutions
18 - Pluto

There might be some typos in field names and values, but this should give you a start.

``````IF(
OR(
),
"In Progress",
IF(
OR(
),
"On Hold",
IF(
AND(
OR(
),
OR(
),
OR(
),
OR(
),
OR(
)
),
"Complete",
IF(
AND(
OR(
),
OR(
),
OR(
),
OR(
),
OR(
)
),
"Not Started",
"Unknown"
))))
``````
9 Replies 9
6 - Interface Innovator

I should mention, in case it’s any help, that I also have %complete columns associated with each status, and a total %Complete column, that were created with lots of help from @Kamille_Parks and @kuovonne in this post.

18 - Pluto

What should the combined status be if ins team is “on hold” but another team is “in progress”?

6 - Interface Innovator

Ideally, it would be “In Progress” if any of the teams are still “In Progress”, but “On Hold” if all other teams are “Complete”, “On Hold”, or “No”.

18 - Pluto

There might be some typos in field names and values, but this should give you a start.

``````IF(
OR(
),
"In Progress",
IF(
OR(
),
"On Hold",
IF(
AND(
OR(
),
OR(
),
OR(
),
OR(
),
OR(
)
),
"Complete",
IF(
AND(
OR(
),
OR(
),
OR(
),
OR(
),
OR(
)
),
"Not Started",
"Unknown"
))))
``````
6 - Interface Innovator

You. Are. The Best.

18 - Pluto

Thanks. I started writing the formula on my phone then decided that I really need a keyboard to complete it. You can see that the logic is a direct translation of what you wrote. The formatting style helps get the commas and parenthesis in the right places (although I still sometimes have typos with them).

6 - Interface Innovator

Ok, I’m realizing there are a couple of gaps in my logic. Maybe you can help.
If any of the team status fields is blank, the output should be “ :question: :woman_shrugging: :question: ” (what I used in place of “Unknown”) - so it forces the person adding the project to choose “Yes” or “No” for each team.
I’m also getting “ :question: :woman_shrugging: :question: ” for a lot of other cases where I’d want something different:

If some answers are “No”, and others are either “Yes” or “Reviewing”, then the output should be “Not Started”.
If some are “Complete”, and others are still “Yes”, “Reviewing”, or “In Progress”, then the output should be “In Progress”.

Is there a way to make use of the %Complete calculation columns you helped me build to make this work?
Here’s the current version formula I use for each of the teams:
SWITCH({Rptg Tasks?},“No”, BLANK(), “Complete”, 1, “Yes”, 0.001, “Reviewing”, 0.05, “In Progress”, 0.50, “On Hold”, 0.50, 0)

And here’s the formula for calculating the totals (%Complete):
SUM({Impl. % value},{Config. % value},{Bill. % value},{Tr. % value},{Rep. % value})/COUNTA({Impl. % value},{Config. % value},{Bill. % value},{Tr. % value},{Rep. % value})

I tried the following, but Airtable didn’t like it (probably because I missed something or didn’t format it correctly):
IF({% Complete}>0,“ :stop_sign: Not Started”,(
IF({% Complete}>0.005,“ :mag: Reviewing”),(
IF({% Complete}>0.25,“ :building_construction: In Progress”),(
IF({% Complete}=1,“ :heavy_check_mark: Complete​:+1:”),
“ :question: :woman_shrugging: :question: ”)

18 - Pluto

Probably, but I do not have time to tackle this now. As you can see the more complex the logic gets, the longer the formula is, and it is difficult to ensure that the logic is complete.

6 - Interface Innovator

Sorry, I’m realizing that my math is totally wonky in that last formula (and formatting). I need to play around with this a bit more. I don’t think the number ranges will work, but maybe someone else has an idea.