Hey @jordi_comas! Welcome in!
I may have just totally misunderstood your needs, so please feel free to let me know if I’m completely missing something.
I went ahead and just pulled out all the stops and created a small example of how I might lay things out.
It can be pretty jarring for people when getting their hands on Airtable because tables are distinctly different from how you might think of a sheet within a spreadsheet.
With that in mind, an excellent way to think about data modeling for your use case might be to think about what types of records you’ll be creating and what data they’ll be holding.
If you’re curious to see what it looks like or want to copy the base, here’s a link to the base.
Your Table Layout
From how I understand it, you have two distinct types of records.
Tasks and their respective “projects.”
So with that in mind, we need two tables to hold those distinct record types.
I have created these tables in the base I built in my playground.
Now, each record in the Tasks table will only hold individual tasks that need to be done. This is regardless of whether or not they’re related to the same project.
This is the same in the Projects table, where each record is a new project.
Okay, but then how do I organize and sift through everything?
This is where one of Airtable’s foundational designs comes into play!
Airtable is a relational database, which means that we can associate records with each other.
The advantage here is that when you get to a point where you have a ton of data, you can track an immense amount of data without having to worry about it all getting mixed up and unorganized.
We will put that into practice in this small, isolated environment.
Projects
In the Projects table, we have three records for major projects my theoretical team might be working on.
In this case, we’ll focus on my Empire Building project.
Here I have some crucial information, such as my notes on the project itself and the overall stage that the project is in.
In this case, Empire Building is in the Backlog stage.
(You’ll also find that there’s another field in there called Completion Status.
It’s a bit complex, so I’ll explain it at the end as a piece of bonus content.)
Tasks
In the Tasks table, we hold individual records for every task that we want to keep track of.
However, the problem we quickly run into is organization.
I can have a million tasks but no way to keep track of everything.
Airtable offers a few ways for us to organize records, but we want to focus on linking these tasks to the Projects they belong to.
You’ll notice that there is a linked record field called Project on the far right.
When you create a new task record, you’ll be able to search for the project it belongs to and then establishes the link between the two records.
We can use the Grouping feature to group the records by their respective Projects.
In this case, they’re grouped into the three projects we saw in the Projects table.
In addition to the linked record field, we also have task status, notes, and start/completion dates.
Views
Views are low-key rather wild.
There’s an insane amount of things that you can do with them, and it can be a bit overwhelming while you’re getting comfortable with them.
For this example base I’ve created, I have a few examples we can look at that seem along the lines of what you’re looking for.
Here I’ve organized my views into Sections.
You’ll see sections for each primary project, as well as an overview grid view with all the tasks displayed.
Here we have the Timeline view.
From the grid view, I’ve set the start and end date configurations to be the respective start and goal completion dates.
I’ve also set the view to display at the annual quarter level.
As my last example view, I’ve made a quick Kanban view that is filtered to only display the Empire Building related tasks.
Now For Some Clean Up
When you establish a link between one record to another (in this case, we created a link from the tasks to the projects), you aren’t creating a one-way link.
It works both ways.
I had a linked record field hidden in the view in my previous screenshot of the Projects table.
This was automatically created when I created the linked field in the Tasks table.
This means that you do not have to go through the linking process two times to do the same thing in the Projects table.
It may seem simple, but I’ve seen many people do it.
Hey! What’s with the Completion Status field?!
This one is just me kind of messing around since I like to use community forum posts to have fun and experiment with my data modeling, formulas, automations, etc.
In addition to the hidden linked record field, I had four other hidden fields in the view.
These are rollup fields.
Rollup fields are one of the ways you can actually pull data from the other table you linked to.
In this case, I can take data from the Tasks table and use it in the Projects table.
The Completion Status field is a formula field that takes the information from the Tasks table and creates a little status message for you about all of the tasks associated with a project.
In this case, it just gives you a breakdown of the task statuses.
Here’s the (strange, possibly poorly written) formula that powers that field if you’re curious.
IF(
OR(
{Total},
{In Progress},
{Complete},
{Not Started}
),
IF(
{Total},
IF(
{Total} = 1,
{Total} & " Task Total",
{Total} & " Total Tasks"
)
) &
IF(
{In Progress},
"\n" & {In Progress} & " In Progress"
) &
IF(
{Complete},
IF(
{Complete} = 1,
"\n" & {Complete} & " Complete",
"\n" & {Complete} & " Completed"
)
) &
IF(
{Not Started},
"\n" & {Not Started} & " Not Started"
)
)
Wait a second! I saw another formula field in the Tasks table!
If you look at the primary field of the Tasks table records, you’ll notice that it is also a formula field.
This formula takes the record data and compiles it into a quick & easy to read format (for me at least) that keeps me from finding all of the data in a record. This allows me to get high-level information by glancing at the primary field.
Again, it’s mostly me just experimenting.
If you’re curious, here’s the formula:
IF(
{Task},
{Task} & "\n",
"⚠ Missing Task Name!"
)
&
IF(
AND(
{Started},
{Goal Completion}
),
"Start: " &
DATETIME_FORMAT(
{Started}, 'l'
) & "\n" &
"Goal Date: " &
DATETIME_FORMAT(
{Goal Completion}, 'l'
),
IF(
AND(
{Started},
{Goal Completion} = 0
),
"Start: " &
DATETIME_FORMAT(
{Started}, 'l'
),
IF(
AND(
{Started} = 0,
{Goal Completion}
),
"❗ Missing Start Date!"
)
)
)
But seriously, please let me know if I’m being stupid and missed something or if you have any additional questions.
