Formula to track social media posts

[Adam_Reaburn]

Hello - We are creating a way to track social media posts for our clients. Clients will ask us to make a certain number of social media posts on their behalf, we then want to keep track of how many social media posts have been made and how many are still required for each client contract.

Here are the two tables we have created

Social Media Posts Created - Airtable - Content overview

Social Media Contracts (requests) - Airtable - All content and requests

I’m new to Airtable, so this may be a simple formula, but any help would be appreciated.

[Carl · Accepted solution]

I’m not quite sure how you have the Base set up for what you want to do.

But it looks like you have one table that shows the requests that is then linked to the other table showing the content/posts. You can create a count field in that table showing the requests and point it at the linked records field. You can then create a new formula field that is just {Field with the total posts needed} - {Count field of the posts that have been done}

[Ben_Young1]

Hey @Adam_Reaburn!
Welcome in!

I started writing out a solution this morning, but I forgot to publish it.

This appears to be a straightforward use case.

One of the first things I ask myself when building or solving something in Airtable is where a given piece of information should live.

In this scenario, you have a contract (campaign), and each contract requires a certain number of posts.
You want to see how many posts have been made towards your contract with a client.

So in this case, you want this information to live on the contract record.

---

The first thing I did to solve this is create three tables. I have Posts, Campaigns, and Clients.

Campaigns are related to clients, and posts and related to campaigns.

In your shared view that you posted, you already had a number field that represents how many posts a given campaign requires in order for you to fulfill it.

I’ve gone ahead and replicated that here.

Next, I built the Posts table, where each record represents an individual social media post.
I also created a field that lets me mark a post into a given status.

---

This is our foundation.

Remember that we want this particular piece of information to live on the Campaign record.
With that in mind, create a new field in the Campaign table.
It should be a rollup field. Here, we will configure the rollup field to count all of the linked records, but only under the condition that the Post Status field equals the “Completed” status.

---

Now we can implement a formula to summarize and format our record information.
I went ahead and wrote a quick formula to provide you with an example of how you can play around with how it’s formatted.

Here’s the full formula that is located in the Summary field.

IF(
  AND(
    {Posts Required},
    {Posts Completed}
  ),
  "Posts Required: " & {Posts Required}
  & "\n" &
  "Completed: " & {Posts Completed},
  IF(
    AND(
      {Posts Required} > 0,
      {Posts Completed} = 0
    ),
    "Posts Required: " & {Posts Required}
    & "\n" &
    "No Posts Completed"
  )
)

Here's a logical breakdown of the formula if you're curious.

This formula is built on the IF() function.
IF(a, b, c)

This indicates that if condition a is true, then return b, and if a is false, then return c.

For the a, we have this:

AND(
    {Posts Required},
    {Posts Completed}
)

The AND() function means that every statement we provide must be true, or else it will return false.
So here, we’re saying that both the {Posts Required} and the {Posts Completed} fields must have values in them.

The next part of the formula is the b.

"Posts Required: " & {Posts Required}
& "\n" &
"Completed: " & {Posts Completed}

This creates the text that appears in the actual field.
We just insert the field values into the text.

The “\n” returns a line break.

The c of the formula is a bit more complicated.
With that being said, if you use what I wrote above, you can start to figure out what’s happening in this layer.

IF(
    AND(
      {Posts Required} > 0,
      {Posts Completed} = 0
    ),
    "Posts Required: " & {Posts Required}
    & "\n" &
    "No Posts Completed"
)

If you plan to use that formula, be sure to double-check the field names, as well as my syntax since I’ve been known to get my quotes a bit mixed up.

Definitely let me know if I’m missing something, or if you have any questions about anything I’ve written here.