Filtering records from multiple tables into one report?

While you might feel that separating your orders by month would make things easier, my gut says that it’s actually one of the key things preventing you from making this aggregation a simple process. When I was using spreadsheets to track certain things with my work, I had a tab for each month because I thought it was easier to organize that way. Once I moved everything to Airtable, I found that it’s much easier to have everything in a single table. There are ways to organize by month within that one table, but what I’ve found so far is that even though I added that month-grouping system, I haven’t used it once. :winking_face:

So while this may not be what you want to hear, the first thing I would strongly recommend is collect all orders into a single table. Once that’s done, you’ll probably find the creation of your report to be a lot easier than you initially thought.

@Justin_Barrett is right - logical filtering is always recommended over physical filtering.

While I appreciate the input so far, the suggestion to have one table for our data isn’t feasible for a number of reasons, the least of which is each table contains about 3500 records and our workflow for ordering would take longer if this were all in one table.

My original question remains. Does anyone know how to pull data from multiple tables into a single report?

Yes. If you’re doing this all within Airtable, you need to get the relevant data from each of your twelve month tables into a single report/summary table via some combination of links, rollups and lookups. From there you can use various blocks (charts, page designer, etc.) to display the collected data.

One potential problem I see is with this:

That says to me that the months you’re pulling from won’t always be consistent, in which case you may find it more difficult to build a single report setup that will work the same for each week. With your orders split across twelve different tables, you’ll have twelve incoming link fields in your report/summary table, and very likely one or more associated lookup/rollup fields for each incoming link field. If you were always reporting on the same time frame each week, building a report (for example, using Page Designer) to pull from the same five months would be fairly straightforward. However, with each week’s report pulling from an ever-changing collection of months, you’ll likely have to manually tweak the report each week to only show those relevant months.

You might be better off having someone design a custom API-based solution. The code behind such a solution would have a far easier time pulling only the needed data each week, which means less work for you to build each week’s report.

Thanks so much for your reply. I played around with the API for a bit before posting, but that’s definitely beyond my knowledge. The input changes each week, with a list of codes ranging between 150-200 entries. Again, I could go in and filter via “date received,” but then I’m still manually entering data and I was hoping there was an easier solution.

I’ll investigate the custom solution if I can find someone. Thanks.

I meant to touch on this in my last reply, but didn’t quite know how to fit it in. You mentioned in your original post:

Can you provide more detail about this CSV file, the data it contains, and how this connects to the reporting process? Perhaps something in there can be used.

Yes. I know how. The API.

I trust that this “list” is the CSV you speak of. I also sense that it contains items with “codes” like product codes or an ID of sorts that you’d use to perform lookups into the various monthly tables.

I want to restate your quote to see if we can put a finer point on the requirement. Correct my compass heading if I’m getting this wrong.

Each week I receive a list that may include items from 5 or more different tables of inventory items that we’ll be receiving from suppliers. This list is notification concerning items that impact the monthly inventory lists.

This is where the thread confuses me -

Do you need to take the 150 to 200 entries contained in the weekly CSV file and distribute them across the various monthly tables? This seems to be exactly the opposite of the title of the thread.

Or…

Do you need to simply pull a single summary report (perhaps into a new table) from the monthly tables where those items match the items in the weekly CSV file dump?

I suspect it’s the latter but I want to be sure I understand the requirements clearly.

If it is the latter, I must [therefore] assume there’s a matching lookup code in the monthly tables that corresponds to the codes in the weekly CSV list, right? If so, let’s call this the product “key”.

Last question (for now). :winking_face:

You mention manual data input. Are you referring to using the CSV data to update the monthly tables in some way? Please describe.

Regardless of the process details, it’s abundantly clear that nuances of your business and information flow require a process that is not as simple as we all wished it were. Furthermore, there appear to be business logic aspects that need to be applied in a contingent or dependent fashion as the data moves through your workflow. Simply put - the system has to exhibit some intelligence and decision-making as the data is processed for reporting purposes.

This suggests (to me) that you need to codify your process externally to Airtable itself. To do this requires some code - custom reporting software - that is designed to ingest all the information, organize it for the report, and then deliver either the data in a report-ready format (perhaps back into Airtable), or in the report format itself (perhaps a PDF) that is dispatched to workers who need the report.

The reason such custom reporting software must be built external to Airtable is because Airtable has no integral scripting feature. Ergo, we must resort to an inexpensive (or free) scripting environment such as Google Apps Script.

Does your company use G-Suite by chance?

I would love to get a glimpse of one of the monthly tables and a sample CSV file. Also, we should understand how the weekly CSV is compiled and conveyed to you.

The key to getting you to a reliable and fully automated solution hinges on good requirements and a thorough awareness of the adjacent processes.