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

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.

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

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.

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.

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.

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

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:

(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

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!

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.

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…

Hi Mac,

Okay, while I think I understand the Zapier thing in principle, and the steps seem clear enough, I think I’m missing something at the Airtable setup stage (ie Step 2 in your workflow) or the outgoing email stage (Step 3).

For the former, I’ve asked it to look at my projects tab, then specifically at the ‘Ongoing?’ checkbox field I’ve set up as follows:

As far as I understand it, this should search my projects table for everything under the ‘Ongoing?’ field for which the value is 1 (ie checked). However, at the email summary stage, this seems to be only pulling one specific entry for which this is true (despite it being true of multiple rows in the table). I thought this might be to do with me having told it to only look at this for a specific row, but the row it’s pulling is the 22nd one down, and I can’t see any reason for it to only be summarising data from that one row.
I’ve tried it with a formula in the ‘Search Formula’ box instead as follows: IF({Ongoing?}=1, {Ongoing?}, 0), but this still seems to be pulling info from only the one row.

At the email stage, I’m just a bit puzzled as to how the whole thing works. I can select for it to print the content of the project name field and the ‘Ongoing?’ field, but again, it seems to only be doing this for a specific project. I can’t seem to get a nice summarised field like you have. The setup looks as follows currently:

I’ll be the first to admit to fundamental ignorance of some kind being the cause here, but if you have any insights, I’d be hugely grateful.

Thanks,

Oli

@Oli_Chance Zapier will only return one record per search.

Which is why the base I created has a single “ongoing” tab that rolls up all the information you need in a report.

This gives you a single record that Zapier can pull it’s reporting information from.

Without that, my sugested reporting method won’t work. You’ll need to explore alternative means.

Ah, that’s interesting. I thought it would return everything in the specified table under the specified field that met the criteria. Would I maybe be able to use the ‘Limit to View’ field instead, I wonder? Let me give it a try…

Nope. Same issue, sadly. Okay, looks like going back to try and figure out what it is about rollups I don’t understand.

I think I can help in a more direct way. Sent you a message.