Skip to main content

Hi,


I want to build an Incident Table that tracks incidents. The key fields include:



  1. “Site” - location of incident

  2. “Date” - date of incident

  3. “Category” - drop down field that allows the user to chose the type of incident i.e., Property Damage, Near Miss, Lost Time, First Aid, etc.


I have a second Person Hour table that includes key fields:



  1. “Site” - location of incident

  2. “Date” - date of incident

  3. Person Hours


I need to create a statistical table that pulls and summarizes incidents from the Incident Table by year and category i.e 2020 3 Near Misses, 4 First Aids, 0 Lost Time, 3 Property Damages.


Also pull the person hours from Person Hour table and record by year against the “Site”


I would then create a formula to create a frequency rate for each category of incident. Example (for near misses) (3*200000)/person hours.


I created this perfectly in Quickbase, but I cannot figure out how to do this in Airtable. Any thoughts?

Try doing the following:



[Incidents Table]



  • Add a Formula field called {Statistic Value} (or whatever) with this formula:


IF(AND({Date}, {Category}), YEAR({Date}) & " - " & {Category})


  • Add a Link to another record field called {Statistic Link} connecting to the [Statistics] table

  • Manually copy the value from the {Statistic Value} field into the {Statistic Link} field (I recommend setting up an Automation to do this for you for all future records).



[Statistics Table]



  • You will have one row per year per category (for all categories that had incidents)

  • Add a Count field that counts the number of linked records



[Person Hour Table]


I wasn’t exactly sure how statistics for these records would get folded in since it seemed they need to be by site but incidents needed to be by type. Best bet would be to follow the same instructions as the Incident table but connecting to a separate Statistics table. If you want a singular statistics table then the overall method would need to be adjusted.

Try doing the following:



[Incidents Table]



  • Add a Formula field called {Statistic Value} (or whatever) with this formula:


IF(AND({Date}, {Category}), YEAR({Date}) & " - " & {Category})


  • Add a Link to another record field called {Statistic Link} connecting to the [Statistics] table

  • Manually copy the value from the {Statistic Value} field into the {Statistic Link} field (I recommend setting up an Automation to do this for you for all future records).



[Statistics Table]



  • You will have one row per year per category (for all categories that had incidents)

  • Add a Count field that counts the number of linked records



[Person Hour Table]


I wasn’t exactly sure how statistics for these records would get folded in since it seemed they need to be by site but incidents needed to be by type. Best bet would be to follow the same instructions as the Incident table but connecting to a separate Statistics table. If you want a singular statistics table then the overall method would need to be adjusted.


Thank you, that worked well.

Reply


Terms & Conditions