Identifying complete projects

[briec]

Hi there and thanks in advance for the help. (Sometimes I solve my problem just by typing it out, so we’ll see if that happens again. If you are reading this, it has not!)

I am processing records from a variety of architectural projects, which may be found all together in one place or, more commonly, in many places. So, for instance, materials from a project called Little House might be found in Boxes 1, 4 and 12. I am removing materials from their original boxes into new boxes, uniting the materials.

I have a master list of my projects (masterprojects). In a separate linked table (projectswithboxes), the primary field is autonumbered and I have a separate row for each time the project appears in a different box. So Little House would appear three times in projectswithboxes, and the table includes a column that shows the box number in which it can be found.

Since it’s not always possible to clear things from boxes 1, 4 and 12 at the same time, I am have another column (emptied?) that indicates if the project’s materials have been cleared from the boxes. So once I take everything out of box 12, it gets a check mark, while 1 and 4 are unchecked. This allows me to sort and filter by project or by the box I am working on. (I do not want to delete this rows when they are complete for record-keeping purposes.)

I want to be able to check in on how many projects I’ve totally completed vs. how many are left. Either in my master list or a new table, I want to know when each project is completely processed - when its materials can no longer be found in any of the original boxes. I think what I want is a formula that looks in projectswithboxes and reports back with the project name (Little House) when it finds no unchecked boxes in the empty? column for a particular project. Another solution would be to ask it to count the number of unchecked boxes for each project, as I can just filter for 0, etc.

I’ve asked airtable to do more complex stuff than this, but struggling to wrap my head around how to write this one up for some reason. Your suggestions are welcome. Thanks!

[Alex_Wolfe]

Is my understanding of your request accurate below?

1. You want to calculate if a Project is ‘totally completed’ vs. ‘incomplete’?
2. Your completion metric is based on ‘Boxes,’ where if the boxes are all checked the project is considered ‘complete’?

I believe there should be a formula solution for this (it may require >1 formula field) but would you be able to share a screenshot or read-only link of the table? I am not sure what you mean by ‘Box 1, 4, and 12’ is why I ask.

[briec]

Hi Alex,

Yep that’s exactly what I want to do. And all boxes checked for a given project does mean its complete.

I created a simple example version of my base, as my data is sensitive. But I think this covers all the bases (ha ha) - let me know if I missed any data you’d need (or if the link doesn’t work).

You’ll see on the second table (projectswithboxes), that I have dealt with all the material for Small Building (which was only in Box 5) and Zoo (which was in Boxes 1 and 2) - those projects should be considered complete. I have removed some material for Little House (Box 12) and Airfield (Box 11) - but those two projects still have material in other boxes, so they are incomplete.

I do not need to track if the boxes themselves still have material in them (i.e. there is still material for Airfield left in Box 12 after I removed the material related to Little House). [Tho on second thought, it should be a similar formula, so I’m not against tracking it. :slightly_smiling_face: ]

Note: the WhereIS field on the main table is just a lookup that shows me all the boxes for each project on the main table.

Link here: https://airtable.com/shrTw9jUFpXv3szS8

Thanks!

[Alex_Wolfe]

Ok I think I understand better now, thank you. Here’s my first impression ‘solution’:

1. Add a Count field to your PROJECTMASTER table. This will give you a count of the linked BoxIDs for each project.
2. Add a Rollup field to your PROJECTMASTER table, and rollup your PROJECTSWITHBOXES table’s Box/Project Cleared? checkbox field. Then use the SUM(VALUES) formula for the rollup.
3. Now you have two new fields in your PROJECT MASTER table. One that counts the number of total boxes linked, and one that counts the total checked Box/Project Closed? fields.
4. Add a formula something like this IF({insert new count field name here}={insert new rollup field name here},“Complete”). This essentially checks if the total number of boxes = your total check marks for that project in your PROJECTSWITHBOXES table, then all boxes are ‘checked’, and therefore, the project is ‘complete’
   (here you can customize this IF statement/formula to say whatever you want for ‘Comlpete’)

Is that what you are looking to accomplish? The only confusion I have is the difference between BoxID and Box# but I’m assuming this is irrelevant for our purposes. Let me know if I’m off base :winking_face:

[briec]

Hi Alex,

Thanks! I will try these out tomorrow and report back.

In my actual base, the BoxID is a formula that computes a box number and a code (so it is similar to what you suspect). I was trying to remove a layer of complexity, but maybe it’d be easier to use the full codes. I updated the base in case that’s the case and you have further thoughts.

Thanks! looking forward to trying this out. (and nice one)

[briec]

Alex!

This is exactly what I wanted to do. Works perfectly and your instructions were clear and easy to follow.

Appreciate your help and have a fabulous rest of your week!

-Brie

[Alex_Wolfe]

No problem - glad it helped!