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).
Linking tables is typically a manually process in Airtable, unless you have a special scenario like importing records into your database or converting a text field to a linked records field. (In both cases, a one-time-only linking will occur.)
I think your best bet would be to move all 3 incident types into the same table, and then use different “Views” for the different types of incidents. Each view would “filter” on a certain type of incident, and each view would only show you the fields that you need to see for that type of incident.
Otherwise, if that doesn’t work for you, you would need to turn to external websites & apps to collect & assemble your data.