Step 1: Prepare your Weekly Status Updates table

Before creating rollups, make sure:

• Each update is linked to a Project
• The Week ending field is filled in
• You are consistently using the same weekday for week ending

This consistency is essential for accurate rollups.

Step 2: Roll up the latest weekly update date

We start by identifying the most recent update for each project. In the Projects table:

• Create a new field
• Field name: Last weekly update
• Field type: Rollup
• Link field: Weekly Status Updates
• Roll up field: Week ending date
• Aggregation formula: MAX(values). This field shows the most recent week for which a status update exists. If this field is empty, it means that the project has never had a weekly update.

Step 3: Roll up the latest status summary

Next, surface the most recent written update. In the Projects table:

• Create another field
• Field name: Latest status summary
• Field type: Rollup
• Link field: Weekly Status Updates
• Roll up field: Status summary
• Aggregation formula: ARRAYJOIN(values,”\n\n”)
• Ensure your Weekly Status Updates table is sorted by Week ending (newest first). The first paragraph in this rollup will always be the latest update.

Step 4: Roll up the latest RAG status

Now bring the reported health onto the Project. In the Projects table:

This allows you to see the most recent health indicator without opening the status table.

Create a third rollup field

• Field name: Latest reported RAG
• Field type: Rollup
• Link field: Weekly Status Updates
• Roll up field: Reported health (RAG)
• Aggregation formula: ARRAYJOIN(values,”, “)

Step 5: Flag overdue weekly updates

A missing update is itself a risk. We now add a formula to highlight projects that have not been updated recently. In the Projects table:

• Create a new field
• Field name: Weekly update overdue
• Field type: Formula
• Formula:
• IF(
  OR(
  {Last weekly update}=””,
  DATETIME_DIFF(TODAY(),{Last weekly update},”days”)>7
  ),
  “Overdue”,
  “Up to date”
  )
• This code flags projects with no updates at all and flags projects where more than 7 days have passed since the last update.
• You can later style or filter this field to draw attention to it.

Step 6: Create a portfolio-level weekly status view

Now that each Project carries its latest status, you can create a single portfolio view. In the Projects table:

• Create a new Grid view
• View name Latest Weekly Status – Portfolio
• Filters Project status is Active
• Fields to show:
• Project
• Latest reported RAG
• Weekly update overdue
• Last weekly update
• Latest status summary
• Sorting
• Weekly update overdue (Overdue first)
• Latest reported RAG (Red, then Amber, then Green)
• This becomes your weekly management dashboard.