Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 11, 2023 11:40 AM
Hi there,
I am trying to set up an OKR table in Airtable. I have two tables in my workspace. One for Key Results and one for Initiatives. Under initiatives I have a column with tasks just check off as they occur. Please see below. How do I create a column with a formula that will calculate the % completed based on the check marks listed in the task column. I have tried to create the formula with creating additional columns (total task count and total task count completed to calculate off of but that didn't work). I used the "countall tasks" in the total task column and tried to create a formula in the total task count but it isn't working.
Any guidance would be greatly appreciated.
Solved! Go to Solution.
Aug 11, 2023 08:23 PM - edited Aug 11, 2023 08:24 PM
+1 for what Joseph suggested
If operational requirements don't allow for it and you really need to use a long text field, try creating a formula field with this:
(
LEN(
{Tasks}
) -
LEN(
SUBSTITUTE(
{Tasks},
"[x]",
"[]"
)
)
)/
(
LEN(
{Tasks}
) -
LEN(
SUBSTITUTE(
{Tasks},
"[ ]",
"[]"
)
)
+
LEN(
{Tasks}
) -
LEN(
SUBSTITUTE(
{Tasks},
"[x]",
"[]"
)
)
)
Aug 11, 2023 12:41 PM
Have you considered making each task an individual record instead of including tasks in a long text field?
Especially with the new List view, it should work quite well, and then you can have another table link to the tasks (records), roll them up, and run calculations.
I've built many task managers, and can't say I've ever used long text fields to track tasks. Just checkbox fields and individual records/nesting records.
Aug 11, 2023 08:23 PM - edited Aug 11, 2023 08:24 PM
+1 for what Joseph suggested
If operational requirements don't allow for it and you really need to use a long text field, try creating a formula field with this:
(
LEN(
{Tasks}
) -
LEN(
SUBSTITUTE(
{Tasks},
"[x]",
"[]"
)
)
)/
(
LEN(
{Tasks}
) -
LEN(
SUBSTITUTE(
{Tasks},
"[ ]",
"[]"
)
)
+
LEN(
{Tasks}
) -
LEN(
SUBSTITUTE(
{Tasks},
"[x]",
"[]"
)
)
)
Aug 14, 2023 09:46 AM
OMG, that worked. Thank you so much! Thank you also Joseph. I will keep that in mind and may even try this for this project when I have more time. However, this worked for me to present to my team as an immediate resolution. Thanks everyone. This community is very helpful:)