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:
- If all subtasks are "Done", the task status should be "Done".
- If any subtask is "In Progress", the task status should be "In Progress".
- If any subtask is "Done" and at least one subtask is "To Do", the task status should be "In Progress".
- 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.
