Creating (and Possibly Emailing) a Summary of Ongoing Projects and Team Members

[Oli_Chance]

Hi there,

Almost complete novice here, so please excuse any foolishness.

I’m using Airtable to keep track of a pool of freelancers and the projects they’re currently working on. I’d like to be able to let our accounts person know at the end of each month what projects are ongoing, and who to expect invoices from, with a summary something like this:

PROJECT 1
Freelancer 1, Freelancer 13, Freelancer 34 etc.

PROJECT 2
Freelancer 12, Freelancer 1, Freelancer 100 etc.

I’ve currently got a column for my project name, a column containing all the freelancers working on that project, and a column with an ‘Ongoing?’ checkbox to track whether the project in question is still ongoing (along with a bunch of other columns containing information that isn’t relevant to this particular question).

Is there an easy way of generating a list of all project names with the freelancers listed under them like the above for only the projects for which the ‘Ongoing?’ checkbox is checked?

If I could automate an email at the end of each month with this information in, I’d be extra happy. I guess I might have to do clever things with Zapier for that though. If so, I’m happy to try it, but I’m a novice at that too, so be gentle.

Any and all help gratefully received, and as I say, forgive me if the answer is staring me in the face.

Thanks,

Oli

[Kendall_Shortt]

I believe you can create a view for your accounts person and filter out everything but the ongoing projects.

Then categorize the view by freelancer and any additional data they might need. This way you don’t have to worry about an email and they can check it at their leisure.

You could also do a Rollup formula to have each project attached to a specific freelancer and send out an email for each freelancer’s projects.

Hope this helps.

[Oli_Chance]

Kendall_Shortt:

You could also do a Rollup formula to have each project attached to a specific freelancer and send out an email for each freelancer’s projects.

Thanks so much for your reply. This seems like the right solution to me, but I’d like to have a summary of each freelancer attached to a specific project rather than each project attached to a specific freelancer, if that makes sense.

Based on looking at the various bits of available documentation, it seems I’d need to create a new ‘Ongoing Projects’ tab and use some kind of conditional rollup to list the Project name and Freelancer columns for all projects in the ‘Projects’ tab for which ‘Ongoing’ is checked. I’m assuming this summary table could then be emailed out (somehow) in its entirety on a monthly basis. However my attempts to do this are not going well. I seem to be having some kind of fundamental mental disconnect as far as the Rollup function goes. As such, maybe it’s best to list my questions:

1. Is spitting all this information out into a separate tab before it can be summarised and sent elsewhere, really my only option? All the Airtable documentation seems to be about how views mean you don’t have to have endless tabs etc. Is it possible to have an email summary go out of a specific view? That way I could just filter for ‘Ongoing’ and send out the summary, and all would be well.

2. I can’t create a rollup field without creating a link field, but I’m not sure I understand why. If it knows a rollup is a summary of information from another tab, what is this link field telling it? Couldn’t it just ask me to select the tab and the column I wanted to look at in the dropdowns when I set up the rollup?

3. A rollup can’t display information from columns other than the rolled up column, right? So I’d need to create two separate rollups, one for project name, and one for freelancers? But how would it then know if the right project name was next to the right list of freelancers?

Sorry, as I’m sure you can see, I’m very, very confused. I really have tried to dig into the documentation, but either I’m missing something fundamental or it’s assuming some piece of knowledge I don’t have, or a magical combination of both.

Any and all help appreciated,

Oli

[Justin_Barrett]

Oli_Chance:

I can’t create a rollup field without creating a link field, but I’m not sure I understand why. If it knows a rollup is a summary of information from another tab, what is this link field telling it?

The link field defines which records are available to be rolled up. The Rollup field doesn’t grab data from every record in the specified field in the target table. It only grabs data from those records that are linked.

For example, say you’ve got a [Projects] table and a [Freelancers] table, and you’ve linked Bob, Alice, and Jeremy in [Freelancers] to Project 1 in [Projects]. In the Project 1 record, you’ll see a link field that has pointers to the records for Bob, Alice, and Jeremy. Using a Rollup field in [Projects], you would target this link field, which would then let you roll up any other field from [Freelancers] for those three people.

