Building an automated weekly Executive Project Status Report to send as email

[kujain]

hi there!

A newcomer to Airtable and really excited to explore and see how I can build a robust system for my use cases. I manage a construction company and am trying to build a robust PM interface to handle the various activities that happen during pre-construction, in-construction and post-construction. There's a lot there but one important aspect is the idea of automating a status report to share with the top management.

Currently I build this manually in email and share with the team. This includes a genera summary of activities during the week, any red items (tasks that have been delayed) and then a list of open items and their last activity.

Assuming all these items are logged in a table with their activity logs in comments (unless there's a better way), is there some way to automate this report to be generated and emailed end of each week?

Thanks very much for your help and advice!

[TheTimeSavingCo]

Airtable has a handy guide for this use case that you can find here: https://support.airtable.com/docs/how-to-create-email-digests-with-automations

You mention that the activity is logged in comments though, which is a pretty difficult thing to reference in general.  May I know what kind of activities these are? Any chance they can be their own records or fields instead?

[kujain]

Thanks for the quick reply Adam!

I'll review the automation process and hopefully it will be straightforward.

Re comments, I too find that a clunky UX to store activity updates in. Especially since I can view them in a flattened format in any way (eg in tables). The ideas was to create a task eg.

"Finalize submittal for light fixtures" and assign date and assignee to it."

That task is a process and goes through a process of updates which we have been storing as comments as its progress eg.

5d ago: prepared a better lobby layout and indicated updated lighting selections and shared with EC.

4d ago: Sent final fixtures selection - waiting on cove lighting and garage exterior sconce from EC to finish this task.

2d ago: still waiting for updates from EC - to follow up

etc.

The above notes are stored as comments, and i would like at least the last update/comment to be inserted into the weekly report as well. 

Would there be a better way to log such activities for each task?

Thanks!

[TheTimeSavingCo]

Perhaps you could use a "Long Text" field instead, and key the notes into that field so that the latest note is always on top and separated by a line break?  The downside to this is that the user would have to key in the date manually though, hmm.

The simplest way would really be to just create a new table called "Activity Log" or some such and link it to your tasks, and then for every update the user would create a new record in the new table I think.  That way we could just make use of the created time of the record and the user can just key in the notes instead.  The main downside to this would be the record limits, but that can be somewhat easily dealt with by consolidating everything in the future if needed

If the new table option isn't feasible, and you also don't want your users to have to key in the current date, you could accomplish this with a text field and a checkbox field where the user would key in their update and then mark the checkbox.  Marking the checkbox would then trigger an automation which would:
1. Grab the text and put it into a long text field with that day's date
2. Clear the checkbox
3. Clear the text field

Slightly overengineered, but it'd work!

[kujain]

Thanks for the above advice...i attempted the 2nd option (building a separate activity log and using lookup to show the logs in the main action items table). This does work and I think has the potential to keep my data clean and organized.

But I have a few questions:

1. My activity log contains a lot of activities for multiple action items. I link them to the main action item through lookups which then it shown on the action items table. Currently it shows ALL the activity logs on that table. Is there a way to limit it to show only the latest one (there are condition but thats very limited)? And can I show multiple columns in the same cell (eg. data logged and notes from the log)? Idea would be to get the latest logs for each action item in the table without clicking and expanding to view more details:

2. Is there a way to leverage forms or an interface on the action items rows to create a new log from within the action items table itself? This would be a better flow since as we review the action items, we can review the log and add another where needed. In some ways, we will be recreating the comments feature but with more granular data management since how we could potentially show specific logs from the activity log using conditions (as long as there's a condition that allows us to filter by date or something)!!

thanks!

[TheTimeSavingCo]

re: Is there a way to limit it to show only the latest one (there are condition but thats very limited)? 

Hmm, I'd tackle this with the "Limit to the last" option, but I take it that doesn't work for you? 

---
re: And can I show multiple columns in the same cell

Yeap, in your activity log table create a formula field that consolidates the data from the fields you want, and then create a lookup to that new formula field in your main table

---
re: Is there a way to leverage forms or an interface on the action items rows to create a new log from within the action items table itself?

Yeap, the idea would be to have some sort of record picker element on your Interface where you select the record you're creating the activity log for.  Once that's available, you can create a Grid / List etc element to display the linked activity logs, and from there you'll be able to easily create new activity log records that are linked to the selected action item, does that make sense?