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

[Oli_Chance]

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

[Mac]

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

[Oli_Chance]

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…

[Oli_Chance]

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

[Mac]

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