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!