Help with Scripting to Add Totals by Commercial and Year in Airtable

[Ramagu]

Hello Airtable community,

I am working on a project where I need to automate some financial reports in Airtable and I would like to ask for your help to solve a specific challenge related to conditional sums in several tables.

Context:
I have a base with several key tables:

[REPORTS] - Commercial: Records monthly orders per year (2022, 2023, 2024) assigned to different commercials.
SUMMARY: Contains a record for each month of the year and I need to add up the order totals for each commercial, divided by year.
Requirement:
I want the "SUMMARY" table to have three columns (2022, 2023, 2024), each showing the sum of sales for a selected salesperson in a single selection field in the same table. The challenge is to have these totals update dynamically based on the selected salesperson.

Attempts:
I have tried using rollup fields, but these do not allow me to filter dynamically based on another selection in the same "SUMMARY" table. I am considering using scripts to calculate these values conditionally and automatically, but need some guidance to set up the script properly.

Question:
Does anyone have experience creating scripts in Airtable to handle this type of conditional logic, could you share sample scripts or suggestions on how to approach this problem? Any help on how to structure this script or set up the automations to run it would be greatly appreciated.

Thanks in advance for your time and help!

[TheTimeSavingCo]

Hmm, and I assume you have a linked field between the two tables?

I think I'd just have a table where each record was a single salesperson and have those three columns (2022, 2023, 2024) as rollup fields with SUM(values), and to show only one salesperson at a time I would use filters

---
If you really want to just have a single record in "Summary" that dynamically updates, you could try setting it up with an automation:
1. Trigger the automation when the select field is updated
2. Find all the records from your data table that are linked to the selected salesperson
3. Link them all to the current record, clearing all the old links

The rollup fields would then update to show what you want

[Ramagu]

Hello,

Yes I currently have it like this, linked via a field.

The problem is that I have more than 10 vendors, so it would be many columns of years.

What you say about automation could be a very good idea, although it might take a long time, as there are thousands of orders.

Those would be the only possible options, wouldn't they?

Thanks

[TheTimeSavingCo]

Hm, I don't understand why you'd have multiple columns of years.  Could you screenshots of some example data and ideally how you'd like it to be laid out? 

re: Those would be the only possible options, wouldn't they? 
I don't think I fully understand what you're trying to do and so can't say for certain I'm afraid