Accounts Receivable and Date Control

[EmDub]

We're building a simple ledger for our clients, many of whom pay us in installments. We can easily post charges and payments, which results in a balance.

What we're trying to figure out is how to post planned installments (out into the future 18 months), then calculate whether clients are ahead or behind at any given time. This seems to involve checking whether Today <= ExpectedPaymentDate, and then calculating TotalExpectedPayments + TotalLateFees - TotalPayments.

Is this a Case statement? Any ideas or references would be welcome!

Thanks!

[EmDub]

Ok, here is further clarification.

Overview

· We have our main Student database that contains contact information.

· We created a separate database called Ledgers, where each transaction is a record.

· The data entry happens in the Student database, through a portal to Ledgers.

· Each record in Ledgers contains a StudentID field, which we use as a link to create a relationship with all the Ledgers records.

· Each record in Ledgers has all of the fields below.

Once we post Charges and Payments, we care about two numbers: Payoff and Current Balance Due.

Fields, Definitions, and Calculations

The simpler fields look like this:

· Charges defined as Number

· Payments defined as Number

· ExpectedDate defined as Date

· ExpectedPayment defined as Number

· TotalExpectedPayments defined as total of ExpectedPayments

· EnrollmentFeePayment defined as Number

· DownPayment defined as Number

· GeneralLedger that provides the option to associate each transaction with a general ledger account in our bookkeeping software through a Values list

To calculate Payoff, we use the following:

· TotalCharges defined as Total of Charges

· TotalPayments defined as Total of Payments

· Payoff = TotalCharges – TotalPayments

This seems to work, as long as you open Ledgers and Find all records with that particular StudentID. Then when you toggle back into Student, you see the correct Payoff. The fact that this does not automatically happen is Problem #1.

To calculate Current Balance Due is more complicated:

· DateTest that finds whether an ExpectedDate is before today, and if so, pulls the ExpectedPayment:

If(Status(CurrentDate) >= ExpectedDate, ExpectedPayment, 0)

· LateFee looks for a late fee, based on text in the GeneralLedger field:

If(GeneralLedger = “4210 Late Fees”, Charges, 0)

· PostToBalanceDue pulls any amount from DateTest, LateFee or Payments (minus EnrollmentFeePayment and DownPayment)

Unstored, If(TotalExpectedPayments = 0, (Charges – Payments), (DateTest + LateFee – Payments + EnrollmentFeePayment + DownPayment)

· BalanceDue is a running summary of PostToBalanceDue

BalanceDue is supposed to show whether a student is ahead or behind on payments. Unfortunately, it has become Problem #2.

Again, thanks in advance for any insights.

MW

[EmDub]

Ok, this is so simple, all the non-novices are rolling their eyes and chortling heartily.

After a few tries, it became obvious that the calculations needed to be performed in the Student database, not in the transaction database. So, there's a field in Students that shows the total amount the student owes. It is defined as Transactions::sum(Charges) - Transactions::sum(Payments) and voila!

Our next step may be automated transaction posting for service charges and late fees. They work in the test database.

Hope this helps someone!

MW