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.

[Jeremy_Oglesby]

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)

[Robert_Glover]

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.

[Jeremy_Oglesby]

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.

[W_Vann_Hall]

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.

[Jeremy_Oglesby]

@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:

[Robert_Glover]

@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.

[Jeremy_Oglesby]

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!

[Gary_Webb]

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.