Help

Re: Arriving in the Airtable Jungle

3576 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Madeleine_Walth
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi everybody!

I’m new in Airtable and I really enjoy this tool. I watched some tutorials and read about Airtables functions. I tried things out, and what should I say, it works very well! Now I try to organize my company’s tasks but I don’t get a quite clear idea on how to do it. My problem is the amount of different tasks and things I need to add. I guess it is not very complicated for you guys, therefore I would appreciate some ideas and insights on how to structure my Airtable.

I’ll try to explain as clear as possible all the tasks and needs I have.
We are a content and marketing agency. That means we have two teams - marketing and content with different projects.

What I have already:

  • I have a clients table
  • I have a service table
  • I have a team table with the different team members and their skills and availability

The problem comes with the table of the projects:

Let’s talk about the content part. We create blog posts, whitepapers etc. for different clients. Some are occurring regularly and some just once. I need to create a content calendar where I see when we have scheduled articles, who is doing it, what topic, who is the writer - I managed to do all this. The problem is now that my data is in a huge list and I don’t have a good overview. I would like that I can separate them by client and month without creating 20 new tables. I want a list with all projects from the same month, but also from one client. I know there is a filter function, but I feel that is not good enough. I want to schedule my content for the rest of the year and I don’t know how to really organize the amount of information I have without losing the overview. To every article there is a lot of data and also billing information. I need to find a good way how to organize all of it.

Do you have a good idea how I could structure my Airtable in the right way?
Thanks for your help and time.

11 Replies 11

Welcome to the community, @Madeleine_Walther!

And welcome to the jungle! :guitar: :notes: :musical_note:

For managing your content, you definitely don’t want to create a bunch of extra tables, but what you will probably want to take advantage of is Airtable’s views, which lets you create different views that show you just the information that you need to see:

I cover views in depth in my free Airtable training course:

You may also want to start dipping your toes into Airtable’s interfaces as well, which could provide you with more clarity when you’re looking at your data:

If your company has a budget for these sorts of things, you may want to work one-on-one with an expert Airtable consultant (such as myself) to help you perfectly setup & structure everything in your entire base:

Hey @Madeleine_Walther!
Welcome in!

These are kinda just some notes before we talk about your goal.

I presume it’s safe to say that you might have caught the Airtable itch.

You’re running into the biggest learning curve of Airtable.
Airtable provides you with so many possible ways to build, design, and configure your solutions, that it can quickly become overwhelming.

If you persist, then you will be able to build some remarkable solutions, but if you settle into trying to use Airtable as a spreadsheet, you will be disappointed and will probably quickly give up on it.


Now, onto your use case.

Here are the requirements I’ve gathered from your post:

  1. Clients: This table contains records for every client your company works with.
  2. Service: :question: Not really sure what this table holds, but I presume it contains records for every service that your company offers.
  3. Team: This table contains records for each team member in the company.

For the Projects table:

  1. Each record is for an individual project y’all are working on for a client.
  2. You want to be able to easily manage the project records by using data like:
    • What client was it for?
    • What month was it completed/started/etc?
    • What about both?

I had a bit of extra time this morning, so I went ahead and created a demo base that encompasses how I imagined what you described.

Everything in there is test data, so don’t worry about any PII or sensitive information if you want to copy the base into your workspace and copy different things.


Grouping

You’ll find the grouping solutions in the Projects table.
There are respective views for:

  1. All projects grouped by their associated client.
  2. All projects grouped by their associated year/quarter.
  3. All projects grouped by their client, and then sub-grouped by their year/quarter.

image

image

image

To group by the year/quarter, I wrote a quick formula that spits out a value based on the Due Date field.
It will provide you a value in a similar format to 2022 - Q1.


If you’d like, I can do a more in-depth walkthrough on how it’s all built, and I’d be happy to answer any questions you have or additional requests in this particular base.

Hey Madeleine,

You’ve been given some great advice!

Views in a single table are your friend!

