I have a base with three tables. Each table is a log of different types of incidents that occur at our company. All tables are indexed using the date time the incident occurred, and also includes other accompanying data like staff involved, etc. The accompanying data for the three different incident types is different, otherwise I would put them in a single table.
What I would like to do is generate a chart that shows how many of each incident occurred each month. I have found out how to generate this chart for one of the incident types using a block, but the block only allows me to pull data from a single table.
I’ve tried creating a fourth table to capture all of the summarized data and then build a chart block there, but I’m stuck trying to find the most efficient way to link all of the data. If I create year and month columns in my summary table, is there a way to automatically link to all of the records in other tables with matching year and month, or is linking in Airtable always a manual selection process? We have a lot of incidents and linking them all manually would be both tedious and error prone.
If I can do that, the next question I’d ask is whether I could avoid manually entering the years and months in my summary table or if there is a way I could get Airtable to automatically populate year and month by the years and months in my data set (The equivalent of a GROUP BY in SQL).