I am attempting to collect data from employees that tracks incident reports over time. This roughly works as follows:
Form1: "Report New Incident"
This form collects information about an incident such as location, date, classification, etc. and assigns it a new ID.
ID | Date | Location
A | 1/1/19 | Plant X
B | 1/15/19 | Plant Z
Form2: "Update Incident"
This form asks the user for an incident ID and then some additional information is added to the original incident (new information or updates to old information).
ID | Date | Status
A | 1/3/19 | In progress
A | 1/5/19 | Solved
The idea is, the employee who is updating on an incident does not need to reenter information (such as location, classification), but should provide some updated information (update time, current status). Having two tables ensures they contain unique information with the exception of ID. Unfortunately, I can’t join together based on ID with some sort of JOIN query. Using lookups partially solves this, but then it gets more complicated with the next thing.
Form3: "Customer Reports"
I have a 3rd table which stores dates and associated values for each date (for example, customer complaint numbers or other metrics).
Date | Value 1 | Value 2
12/1/18 | 5 | 1
1/2/19 | 5 | 2
1/4/19 | 6 | 1
2/1/19 | 1 | 1
Then, in some table (2 or otherwise) I need to sum values from the 3rd table based on the date and its relationship to the dates in Table 2. This would be something like SUMIF(VALUE1,DATE>TABLE1:DATE AND DATE <TABLE2:DATE)
ID | Date | Status | Sum Value 1 | Sum Value 2
A | 1/3/19 | In progress | 5 | 2
A | 1/5/19 | Solved | 11 | 3
This last part is important since I want to use blocks to track how incidents and our response to them (or lack of response) effects the severity of the fallout as a result of the incident. I can’t think of how to combine this information into one table since the data in table 2 (incident status updates) and table 3 (issue reports/metrics) are quite different data types and reported by different employees.
Any ideas on how to accomplish this or how to better format my data to meet my needs? It is unfortunate that AirTable doesn’t allow SQL queries to combine tables as I think it would solve a lot of these.
