Using Rollup to check on completed tasks?

[Hashim_Warren]

I have a table of TASKS that links to a table of PROJECTS. There’s a one to many relationship between PROJECTS and TASKS.

Each TASK has a status - todo, doing, and done.

I want to create a field that will say “complete” when every task is has a status of “done”.

So far I tried making a rollup on the PROJECTS table with OR(), but I’m getting stuck.

Is what I’m asking for possible? How would I do it? Thanks a million.

[Chester_McLaugh]

One way would be to have two status columns in the tasks table. One would be the textual status such as done or pending or in progress. The other would be a formula field that would calculate a numeric result such as a zero or one Based on whether or not the status was marked as complete. Then in your projects table your roll up could target the numeric status field and simply use SUM to see if the total was zero or not. If it was not zero, that would mean there was at least one task that was not set to complete. If it was zero then you would know all the tasks were marked as complete. It seems like there should be a better way to do this, but nothing comes to mind offhand.

[Matt_Bush]

Chester’s suggestion is right - additionally, you can wrap the Rollup formula in an IF function to output it as “Complete” or blank. The specific fields you would want to set up are:

1. A Formula field on the TASKS table called “Is Done”, with the formula Status = "Done". This should output 1 for tasks that are done, 0 otherwise.
2. A Rollup field on the PROJECTS table targeting the TASKS table’s “Is Done” field with the formula IF(AND(values), "Complete","").

It is possible to do this in 1 field, but it’s hacky and I wouldn’t recommend it:

1. A rollup field on the PROJECTS table targeting the TASKS table’s Status field with the formula IF(OR(LEN(CONCATENATE(values)) = 0, AND(FIND("T", CONCATENATE(values)) = 0, FIND("i", CONCATENATE(values)) = 0)), "Complete", "")

This is quite fragile and doesn’t generalize well since it relies on the fact that Todo and Doing have a “T” and “i” respectively while Done does not, and it’s far less efficient.

[Hashim_Warren]

That worked perfectly - thank you @Chester_McLaughlin and @Matt_Bush

So, what does “or” do in the rollup? I searched everywhere and couldn’t find a good example.

Again, thanks a lot

[Matt_Bush]

The purpose of OR in my second example is to express: "Either there are no linked tasks (LEN(…) = 0) OR these letters cannot be found in the status fields of linked tasks.

If it wasn’t for the first part, the rollup wouldn’t work properly for projects with no linked tasks.

[Chester_McLaugh]

Never even considered the possibility of your last approach! Awesome! Mind = Blown!

[Hashim_Warren]

Still testing this out…

[Hashim_Warren1]

I found a clean way to make this work:

IF(ARRAYUNIQUE(values) = “Done”, “Complete”,"")

If all of the ROLLUP values are “Done” then it will spit out “Complete”. If it’s not all done, then it will be blank.

Thanks again @Matt_Bush. This solution came about after tinkering with the first one you gave me.

[Hashim_Warren]

I found a clean way to make this work:

IF(ARRAYUNIQUE(values) = “Done”, “Complete”,"")

If all of the ROLLUP values are “Done” then it will spit out “Complete”. If it’s not all done, then it will be blank.

Thanks again @Matt_Bush. This solution came about after tinkering with the first one you gave me.

[Robert_Glover]

I’m hoping you guys can break this down a bit for me and help me with an issue I have along these same lines.

Lets start with ground work.
I have three tables:

1. Assets (thousands of individual pieces of equipment)
2. Asset Types (±125 categories that all Assets fall into)
3. Attributes (±178 attributes that each need to be completed on at least one Asset Type. Each Asset Type has a field for “Required Attributes” and “Optional Attributes” that pull from the Attributes list. All Attributes are listed on the Assets table as fields, along with additional fields for tracking/organizing. The need for the Attribute is a field on the Asset Types)

So what I’m trying to do is create a field that is % Complete of the required Attributes for each individual Asset. However, the specifics Attributes required to be completed vary depending on the Asset Type assigned. For example:
Asset Type 1 needs attributes A, B, C, D, E (no optional)
Asset Type 2 needs attributes A, B, D, E (no optional)
Asset Type 3 needs attributes B, C, D (A is optional)
I want to make sure that all the required fields (as determined by the Asset Type) are completed.

What I have right now is a field {# of Attributes} that counts the required Attributes per Asset Type in one field, typically ±33. In a separate field {Count Filled Attributes} I have a formula that counts the number of fields that have something in them (using COUNTA(all Attributes)), typically currently ±18. Finally I have a third field that takes ({Count Filled Attributes}/{# of Attributes})*100 = % complete.

My concern is that the {Count Filled Attributes} may cause inaccuracy if an attribute not required by the Asset Type is filled in. This would result in a bloated % Complete and make it look like all the data is complete when all the required attributes may not all be filled in.

Example:
Record 1 = Asset Type 1, Fields completed = A, B, C, D = 80% complete (requires A, B, C, D, E)
Record 2 = Asset Type 2, Field completed = A, B, D, E = 100% complete (requires A, B, D, E)
Record 3 = Asset Type 3, Fields completes = B, C, E = false 100% complete. (requires B, C, D not E)

Does this make sense?

The only way I can think of to make this work properly would be to replace the {Count Filled Attributes} with the single most complex nested IF statement ever. It would need to say “IF(Asset Type=1,COUNTA(A, B, C, D),IF(Asset Type=2,COUNTA(A, B, D, E),IF(Asset Type=3,COUNTA(B, C, E),0)))” now multiple this by ±125 Asset Types and ±178 Attributes. :frowning:

I know this won’t get over the hump of bogus or incorrect data in the fields, but I have other ways of dealing with that later.

Does anyone have any suggestions on an easier way to do this? Thanks.

I forgot to mention, I’m new to AirTable but have been using logic programing and formulas in Excel and Revit for quite some time. As such I’m not familiar with some of the functions like Rollup.