Summary Table within Single Base

[The_Redirection]

I have a Base with a Table for each week. Every week, I add a new Table with a new set of data for that week. I need a Summary Table that pulls from the same column in each of the weekly Tables. Any suggestions? The less I have to configure each week, the better. Seems like a simple function!?

Thanks!

[acdye]

Have you considered using a single table with multiple views rather than creating a new table for each week? That would be the most straightforward route in my opinion. Personally, I would group the data by week, and then you would be able to collapse each week as needed. It would also allow you to easily summarize the data since it would all be in one table. You could also use Zapier to add each new week. I’d be glad to provide more of an explanation if this sounds like something promising to you.

[The_Redirection]

@acdye, thanks for the post. I’m not sure that will do what I’m needing. Each week has a set of data from 7 columns - one of them adding sum of 4 columns, and the 7th column generating a percentage of that sum to the starting number in column 1. In total, there are 75 records that we input this data into every week. Right now, it’s easiest for me to duplicate the Table, rename it the new “week”, and enter in the numbers. Further thoughts? I can send you a screen shot if that helps.

[acdye]

I think once you created the perfect “weekly view” view you could easily duplicate the view. As for automating the creation of week, I believe I’ve seen Gareth Pronovost on Youtube post a video demonstration how to automate it with Zapier. I don’t have time right this second, but I’ll try to dig it up later today. If you want to send a screenshot, it may help me visualize how you have it set up/what you’re wanting to achieve. Sometimes when I start building a base I plan to have multiple tables, but end up merging it into one table and using views along with groups and filters to get what I need. Especially when formulas come in to play.

One more thing I seem to have overlooked… for the summary table, I would create a column to “Link to Record” where you would like to the week you were wanting to reference. This “Link to Record” would then allow you to use Rollups and Count as needed.

Learning how to do everything in the last paragraph is what I struggled with the most when I was introduced to Airtable. Gareth has videos for all of these things, and I highly recommend you check them out. I still watch his videos weekly for inspiration.

Maybe someone else can jump in and add some more ideas/suggestions for you.

[JonathanBowen]

hi @The_Redirections_Gro - as per @acdye, I would generally suggest that a single table is the best design when you’re dealing with repeated instances of the same thing, i.e. weeks in the this case. However, if you could share a screenshot, then we could see your set-up, data, structure a bit more and could comment from there.

JB

[The_Redirection]

[The_Redirection]

@JonathanBowen & @acdye - attached is a screenshot of the columns in discussion. Again, there are 75 of these records (rows) that I fill out every week. I’ve attempted the Link to Record to do a Rollup, but it would require doing that each week in order to summarize every column, correct? Automated would be amazing…

In review, the #Place is a benchmark, then I sum up the #S, L, R, A columns into the Total Loss, which then gives me a Loss % - I’m looking to have a Summary Table (or some other solution) to show averages of this Loss % column and a sum of the Total Loss column over multiple weeks.

Help if you can - thank you!

[Kamille_Parks]

I agree with @acdye that Zapier or Integromat would be your best bet. I prefer Integromat so that’s what I used to test the following solution.

Table 1 has 75 rows each named for the item you collect data on each week, and one formula filled with calculate today’s week number and year. The calculated field can be hidden from view.

Table 2 will hold all data for all weeks. This second table includes a autonumber as the primary field, the seven columns to discuss for a week, one link column pointing at Table 1, one field for the year and number (i.e. 2019 29).

I made an Integromat scenario which:

1. Looks for records in Table 1 to copy,
2. Passes all 75 records through an Iterator function
3. Generates a blank set of 75 records into Table 2, each with the {Week} column filled in with today’s week number and year using the value calculated in Table 1.

You can set that scenario to run once a week on Saturdays (or whenever you typically log your data) and Integromat and Airtable will do all the copying for you. This will solve the issue of having way too many tables, will be a lot easier to manage, and you’ll be able to group your 75 items in Table 2 by the week so you can see a summary of them all in one table.

[Kamille_Parks · Accepted solution]

Here’s a test base I made with the structure suggested by @acdye, @JonathanBowen, and myself.

Airtable

The_Redirections_Gro public - Airtable

Explore the " The_Redirections_Gro public" base on Airtable.

And a screencap of how the Integromat scenario works:

Done this way, by the time you open Airtable you should have a fresh set of 75 blank records with the week filled in and ready for you to put in your data. This is as automatic as it can be, I think.