Calculation based on monthly records from multiple tables... possible?

Yes you could indeed use the API to achieve what you are wanting.
You could use Zapier or any other similar third party to access your data via the API, do your computations and then create your reports using some other third party report generation service.

You could also access the data directly via the API as Airtable exposes the API via a REST interface.
Then you could code out a program to access your data( It could be using any language of your choice JAVA, PHP, Python, etc…though Airtable provides a Node.JS library)
Once your program has been able to access your data,do your computations and then create a PDF report out of it…(Probably using some open source library out there)

If in case there exists a relation between your 3 tables, you could link all of them up. And then with the help of lookup fields have all the data present on one table.
Compute all your summations etc and create a view on this table that consists of all the fields you would like to see, hide the rest.

You might want to also check out Airtable Blocks.(More specifically the Page Designer Block)

Thank you Andrew.
There is no hard relationship between the tables to exploit.

I tried using Zapier to pull out new records to Google sheets but the zap seems unreliable. I did not find any integrations within Zapier between Airtable and anything else that seems useful. Do you have any suggestions for third party report generation?

This doesn’t address your larger issue at all, but rather the side issue you have with Zapier…

I’ve found Airtable integration with Zapier works best when you have some manual confirmation of what you want zapped, and some visual feedback that the zap has worked. Checkbox fields work well for this, as binary datum - have a checkbox field called “To Sync” or somesuch and check it on a record when you want that record to trigger a zap. Have a view that filters to show only “To Sync = TRUE()” records. Zapier will use that view to know what to sync.

Then have Zapier uncheck that box on all records synced as the final step of its routine - this confirms that Zapier has done its job as it works to keep the “To Sync = TRUE()” view empty.

I don’t think you need middleware (you could use it, but it’s not necessary).

Link all the records from each of your three tables to a single record in a new table. (I posted a quick how-to last night in response to another request.) At that point, you can access any field from all three tables from your fourth table. Depending on what you do and do not wish to see, you might have to add some formula-driven filtration fields to your original tables so you can roll up subsets of data.

If you want to display or print the rolled up values, you can do so using ARRAYJOIN(values,'\n'). This will return every entry in the specified field in a single column with a carriage return and line break after each item.

When it comes time to write the code to handle end-of-month calculations, I urge you to look into using aggregation formulas. An amazingly powerful un[der]documented capability of Airtable is that one can craft full-blown formulas inside rollup field configurations rather than being limited to single functions. You lose the contextual help provided by the configuration editor, as it’s not yet been informed of this enhanced functionality; you also can access only a single field from the remote table, which must be referred to using the keyword values. (There are numerous examples of aggregation formulas in my demo tables and related posts. For example, this reply includes a stunningly ugly specimen containing 98 lines of code.)

Despite any difficulties introduced or exacerbated by the use of aggregation formulas, the potential gains in performance and elegance greatly outweigh any negatives. (Did I really write that?) Take Two: Sure, it’s a little harder, but it’s worth it, in terms of performance, legibility, and maintainability.

This is especially the case when rolling up large values. Using an aggregation function, you would ordinarily rollup a large value to the joining table — after which you would then perform a lookup or rollup to duplicate said value onto every record in the original tables. Finally, once that was done, you could create a formula field to make use of the aggregated value in a calculation.

With aggregation formulas, though, you still do the initial rollup of the large value to the joining table. After that, though, you roll up that value from the original tables — but you do so with a formula embedded in the configuration of the rollup field. This allows you to access the large value by reference, using that single instance in your calculation, rather than having to create a copy of the entire thing for each formula field. In one of my demo bases, changing from aggregation functions to formulas trimmed the equivalent of three copies of War and Peace from the size of the base’s footprint. That was with 1,000 records in the base; because of the exponential growth rate of the value in question, at 10,000 records a single copy of that field would be as long as a novel — making a copy of that base with a lookup field duplicating that value onto every record about the same size as the average U.S. public secondary school library. :winking_face:

Admittedly, a lot (all?) of this is non-intuitive and hard to decipher, even on days when my writing skills are firing on all cylinders, as they are not so doing, today. If you would like to share a copy of your base (either by posting or PM; prefer a read-only link to the entire base with copying enabled), I’ll try to rough in how this might work.

Have you looked at Ultradoc (http:/www.ultradox.com)? Ultradoc has all kinds of reporting, templating, forms and automation capabilities and an Airtable integration. We use it for invoicing and quoting.
-Donald