Using Formulas to create a Task status based on status of Sub Tasks

[leannawisener]

I have 2 linked tables, Tasks and Sub Tasks. I want the Sub Tasks' status to define the status of the Task. I currently have a rollup that makes an array of all the statuses in a given Task's Sub Task. Then I have a formula that reads these arrays and creates a status. I

Trying to create a formula that fulfills the following requirements:

1. If all subtasks are "Done", the task status should be "Done".
2. If any subtask is "In Progress", the task status should be "In Progress".
3. If any subtask is "Done" and at least one subtask is "To Do", the task status should be "In Progress".
4. Otherwise, if none of the above conditions are met, the task status should be "To Do".

I am not much for coding so I explained this to ChatGPT and it gave me the following code.

IF(
  BLANK({All Sub Tasks- Status}),
  "To Do",
  IF(
    FIND("In Progress", {All Sub Tasks- Status}),
    "In Progress",
    IF(
      AND(
        FIND("Done", {All Sub Tasks- Status}),
        FIND("To Do", {All Sub Tasks- Status})
      ),
      "In Progress",
      IF(
        COUNTA(ARRAYUNIQUE({All Sub Tasks- Status})) = 1,
        "Done",
        "To Do"
      )
    )
  )
)

This mostly works, but there are a few cases where it isn't working and I can't figure out why. For example, I have one Task where the Sub Tasks have statuses "Done, To Do" that returns the Task status as "To Do", but another Task where the Sub Tasks statuses are reversed ("To Do, Done") and it correctly returns "In Progress"

Any help as to why this is happening? ChatGPT hasn't been able to help me past there. 

[Dan_Montoya]

Hi Leanna,

I solve this by using a combination of count fields and formula fields.  Then I like to use an automation to copy the output of the formula into a single select field.

IF( {% done} = 1, "Done",
IF({In progress} >0, "In progress",
"To do")
)

[TheTimeSavingCo · Accepted solution]

Try this: 

IF(
  FIND(
    "In Progress",
    {Select Rollup (from New table)}
  ),
  "In Progress", 
  IF(
    FIND(
      "To Do",
      {Select Rollup (from New table)}
    ),
    "To Do", 
    IF(
      FIND(
        "Done",
        {Select Rollup (from New table)}
      ),
      "Done"
    )
  )
)

You'll need to update the formula to use your field headers, and also need to make sure that the rollup is using 'ARRAYJOIN'

Link to base

[Alexey_Gusev]

Hi,
No need to use word BLANK in Airtable formulas
Rename your rollup field to 'rollup', insert formula (ensure it works), rename field back

IF(FIND('I',rollup),'In Progress', 
  IF(FIND('T',rollup),'To do',
    IF(rollup,'Done')))