Using Rollup to check on completed tasks?

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.

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.

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

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.

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

Still testing this out…

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.

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.

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.

Do I understand this correctly?

• when you create a new Asset, you assign it an Asset Type
• that Asset Type has a set of Required Attributes associated with it
• you have some process of adding Completed Attributes (or something along those lines) to the Asset, but you do not differentiate whether that Completed Attribute is on the required or optional list for that Asset Type (and presumably it would be implausible/unworkable to differentiate that at the time of adding a Completed Attribute to an Asset)

You’re on the right track. We have a number of different people adding information to the Assets throughout the life of the project so no one person inputting data knows when everything is complete. Plus I want to be able to track progress throughout not just complete or not.

Within the Asset Types table is a column for which Attributes are required and which ones are optional. So yes you could have a column in the Asset table that says “For this asset: X Attributes are required, Y Attributes are optional” that would be driven by a lookup from the Asset Type selected.

The problem is that Fields in the Asset table don’t equal records in the Attributes table, other than having the same name.

Ya… this is a tough one. I’ve been trying to work it out, but I can’t find any good way as of yet to compare two arrays and subtract the values of one array from the other.

@Katherine_Duh @Matt_Bush @Howie @Alexander_Sorokin @Kasra

If that were possible through some Array Function, say ARRAY_DIFF({array1}, {array2}), we could take the Lookup field {Optional Attributes} on the Asset, and subtract the array of those values from the array of values in the {Completed Attributes} field, leaving an array of only {Completed Attributes} that are also {Required Attributes} - call it {Completed Required Attributes}. Then {Completed Required Attributes} could be used to compare against {Required Attributes} to generate your {% Complete}.

I don’t know if that constitutes a #feature-requests or not. If so, perhaps @Katherine_Duh will make one out of my post, or else direct me to make a new one.

In the meantime, if I think of some workaround here I’ll let you know.

If there are a manageable number of asset types, you might want to consider not worrying about normalizing the % filled function but instead setting an alert on records with an extraneous alert entered.

For instance, create an alert field similar to this

IF(
    {Asset Type}=2,
    IF(
        {Attribute C},
        '🔥: C'
        ),
    IF(
        {Asset Type}=3,
        IF(
            {Attribute E},
            '🔥: E'
            )
        )
    )

Or words to that effect. That would serve both to catch potentially bogus % numbers and help employees adhere to the proper attributes per asset type…

Edit: If you must fix the calculation end, I think I have a way to do so; my only concern is that it may be a bit, well, twee for the purpose. (That is to say, I may be making it more difficult than it need be.) Let me kick it around a bit more and re-read your post, and I’ll let you know if it holds up.

@Robert_Glover @Katherine_Duh @Matt_Bush @Howie @Alexander_Sorokin @Kasra

Another possibility is if we had an Array Function that acted basically like a for loop and could resolve on other Functions like the SUBSTITUTE() Function - let’s call it
EACH(array_to_loop, array_item_variable, function_to_perform_with_variable).

In your case, @Robert_Glover, we could write:
EACH({Optional Attributes}, opt, SUBSTITUTE({Completed Attributes}, opt, ""))
to cycle through each string in the {Optional Attributes} array, save it as the variable opt, and use it to perform a SUBSTITUTE() for each occurrence of opt in {Completed Attributes}.

There’d have to be some check in there too to ensure the right data type is coming out of the array to be used in the function_to_perform.

It’s probably super far-fetched in terms of whether airtable could actually implement it, but it sure would add a lot of power to our ability to manipulate data in our bases. :muscle:

@Jeremy_Oglesby You have made my head explode, or rather I’m drowning in data.
I like the concept of what @W_Vann_Hall suggested, but with 125 Asset Types and 178 Attributes, that would be dauntingly long option, especially since the vast majority (over 100) of Attributes are used by 3 Asset Types or less.

I figure this is asking a bit much of a database program, but I’m still hopeful.
Thank you all.

Sorry, @Robert_Glover - pretty much everything in my last 2 posts was “feature request” material directed more at the airtable employees I called-out than at you. It’s all stuff that I would like to be able to do in airtable, not suggestions of what you can do right now. You cannot do any of those things.

Sorry there isn’t a clean and easy solution to your issue. Hopefully it’s the kind of thing airtable will see as a use-case for adding new Formula Field functionality that can handle this kind of need!

I’m stumped on a way to utilize Airtable to figure out which tasks are completed. This is for a photography studio.

I have a table of COMPLETED SHOTS that utilizes the rollup feature to populate an Assortment List table with a DATE SHOT field. This rollup feature is linked by a SKU.

Problem: My Assortment List table has duplicates SKUs, but only some of these SKUs are marked with PHOTO NEEDED indicated by a field with a check box. Right now the rollup is populating DATE SHOT with the correct SKU but not always the SKU associated with the PHOTO NEEDED checked.

I want to be able rollup the DATE SHOT to populate the SKU line with the checked field PHOTO NEEDED.

I’d like to be able to filter by: (PHOTO NEEDED is CHECKED + DATE SHOT is EMPTY) and see exactly what is still outstanding with the trust that the rollup isn’t populating a duplicate sku’s DATE SHOT that isn’t checked as PHOTO NEEDED.

Is what I’m asking for possible? Thank you for your thoughts.