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.

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.

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.

2. 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.

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!

Part Two

Return To: Table of Contents

Projects & Invoices

When exploring this part of the base, I wanted to find a way to build something that would highlight some of the dynamic ways you can implement an invoicing and billing system.
I’ll reiterate that I’m taking a few intentional deviations from your requirements for the sake of exploration.

I had to sit on this for a few hours.

The most straightforward thought I had was to simply use a pivot chart to allow us to break down that information.

As a side note to this question:

You only need a single date field to implement these:

IF(
    {Due Date},
    DATETIME_FORMAT(
        {Due Date},
        'YYYY'
    ) & " - " &
    'Q' & DATETIME_FORMAT(
        {Due Date},
        'Q'
    )
)

IF(
    {Due Date},
    DATETIME_FORMAT(
        {Due Date},
        "MMM YY'"
    ) &
    ' - Q'
    &
    DATETIME_FORMAT(
        {Due Date},
        'Q'
    )
)

At this point, I was super curious to push the boundaries on solutions I’ve built before.

Remember this from the first post?

We can now reap the benefits of setting ourselves up in our design.
Before I show you where it comes together, I need to quickly stop at our Team Members table because you pointed out that:

Team Members

Return to: Table of Contents

This table was a lot of fun to explore.
As you might be able to guess, this table contains records for each team member that you might have.
You’ll find any information about a given team member here, ranging from their position/title, billing rate, email address, primary phone number, address, etc.

Additionally, you can also pass in their preferred profile picture. Doing this allows you to do some really cool things when you start looking at page templates for invoices or automations.

When you start organizing your staff with things like their position/title, you can even easily identify and handle your contractors/freelance team members.

Here’s a quick example of that.
Using a quick formula field, I can summarize freelance/contract information allowing for quick and insightful glances at the contract and work history of a given individual.

This also allows you to play with implementing a company/team directory.

As per usual, all data you see is test data generated for this purpose.

Now that we’ve done an initial glance at what’s here, let’s take a look at some of the nuances that build the overall workflow.

Writers & Support Positions

Return to: Table of Contents

Let’s say you need to identify the writer of a given project.
While a project might have multiple people working on it, there is a primary writer that takes ownership of the actual content.

For example, I can have a project with an editor, designer, and planner. Those are in addition to my assigned writer.
Since the writer serves a critical role in the project, we might want to isolate them as being the writer.
While this makes for slightly better sorting and organization, it also lets you go in-depth with how your billing and invoicing behave.
We’ll touch back on that later.

Now that we’ve filled in the billing rate for each team member, we can use that information to build our billing and invoicing.

In my example, I have them billing per project.
There will be slight permutations in how things work if you bill by a different metric, such as on a time-based method.

Paying Up!

Return to: Table of Contents

We have two sources if you sit back and think about where all the information comes from regarding rates and prices.

1. Billing by service
2. Billing per team member

Those two pieces come together at a central point. When working with data design, you might hear these referred to as junctions.

Just because we have information pumping into a single point does not mean that we can just call it a day and hope that it all works out fine.
We have to synthesize that information to properly support our workflows.

We’re now at the point where it comes full circle.

Rollup Galore

Remember that rollups let us take a bunch of data from other sources and transform or summarize it in a different location.

In this case, we want to work with the billing/service rates from both our Service and Team Members table.

To start, I’m going to create a rollup from the Service table.

This will give us the rate as shown in the service table.

Now we need to handle the billing for the team members that are assigned to the project.

Remember this?

Since the writer serves a critical role in the project, we might want to isolate them as being the writer.

It will come in handy now.

Look carefully, and you’ll see that I actually have two linked record fields to the Team Members table.

The difference in the fields is that the Writer linked record field only allows us to link specifically our writers in this field.
On the other hand, the Team Members field allows you to assign anyone who is not a writer.

Here’s the thinking behind that choice:

There are situations that I’ve worked with in the past where the billing of a writer or principal staff member needs to be handled differently or called out differently from the rest of the other staff on a project.

This also allows you to clearly identify how much of the overall invoice amount is paid towards the writer or staff member that owned that particular project delivery.

I apologize for the image compression.

Here’s a crude explanation of what you’re seeing.

1. Service Rate → From Service Table. It’s the flat rate on a service.
2. Team Rate → The combined rates for all of the non-writer staff on the project.
3. Staff Rate Total → The combined rates of the staff plus the writer’s rate.
4. Invoiced Amount → This is a field that you type the final charged amount in. This allows you to have the final say and control over project invoicing. There are ways to automate this, and I’m happy to show you if you’d like.

There could be a ton of different things that might be added to the invoice amount.
I’ve enjoyed using a simple note field to note down additional things.

When you take all of this work that we’ve done, you can do things like fuel some dope document generation without having to do any duplicate data entry.

Recording Payments

Return to: Table of Contents

You may have noticed that I have a single-select field that shows the status of the invoice.
Let’s say that when you get a new project, the client needs to pay the 25% deposit fee of the flat rate service fee.

The remaining invoice amount will be due when the project is completed/published.

To show this off, I will create a new project record and show you the invoice/billing workflow.

Here’s my new project record. I have added my client, my writer, and my team members.

Now, since I’ve added my team and I’ve added the service that this project is based on, all my billing information is present and has automatically filled in.

Since the project is new and is in planning, the only balance that’s due right now is the deposit amount.

Here’s the Balance field:

Balance: $171.25
Total Paid: $0

Here’s the Billing Breakdown field:

Service Rate: $685
Staff Rate: $581
Invoiced Amount: $0.00

Here’s the Deposit Amount field:

Deposit Due: $171.25

Here’s the Publishing Amount field:

Due At Completion: $513.75

Okay great! We have our billing information!
Now that we have our invoice calculations, we can run our deposit invoice!

I’ll go ahead and input the amount that I want to run the invoice for. From there, I can click my Invoice button.
Airtable will then fill in all the record data into the template I created.

I’ll go ahead and add this invoice to the record for record keeping.

You can then send the invoice to your client or client contact.
If you’d like, I can show you how to build an automation that sends out your invoices to your contact for you.

So what happens when your client pays the invoice?
We need a way to record our payments and update our billing information.

This is where we see the new Payments table!

Each record in this table is for each individual payment made by a client.
We can designate payments that are being processed, processed, whether they are for deposits or for completed projects, etc.

We’ll imagine that our client has paid the invoice we sent them.
So we will create a new payment record and fill in the information.

Since we link our project to our payment, our balance is now $0.00

You can also see that our Balance field now shows a zero balance and that the deposit has been paid.

What happens when the project is completed or published?

Now that our invoice is due, we can update our invoice amount to the final billing amount.

I’m going to enter in a ballpark-ish number for our final invoice amount that takes into account all of our staff billing rates and service balance.

We can then run our final invoice and issue that to our client.

And when they pay, you can just record it as we did before!

A quick note on reporting:

A small side-exploration I did was create a table that breaks down your months/years/quarters.

It allows you to see which clients did business with you that month, the number of projects, and the total revenue for that time period.

I didn’t explore it much, but we can explore it a bit more if you want.

Return to: Table of Contents