Help with a formulas

I am new to Airtable. I am building a tool to collect behavior incidents in the school where I am Principal. After testing multiple tool, I came to the conclusion that I need to create my own application and Airtable expends the possibilities.

Here is what I am trying to achieve:

  1. In a first tab called INCIDENT I can create incident record with one field being the TYPE of incident. This tab is using variables located in an other tab named TYPES. On this tab TYPES, I have two information: the name of the type (bullying, cheating, etc…) and the point associated to the incident (a number value).
  2. When a type or multiple types of incidents are selected, it shows the point on the next column. If two types are selected it will show point 1, point 2.
  3. On the next column I have created a formula to add the points.
  4. In one of the column I have a drop down menu which can select the name of the students responsible for the observed misbehavior. This field is linked to an other tab named STUDENTS. I have also a columnn. to select the names of the student(s) victim and one for the student(s) witness
  5. In the STUDENT tab, I have the student names, their ID, their grade etc… I have also three other column that collect the CASE number from the INCIDENT tab if the students is either responsible (column X is the student tab), is the victim (column x+1) or is the witness (column x+2). Those fields are automatically populated based on the INCIDENTS created in the INCIDENT tabs.
  6. I would like to create now on the STUDENT TAB in an other column, a formula which will do the SUM of all the POINTS a student would have accumulated in all the different incidents. For example if student X is responsible of case 1 for 30 points, case 2 for 10 points and case 21 for 50 points, in the STUDENT tab I would like to have the total showing 90.

how can I do that ?

You need to bring all the numbers you want to add together into the same table using lookup fields, which look up those numbers from the records you’ve linked to in the other tables. Then use SUM({Number 1}, {Number2}, {Number 3}) as your formula. Could you share some screenshots/a blank template?

Here are some screenshots, I am new at Airtable so I am not sure how to do what you are describing. Thanks you so much for your help. The possibility are endless with this database system.

This tab is the incident report tab where we select the incident type and it adds the “points”

In this type we are listing all types of incidents including the weight they have (point)

In this one we have all the information about our students which is used when you work on reporting an incident, I would like after the list of the incident on the right to add the total point they have.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.