Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 26, 2024 02:12 PM
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:
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.
Solved! Go to Solution.
Feb 26, 2024 06:18 PM
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
Feb 26, 2024 03:27 PM
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")
)
Feb 26, 2024 06:18 PM
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
Feb 27, 2024 04:26 AM
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')))