Lack of Conditional Sum Functionality in Airtable - And Transitivity in realtions!

[dondon]

Hi Airtable Community,

I’m currently managing a database where payments are recorded in a “Payments” table, attendance is tracked in a “Attendance” table, and student information is kept in a “Students” table, all to maintain good data normalization. However, I’ve run into a significant issue when trying to create a “Debtors” table.

Creating a unique list of student-class combinations from the “Attendance” table was not as straightforward as I had hoped. It required exporting the data to Google Sheets to generate that unique list. Additionally, I was able to automatically link each payment to the attendance records by using an automation based on a shared key (student-class-period) between the payment records and the attendance records. These challenges were overcome, but they clearly could have been handled more efficiently if Airtable allowed data from payments to be transferred indirectly. However, since relationships in Airtable are not transitive, if a payment is linked to “Student1,” and then that student is entered in the “Attendance” table, the payment cannot be automatically linked. One has to manually create the linked record or use an automation.

The “Debtors” table is intended to show unique instances of student-class combinations within a given period (e.g., September) where the payment status is marked as “Pending”. The challenge arises when trying to update this table because each record needs to be manually linked to the corresponding transaction in order to update the payment status. This is problematic because the list of debtors is not static; it needs to update dynamically as payments are made, reflecting the change in the payment status.

The current method of manually linking each row in the “Debtors” table with the relevant transaction to update the payment status is not practical. Ideally, there would be a formula that could sum the amounts of transactions associated with a specific “Student”, “Class”, and “Period”. This would eliminate the need for manual linking and would greatly simplify the process.

A significant part of this problem stems from the fact that Airtable does not support transitive relationships. Complex relationships would be much better managed if Airtable accepted transitivity. For example, if I could automatically populate the “Linked Transactions” table without requiring automations, based solely on the fact that those are the only transactions associated with both that student and that class, it would greatly simplify managing these relationships.

In fact, all the issues I’ve encountered seem to stem from this lack of transitivity in relationships. When Table A is linked to Table B, and Table B is linked to Table C, there is no way for Table C to “automatically” relate to Table A, even though the information is entirely there. It’s just that Airtable doesn’t currently allow this relationship to be expressed. I’m quite surprised not to see more people expressing this problem, as it seems fundamental to handling complex data relationships effectively.

I noticed that Airtable now (from march 2024) allows setting dynamic conditions for linking records (e.g., only linking records where one field matches another, and so on). This is a fantastic improvement. However, given that Airtable already has the payment data associated with each student and class, it should be able to calculate the sum without requiring the user to manually link each payment to the new record in the “Debtors” table!

Unfortunately, Airtable does not seem to have a built-in function that allows for conditional summing based on multiple criteria (similar to SUMIFS in Google Sheets). This limitation is not only frustrating but also adds unnecessary complexity to what should be a straightforward task.

A native function that allows for conditional summing directly within Airtable would be incredibly useful and would save a lot of time, as well as reduce the need for excessive manual work or automations. Has anyone else faced this issue? Are there any workarounds that don’t involve manually linking records or creating complex scripts?

Thank you in advance for any insights or suggestions!

[Mike_AutomaticN]

Hey @dondon!

We've all been there at some point in time! However, each issue described above can be easily solved.
For example: 
- Debtors: Should probably be a view obtained from filtering a Rollup field in the Students table. 
- Transitive Relationships: Almost always, this can be achieved by rolling-up/looking-up fields throughout different tables, until you get the data wherever you need it. Of course, database architecture is really important to this effect.
- 1:1 relationships between Students and Classes could be created automatically, without having to link each manually.
- Conditional summing is def possible. Try combining sum and if statements. 

Your post is pretty long, so addressing each item would be hard. However, I'd be happy to hop on a 15 minutes call to go through your questions. I have plenty of experience building bases for academic institutions 😁.

Mike, Consultant @ Automatic Nation

[dondon]

Hi Mike! I created a base to illustrate this better, and I still think Airtable CANNOT solve it:

https://airtable.com/appliDyQzWFVkwgh0/shrYSrWH9OMAPzXFK

I fully understand everything you're saying. If, in this base, you can get Airtable to show the sum of payments made by the STUDENT, in the CLASS, in the PERIOD, then the issue is resolved. Otherwise, it is not.

Rollups with filters don't work for this because the same student can be in several classes and in several periods. I have 12 periods (months of the year) and approximately 60 classes. That makes 720 combinations. I can’t create hundreds of rollup fields (which Airtable doesn’t even support). What I need is to sum the paid amounts with the condition that it's the specific CLASS, PERIOD, and STUDENT. Airtable cannot do this. The only way it can is if I manually establish the linked record (but the information is already in the base). With Google Sheets, this is easily done using a simple SUMIFS.

**- Debtors:** I understand your point that it should be a rollup field in the students' table. On the surface, yes. But what if the student takes two classes? How do I differentiate that? There are 60 existing classes! I’m not going to create 60 fields, one for each class filter. The student table doesn’t have the granularity to account for things like whether they paid for one class but not another, or if they paid for September but not for August. It lacks the period and class data granularity.

**- Transitive relationships:** I understand that with lookups and rollups, you can achieve “something similar,” but I cannot sum in table C with conditions from fields in table A through a lookup in table B. The math of the relationships shows that many combinations are lost because it’s not truly transitive.

**- Conditional sums:** It’s not possible in the debtors table to create a formula that looks for amounts paid in the payments table by the STUDENT, the CLASS, and the PERIOD corresponding to each row in the debtors table. If it’s possible, we should be able to do it in the example base I shared.

At this point, I care more about knowing the truth about what Airtable can or cannot do.

Airtable allows filtering linked records by dynamic conditions when FIELD1 in TABLE1 matches FIELD2 in TABLE2 and allows adding multiple dynamic conditions. A sum based on these dynamic conditions would be perfect. But apparently, it doesn't exist, and there is no simple workaround.

Thank you for your response! I truly hope you can prove me wrong so I can solve the problem. But even after reading your comment, I don't see it that way. I hope you can show me that I’m mistaken.