Creating weekly records and totals from other tables

[WaitroseCarpark]

Hi Airtablers

I am trying to create/automate a weekly report (or record) that contains summaries from other tables.

So for example, I have a ‘Daily Report’ table, that contains a column with a %. I’d like to create a record in my ‘Weekly Report’ table, on a Saturday, that automatically sums all the columns from the previous 5 days.

It’s not a problem to do this on the outset, but what is a problem is having the Weekly table use a fixed date to sum the past 5 days. I can’t figure out how to specify that the sum should only be from say Monday 1st to Friday 5th in the weekly report, and the week after that should automatically sum from say Monday 8th to Friday 15th, if that makes sense?

So like this:

• Week 1 report created automatically on 6th that contains sum of Daily Reports from 1st - 5th
• Week 2 report created automatically on 13th that contains sum of Daily Reports from 8th - 12th
• Week 3 report created automatically on 20th that contains sum of Daily Reports from 15th - 19th
• …etc

[KVachon]

You could create linked records between your Weekly Report table and Daily Report table, then use a rollup column in your Weekly Report table to look at the number column.

This is what you’d need:
Daily Report Table

• Number column
• If you have multiple columns that you need to bring over to the weekly report, create a formula field in Daily Report that uses the SUM() function to add each column there. It’ll make it easier to bring the total over to the Weekly Report table.
• Use linked record column (once you create the field below) to assign the week you want each day to roll into

Weekly Report Table

• Week Start
• Week Stop
• Linked to other record > Select your Daily Report table > Choose allow linking to multiple records
• Rollup column > choose your calculated column of the total from the Daily Report table