How can I create a summary table of thousands of records, my method seems clunky

[Brady_Meisenhel]

I have a table with 5K+ rows of data as such:

table1
Date - Year(formula of date) - Month(formula of date) - DataPoint
1/1/2020   2020                                         01                          546
1/2/2020   2020                                        02                           45
...

I'm pulling this data occasionally to an external dashboard with paginated API calls and would rather not have that latency.....which is fine, as I don't need all 5K+ records....I really just need the grouped and summed yearly and monthly totals such as:

Year  -  Month  - DataPoint (a rollup sum)
2020          1                1,400
2020          2                1,122
2020          3                1,300
...

So, I figured I should try to just create a new table summarizing the data and do API calls to that table.

I made a table2. I made a new column on the far right of table1 "yearLink". This column was setup to be a link to table2. I then pasted the "Year" column date into "yearLink" and table2 was populated with unique year values (2020, 2021, 2022, 2023, 2024). Then I can put a rollup field in table2 and sum the dataPoint values.

Issues: It doesn't give me months..though I'm sure I could work that in somehow. Bigger issue, when I add new records, how do I keep the link populating? And, obviously, once I hit 2025 I'll need that year 'migrated over' to table2 from table1.

But mostly, this all feels like it must be unneccessarily complex. I know a bit of javascript, so I can tinker into scripting if that's the best path..but seems there should also be a much more elegant solution through the UI.

I appreciate any thoughts and the time!

Best,

Brady

[TheTimeSavingCo]

Yeah, this is my go to as well.  I make an automation to paste the value from a formatted formula field (e.g. outputting "Feb 2025") into the linked field to populate the link, ensuring that the summary table's populated

I'm not sure I follow about needing to migrate the year from table2 to table1 though.  table2 is the summary table while table1 is the data table, right?

[Brady_Meisenhel]

I just put in an automation to populate the linked field so it will pull in each new entry to my summary table2 and add new years as time goes on. It seems to work. Still feels very clunky, but since I'm not relying on interfaces or record grouping cause I want to pull the data out by API call, maybe it has to be clunky.

Thanks, @adam.   I might not need to!  But table 1 has 5K records and I just want sums by year by API call (I'll recheck the API can't help me limit this with grouping). So, I need a table with just "5" summary rows of data for 2019-2024 (or, eventually...a row for each month - year combination). But again, I might be totally missing something.

[Brady_Meisenhel]

This seems a relavant previous discussion from a few years ago - https://community.airtable.com/t5/development-apis/how-can-i-get-a-grouped-record-with-api/td-p/1201... -  idk if there are better solutions now then I have laid out above. 

[Brady_Meisenhel]

Well, despite hunting a lot before I posted, I'm finding many answer after I posted. This seems a relevant answer with an example base (and basically what I am already doing). https://community.airtable.com/t5/base-design/totaling-multiple-fields-based-on-month/td-p/146269

So, I'll assume that is the best working solution (and not all that clunky once the automation(s) are set)