Using Airtable as a Financial Database

[Dennis_Yu]

Hey all your Airtable users,

I’m trying to use AirTable to do some of my accrual accounting for a small school, we used to do it via Google Sheets in a relatively manual process.

I want to recreate something like this, but using a webhook to receive information from stripe, and automatically filling up the amount received, split into the months according.

The problem lies therein.
The students pay in a lump sum of 8 lessons, and each lesson is, for example every Monday of the month.
The students can join at any point of the month.
So for example:
Student A goes to class on Mondays and joins us on 7 Nov 2022
So on 7 Nov 2022, Student A pays us let’s say $80
This $80 covers the dates
7 Nov
14 Nov
21 Nov
28 Nov
5 Dec
12 Dec
19 Dec
26 Dec

So on the accounting, it should show that Student A paid $40 in Nov and $40 in Dec

But let’s say Student B joins us in 31 Oct, and pays $80. This $80 should cover:
31 Oct
7 Nov
14 Nov
21 Nov
28 Nov
5 Dec
12 Dec
19 Dec

Meaning, Student B’s $80 should be accounted as $10 in Oct, $40 in Nov, and $30 in Dec

How should I work this out keeping in mind that the lessons are on a fixed day of the week.

[TheTimeSavingCo]

Hi Dennis, I think if I were you I’d:

1. Have a field that indicated the day of the week that student is coming for lessons and the start date
2. 8 formula fields, each representing the date of a single lesson, which would output the month that lesson had taken place in
3. 12 formula fields, each representing a single month of the year, which would output the sum collected per month based on the formula fields mentioned in the previous point

[Dennis_Yu]

Hey Adam,

Thanks for your response, I’m not sure I fully understand how what you mean.
Field 1: Student ID
Field 2: Start Date
Field 3: Formula of start date + 7days(?)
Field 4: Formula of start date + 14days(?)
.
.
.
Field 9: Formula of start date + 56days(?)

But I don’t understand what Fields 10 - 17 would be.

[TheTimeSavingCo · Accepted solution]

I’ve put something together here for you that might help!

To view the formulas, you can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.

(Also just realized I had a typo in my previous post heh, it’s 12 fields each representing a month, not 8 sigh)

[Dennis_Yu]

Hey Adam

I think this might work, thanks a lot!