Can't filter payments by academic quartals

[Davor_Budimir1]

Hi Airtable community!

I have an issue and I desperately need your help :tired_face:

To better explain my issue I created demo database that you can find here.

This is a basic school management database with table student, academic quartal and payments.

• One student can attend many quartals but can also attend one quartal
• One student can have multiple payments through multiple quartals

The issue is:

• I can’t find a way to show all payments of students that payed something in specific academic quartal
• This is probably because of the table payments and primary key that’s “amount” with amount in currency

I tried to solve it like this:

• I added “total sum” in a table “Students” that shows combined payments through all academic quartals but this won’t solve anything because I would need to show payments only for specific quartal (e.g. to show payments for 2018 Q1 or 2017 Q4 etc.)
• I used filtering in “payments” table but it wasn’t helpful. Filter that I used is: WHERE academic quartal CONTAINS 2018-Q1 AND Total sum < $100.
• This filter isn’t very helpful because it compared “total sum” instead of SUM of current quartal. But keep in mind that one student can pay 4 times $50 and it can pay one time $200. But I can’t find a way to combine all payments in one quartal of one student.

Basically, what I’m trying to do is create a view to show all students that payed only half amount for one academic quartal so I can remind them to pay another part of scholarship.

I hope that this make sense? Can someone tell me what I’m doing wrong?

[Danny]

This isn’t a solution, but perhaps it will help. In the payments table, you could group by student and then group by quartal. Then, you’ll see the sum for each quartal for each student, so at least you can more easily scan through the records to determine who hasn’t paid.

Unfortunately, you can’t filter on that aggregate value, but I’ll let you know if I’m able to come up with a better solution.

[Danny]

I have a real solution for you :grinning_face_with_big_eyes:

They’ve actually already covered it in this help article, but I made a sample base specific to your situation so you can see how it would work.

Once you start adding If columns to the payments table for each quarter it can start to look messy, but remember you can always hide those columns. You’ll see what I mean when you check out the base.

[Davor_Budimir1]

Hi Danny,

Sorry for the late response. I don’t know where to start. That’s the most genius solution I saw. I totally forgot about conditional rollups and when I saw your example I almost died of happiness :blush: You solved my issue and I’m super grateful to you :blush:

I’ll share a bit advanced DB in a few days and hopefully it will be of some help to someone. Thanks one more time Danny, you’re the man! :relaxed: :hugs: