Accepted Solutions
Hey @J715!
Welcome to the forums!
My brain is a bit scrambled right now for a few reasons, so it’s a bit hard for me to write everything out.
With that in mind, here’s how I would go about implementing a solution for your requirements.
Wanted to take a quick pause here to answer this question.
I had a solution for this question, but I quickly realized that it’s limited since Airtable’s automations do not allow you to iterate through arrays.
Unless you’re comfortable with scripting this, I don’t think you will be able to build a scalable solution for this.
Databases can be weird.
They can sometimes require that you sit back and trace the wires between everything you’re trying to piece together.
I tried writing out an answer but struggled a bit, so I will walk through how I would approach your use case start-to-finish.
You certainly don’t have to overhaul your current solutions, but hopefully, you’ll be able to pick out a way to think about approaching your current workflow.
You’re probably going to want three tables for this use case.
1. Team Updates
2. Days
3. Team Members
I’ll start with the Team Members table.
This table will have individual records for each of your team members.
I filled it in with some random information for the sake of the example.
This certainly works, and there’s absolutely nothing wrong with this method.
It’s really a question of organization and scalability.
By having dedicated records for each team member, you can store unique information about your team members. From there, you can draw the relationship between them and their respective log records.
In this context, I have things like their role and their email addresses.
You can scale this up to hold any relevant data you might want to store about them, such as their working hours, timezone, other contact methods, etc.
Establishing this structure also has some really cool advantages that we’ll look at a bit later.
Next, I created the Days table.
This table will have an individual record for each unique day.
A key part of thinking about records and databases is uniqueness.
Each record should not just be unique, but it should be easily identifiable as unique.
This complicates things a bit.
From how you’re describing it, I’m imagining it like this:
While this doesn’t seem too lethal at first glance, this can come back to haunt you over the course of time.
This example configuration has five team members with updates over the course of four days.
With that, we’ve created 20 records.
Whilst I’m describing a purely theoretical scenario, that’s a massive amount of data bloat.
When you scale that into a month, that’s 150 records for just 30 days.
The next part of that issue is organization.
Taking those 150 records makes it almost impossible to quickly sort through them to find what you might be looking for.
It just defeats the purpose of keeping detailed meeting logs since you’ll never be able to really leverage them.
Sure, you can group them and sort them by dates, but even that doesn’t solve the fact that you have multiple records identified as “8/5/22” or that you can’t tell whether or not someone is missing a report record from last week.
It’s harsh.
By having a table with unique records for each individual date, we protected ourselves from having to contend with an expansive table of records with massive amounts of duplicate data.
The real advantages to this approach will come into view a bit further down.
The next table is the final piece of the puzzle. The Team Updates table.
I apologize for the resolution scaling.
Each log here is a unique update for a team member that happened on a unique day.
You’ll see that our log records are linked to a (1) unique calendar day and (2) a team member.
This creates a new problem.
How do we create a unique identifier for a record that is the junction between other tables?
For these instances, I like to use a formula field.
In this table, I created a formula field as the primary field that just takes the date of the report and formats it into an easily readable format.
In addition, it will also provide you with the name of the person to whom the report is linked to.
So from a glance, we can instantly see who the report is for and when the report is for.
The raw formula I used can be found here.
IF(
{Date},
DATETIME_FORMAT(
{Date},
"ddd, MMMM Do, YY'"
),
"Missing Report Date"
)
& "\n" &
IF(
{Team Member},
{Team Member},
"Missing Team Member"
)
For visibility, I created a rollup field displaying the role of the person the report is for.
Now that we have built the bones of our data, here’s a visualization of what it looks like:
Kinda confusing, but here’s the spoken layout:
- One team member can be linked to multiple logs, but a log can only be linked to one team member
- One day can be linked to multiple logs, but a log can only be linked to a single day.
Okay, now that we’ve established our structure, we can take advantage of some really powerful tools that come with working with databases.
To highlight some of this, I created a ton of new data for our base to hold.
I started by setting my company roster to 60 total people with a varying array of roles.
Then I created a ton of new log records for a total of 123 records.
Using the role lookup I created, we can group them to see every log we’ve created for each role.
In this case, let’s say I wanted to see every report made for the Assistant Manager role, or maybe I want to see everything for my designers and engineers. It’s all quite easy now.
Let’s hop into our Days table.
Since all of our reports are neatly linked to their associated date records, we can use rollups to build super dynamic and slick summaries of the data that is linked.
So this:
Can become this:
Even better, if want to see every report made for a given day, we can just pop into the record and quickly see it all at a quick glance.
Super clean.
Similarly, in our Team Members table, we can also build summaries to show us stats, such as how many reports we have for that given team member.
Okay, so all of that is really dope.
But what about this:
Sweet, so since we’ve built a strong foundation for our data and how it’s held, we can easily account for this; however, as I stated at the start of this post, I don’t think you will be able to do this without scripting this action easily.
Here’s what we can do.
Today is 8/19/2022. Let’s say it’s the start of the day, and we want to have our log templates set up and ready to roll for the day.
We’re going to build two automation for us to do this.
We’ll set our trigger to fire off at a scheduled time.
In this case, we’ll set it to trigger at 7:00 AM every day.
When this happens, we’ll have it create a new record in our Days table.
We’ll have it fill in the Log Date field with the date/time of when the trigger was set since the trigger will only go off on the date we want the new logs to be created.
(Since our Log Date field is set to not care about time values, the automation won’t care that there is a time included in the data we feed it.)
Testing this will show us the newly created record.
From there, I think the simplest way forward is to create a new step that creates a new record in the Team Updates table and links it to the new Days record.
This will have it automatically pop into your view for the day’s new log records where you can then quickly start creating and filling them out.
Again, my brain is kinda scrambled right now, so I will probably need to revisit and reword some of these thoughts in a bit, but for ease of access, here’s a link to the base I built.
Feel free to copy it if you’d like.