Here’s how we manage content marketing at On2Air Airtable Apps. There are some screenshots and examples listed.

______________________________________
Hannah - On2Air.com - Automated Backups for Airtable

Hey ScottWorld,
thanks for your helpful answer. I’ll consider to have a one-to-one consulting once I set up the basics. Thanks for taking your time to help me out.

Hey Ben,

thanks for your nice and long message and of course for the demo base. I’ll try to answer your questions and explain best I can what I did until now.

1+3. Clients + team:
Yes, this table contains all info about the clients: name, website, person in charge with mail etc. and the team members: position, name, mail etc.

  1. Service:
    We create articles, newsletters etc. So I have services like: newsletter1, newsletter2. etc. because of a different rate.The content table contains all kind of content we write such as newsletters, blogposts etc. I created there different view for each company.

The goal is now to get a monthly overview and create invoices, or at least a billing list. This means that each article is connected to a service. This service has a special rate. Now I would like to have a sheet where I can see which articles I did for which company in the month of XXX and how much I earned in total. I understand I have to connect to an invoice channel the client, the service and the month to create that, right?

For the project table/content table: I added due date but not the month, should I add a second date field?

I understand I can work with the grouping as well to solve problems. Have to study how it works exactly.

I actually have some questions to billing etc. Because I would like to add the rates of the team members to get a better overview of how much profit we make at the end of each month, but well…Let’s start with the basics.

Thanks for your help, that is amazing.

Thanks Hannah!

I will have a look into it.
Thanks for your answer and time :slightly_smiling_face:

Hey @Madeleine_Walther!

I will build out a couple of solutions to walk you through.
I’m in the middle of working on them; just wanted to give you a heads-up so you don’t think you’re abandoned!

Ben, that’s super nice of you! Looking forward to it. Thanks a lot!

Hey @Madeleine_Walther!

I’m sorry I took a while to get this back to you!
Hitting the weekend gave me enough breathing room to tie this up!

I got a bit carried away and just continued building within the demo base I shared with you.
Since I’ve been working on it, all the changes I show you here are also reflected in the demo base.

Again, feel free to open it and copy it into your workspace.

I do apologize for the density of this. I’m a huge believer in creating repositories of information for users. So the information here is for you as well as for users in the coming years that might stumble upon it and find that it greatly benefits them.

Because of the sheer volume, I’ll probably end up breaking this into separate posts. With each post, I’ll update the Table of Contents.


Table of Contents

Post One

  1. Clients & Contacts
  2. Services & Rates

Part Two

  1. Projects & Invoices
  2. Team Members
  3. Paying Up!

Clients & Contacts

Okay, I might be slightly misunderstanding this portion.
From what I understand, you’re using a single table to keep track of both your clients and your team members.

If you are, then here's a bit of context as to why that's probably not a good idea.

When thinking about a database, consider the data you’re storing.
More specifically, consider the ‘types’ of records you’re storing.

The rule of thumb is that you want each table to contain records for a single type of ‘thing.’

  • The Clients table → records for each client.
  • The Services table → records for each unique service.
  • The Projects table → records for each unique project.
  • etc.

This separation is critical when you begin to pump a lot of data into your database.

Imagine you’re keeping between 50-100 mixed record types in a single table.
With that many records, you can kinda pick out the data and interpret what you’re looking at.

The problem becomes painfully apparent when you add in more data than you can reasonably digest at once. This is where Airtable as a database becomes the foundation for you being able to manage and manipulate that information easily.
Airtable will only help you organize data if you feed it into a considered design. It will do a lot of the work, but only if you give a tiny bit of work at the start.

Besides having “dirty data” mixed up and disorganized, you become crippled by the inability to leverage linked records.
Establishing strong, consistent relationships between clean data is something that multi-billion dollar companies pay millions of dollars for their staff to do.

This entire base/demo solution would be impossible to implement without that clean separation.
Just something to keep in mind.

