Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

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

Topic Labels: Formulas
Solved
Jump to Solution
1453 3
cancel
Showing results for 
Search instead for 
Did you mean: 
leannawisener
4 - Data Explorer
4 - Data Explorer

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. 

 

1 Solution

Accepted Solutions

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

Screenshot 2024-02-27 at 10.16.06 AM.png

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

See Solution in Thread

3 Replies 3
Dan_Montoya
Community Manager
Community Manager

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.

 

Screenshot 2024-02-26 at 3.26.19 PM.pngScreenshot 2024-02-26 at 3.26.34 PM.png

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

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

Screenshot 2024-02-27 at 10.16.06 AM.png

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

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