In your situation, I’m not sure that a rollup is going to address your problem. Views are a more likely solution, but as I only have the free Airtable account, I don’t have access to blocks, so I can’t offer more guidance on how to build an email based on a view.

[Mac]

Okay, I think I figured out how to get your report, if I understand your requirements correctly.

Check this base out. The final report summary is under the “ongoing” tab.

Airtable

Monthly Report - Airtable

Explore the "Monthly Report" base on Airtable.

If that works, then I can show you how to create the monthly report in Zapier.

The formatting is a little ugly, but we should be able to use Zapier to get it into the format you want.

[Oli_Chance]

Hi Mac,

Thanks for this, that’s a really useful illustration of how this could be done.

Would be great to get some info on how to make the monthly report thing work on Zapier - I discussed this with their customer service people and they didn’t seem to think regularly generated reports of this kind would be a simple thing to do, so any input on that would be very much appreciated.

Cheers!

Oli

[JonathanBowen]

Hi @Oli_Chance - just picking out some of your questions here:

• Ongoing projects tab - I wouldn’t recommend this. I think you should have a table for “projects” and within that have a status field - ongoing, not started, completed and so on. Fundamentally, there’s no difference between an ongoing project and a completed project (other that its status, of course), so I would recommend keeping these in one table. Generally much better for reporting purposes later on.
• your question 1 - yes, as above, don’t have endless tabs, filter by status “ongoing”
• your question 2 - you can only rollup fields in linked tables. But, make the link field, do then roll-up, then hide the link field if you don’t want this in your view.
• your question 3 - not sure I understand this.

I’m not sure if this will be helpful to you as I don’t know your specific situation, but here’s a quick mock-up of how I would approach this base design:

Airtable

Projects-Freelancers - Airtable

Explore the "Projects-Freelancers" base on Airtable.

(I’ve added the notion of hours worked, but this may be irrelevant to you - just for illustration really).

I like to separate all of the key things I’m tracking - people, projects, assignment etc. You could, of course, have assignments on the projects table directly or projects on the people table directly, but I find I have more flexibility if I create a third table which links these two things.

In the people table, you are just managing name
In the projects table, you are just managing name and status
In the assignments table, you are just managing project and person
In the hours table, you are choosing project assignment, then adding month, year and hours.

From here you can create multiple views for different data needs, e.g.:

• in the project table, you can filter by status and see the ongoing projects and who is assigned

• In the people table, you can see who is working on which projects

• across people, projects and assignments, you can see a roll-up of the hours worked to get multiple different views on this.

Anyway, hope this is useful to you.

JB

[Mac]

Nice additions @JonathanBowen!

@Oli_Chance, no matter how you design your base, you will still need a way to roll-up the freelancers invloved in all of your active projects so that Zapier can send out your monthly email. Lots of ways to skin that cat, some combination of the two suggested bases may be required.

On to the monthly report!

Here is a basic Zapier workflow that results in a monthly email that shows ongoing projects and the freelancers associated with those projects.

Step 1

Step 2

Step 3

In between Step 2 and 3 you could use the formatting zap to replace the comma separating the two projects with a return or any other information that you want included in the email. I use a similar method to automate my weekly reports.

Best of luck to you!

[Oli_Chance]

Wow, this is amazing. Thanks so much for the super-detailed breakdown and the example table. Very happy to learn I don’t need to create a whole separate tab just for ongoing projects. I was hoping this was me not understanding things correctly.
This is all helping me get much closer to what I need. Thanks so much, all. Great to have some community feedback that’s so positive.

[Oli_Chance]

Hi Mac,

This is great, thanks. I tried something similar myself before, but I get a bit lost with some of the information Zapier asks for, then lose confidence that the thing I’m creating is capable of doing what I need. I’ll try this out and report back with my progress. Fingers crossed…