The other way to interpret what you described is that you have contacts that are associated with your client accounts.

There are some substantial advantages to keeping track of client account contacts.
I’ll highlight some as we go.

image

image

So I’ve created a new table called Contacts. Each record in this table is a person that is associated with a client.

image

Question: What is the benefit of doing this?

It depends.

One of the benefits of seeing which contacts are associated with a given account is that you can isolate a contact as a primary account owner. i.e., Your primary contact.

You can also indicate if a project has a specific person as your contact point. This can be useful if you’re working with an isolated team in a company.

In our scenario, I primarily added this table because it allows us to add flexibility when handling billing and invoicing, something I know you previously indicated that you wanted to explore.


Services & Rates

A quick note on the design I have below:

I wanted to explore and show you a few more nuanced ways to implement a billing and invoicing solution. With that in mind, I am adding some twists and additional ‘features’ that you didn’t explicitly state.
The two add-ons I placed are deposit and publishing amounts.
Just a heads up in case you are confused about why I added them.

This is excellent context!
I took a bit to explore this implementation and have an exciting approach to this requirement.

image

I first created a new currency field for you to set a flat service rate for your service records.
In addition, I have two formula fields that break down a 25% deposit fee and the total amount that remains minus that fee.
image

Here are the two formulas that power those fields:

  1. Deposit Amount
IF(
    AND(
        NOT(
            {Pricing Model} = "Free"
        ),
        {Rate Per Project}
    ),
    "Deposit Due: $" &
    FLOOR(
        ({Rate Per Project} * .25),
        0.01
    ),
    "Deposit Due: $0.00"
)
  1. Publishing Amount
IF(
    AND(
        NOT(
            {Pricing Model} = "Free"
        ),
        {Rate Per Project}
    ),
    "Due At Completion: $" &
    FLOOR(    
        ({Rate Per Project} - ({Rate Per Project} * .25)),
        0.01
    ),
    "Due At Completion: $0.00"
)

Question: What do we gain by building this information in this table?

Put simply… Rollup & Lookup Inc.

You'll see how it comes together as we go, but if you want to read a branch about it, click here.

Always remember: Tables contain records for each ‘thing’ we want to store information on.
It’s a fundamental design principle when working with databases. When you think about the implications of working by the principle, you eliminate duplicate data entry. If you find that you’re entering the same data more than once, then something is wrong in your workflow.

Some people might sit back and say, “Well, I’m just going to put that information somewhere else; I don’t need to put it here.” Did you catch the problem?

Your rates are attached to your service.

“Okay, cool. But I need that information in other tables.”
Using rollups and lookup fields, we can give a piggyback ride to data in other tables. Furthermore, we can transform and summarize that data in transit.

Here’s an easy way to visualize it:

image

image

In my demo base, I have unique names for each service.

image

In this example, if I wanted to have multiple “Marketing Plus” service records, I’ll want to duplicate the primary field, Name, to allow us to create a new primary field.

Why do we want a new primary field?

The primary field should have a unique value for each record.
To maintain “clean data,” you want to be able to uniquely them.
This is crucial when you start adding more and more information to your database.
If you have ten records with the same names, you will quickly run into problems.

So if we have multiple “Marketing Plus” records, but each has a different rate, how do I tell them apart?
We can use a formula field!

image

Here, I have duplicated the primary field and made it a single-select field.

Question: Why did you change the single-line text field to a single-select?

If we had to type “Marketing Plus” into each record manually, we would be breaking the golden rule of eliminating duplicate data entry.

Here’s the formula used in the new primary field.

IF(
    AND(
        {Service Name},
        {Rate Per Project} > 0
    ),
    "#" & {ID} & " " &
    {Service Name}
    & "\n" &
    "$"
    & FLOOR(
        {Rate Per Project},
        0.01
    ),
    IF(
        {Service Name},
        "#" & {ID} & " " &
        {Service Name}
        & "\n" &
        "Free Tier"
    )
)