School payments - how to allocate payments into different "buckets"

[OG School]

HELP! I DO NOT KNOW HOW TO ALLOCATE PAYMENT PORTIONS TO DIFFERENT AREAS OF REVENUE STREAMS!

At our K-8 school, we've got an FMP DB that's been in production for a few years. It has always been a single table DB, and was used primarily as an online registration tool for parents to check over the info we have on file for them in our Student Information System (PowerSchool by Pearson). The reason I used FMP is because it was much easier to set up than to try to make PowerSchool do what we needed it to do.

Last year I was asked to track student fees in the DB. No biggie. About 1/2 way through the school year, I was asked to add a payment component. I see what a bad idea retrofitting a DB is, but my hands were kind of tied.

The DB now has a 2nd table for payments, related to the student table by student_number. Again, this wasn't hard. The parents register, they select their optional items (milk, band, PE uniforms). Their BALANCE is calculated in the student table:

STUDENTS TABLE

Student_Number (hook)

FeeSummary = OverdueFees + RegistrationFee + BandFee + MilkFee + PeUniformFee

When the parents make a payment, it gets recorded into the payment table

PAYMENT TABLE

Student_Number (hook)

Date

Amount

Back in the students table, the Balance is the FeeSummary - PaymentSummary. The payment summary is the sum of any payments that have been made.

STUDENTS TABLE

BALANCE = FeeSummary - PaymentSummary

So far this has all been easy. Now they've asked me to figure out how to allocate payments to go into different areas of revenue! I am at a loss! When a payment is recorded, they want to know how much of the payment is supposed to go to each area that they need to account for.

So let's say a payment of $100 has been made. When they get that payment, they now need to know that $25 of it goes into "overdue fees," $30 of it goes into BandFee, $7 to PEUniform fees, etc.

Since parents may make payments several times throughout the year until they reach a zero balance, the bosses have indicated that all of a payment should go into "overdue fees" until the overdue fee balance is zero, then money should go into RegistrationFees until that balance is zero, then any money left over should go into MilkFee until THAT balance is zero, etc.

I do not think retrofitting this DB is going to work out very well, so I may have to design one rom scratch. Can anyone clue me in on how I might accomplish this task? I'll try to give a synopsis again.

• parents register. They come up with an amount they owe based on overdue fees, registration fees and optional items

• parents make a payment

• the payment must "fill the buckets" of each area that we charge for until each bucket is full

• if the "buckets" have not been filled, their balance will reflect that, and future payments will need to pick up where the last payment left off.

I greatly appreciate any help!

-Dave

[comment]

The parents register, they select their optional items (milk, band, PE uniforms). Their BALANCE is calculated in the student table:

This looks like all the fees are set at the time of registration, and cannot be changed or added to later. Is this correct? If not, you will need another table to track the individual charges as they are being created over time.

Allocating a payment to different "accounts" can be scripted - but the script needs to find out the current balance of each "account" first. Then it's a relatively simple process of repeatedly "depositing" a sum into an "account" until you run out of money or debt - whichever comes first (and you also need to figure out what to do in case of overpayment).

[OG School]

Thanks, Comment. I don't suppose I can pay you to help, hmmm?

-Dave

[comment]

I don't know what your abilities are...

Anyway, I've sent you a private message.