Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Using Airtable as a Financial Database

Topic Labels: Base design
Solved
Jump to Solution
165 4
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Screen Shot 2022-11-14 at 11.19.34 AM

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.

1 Solution

Accepted Solutions

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

Screenshot 2022-11-17 at 3.15.34 PM

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)

See Solution in Thread

4 Replies 4

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

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.

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

Screenshot 2022-11-17 at 3.15.34 PM

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)

Hey Adam

I think this might work, thanks a lot!