List of non-completed tasks for a project

Question

I have a table of projects, and a table of tasks, and I can create new tasks within a project by entering them directly in the column linked to the tasks table.
First of all I’m curious if this is the best way to do things, since technically I’m creating a many-to-many join where I can have tasks that are unassociated, or tasks that are associated with several projects, both of which I have no intention of ever doing; I only want each project to have 0 or more tasks that are strictly associated with that one project.
More importantly though, when I finish a task I created a checkbox field on the task called “complete” which I would like to cause my task to be hidden from the tasks field in the projects table. I had assumed that creating a view of incomplete tasks in the tasks table (which I did, and it works fine), then checking the “Limit record selection to a view” and selecting the incomplete task view, would do exactly what I want. But it doesn’t, and I don’t even understand what it could possible do if not that! So what is limit record selection to a view, and if not what I need, is there a way do do it?
I’ve just started using airtable for this and so I could start my entire project/task list from scratch pretty easily if there is a better way of organizing things!

Justin_Barrett · Accepted Answer

Welcome to the community, Maxwell! :grinning_face_with_big_eyes:

It does quite literally what it says: it limits record selection—i.e. the ability to manually select records from another table—based on records visible in a specific view of that table. It doesn’t change existing linked records.

What you’re trying to do—dynamically change the collection of linked records (tasks) tied to another single record (your project)—is not currently possible in Airtable. However, with some workarounds, something similar could be achieved.

Here’s a dummy [Projects] table I made, with a single project that links to three tasks from a [Tasks] table.

On that [Tasks] table, I have a {Done} checkbox field, the reciprocal link field pointing to [Projects], and a formula field named {Name Copy} containing this formula:

IF(NOT(Done), Name)

Without any tasks marked as done, it looks like this:

Back in the [Projects] table, I added a Rollup field named {Tasks to Complete}, choosing the {Tasks} link field as the source field, the {Name Copy} field as the field to pull from, and ARRAYCOMPACT(values) as the aggregation formula, which gives me this:

Once I check off the first task, its {Name Copy} field goes blank:

And the record in {Projects} now looks like this:

Feel free to hide {Name Copy} to clean up the look of that table if you wish.

The downside is, of course, that you have two fields with tasks in [Projects]. However, because the {Tasks} field only serves to create the task links, you can shrink that one down much smaller. The info you really want to see—which ones are completed—is in the {Tasks to Complete} rollup.

This is great, thanks! The duplicate field isn’t even that bad since my rollup field will contain things I need to do, and my tasks field will allow me to look back at the OLD tasks that may contain relevant notes. The only thing that would make this a little better would be the ability to click on tasks in either field to blow them up and look at all the details, since currently I can look at my tasks to complete but still need to click in the tasks field see detail.

Maxwell_Pistill · Answer