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!

Running FM 5.5 Server and Client 6.0 on mac os Tiger.

Thanks!

In article <1182440863.249859.16200 (AT) m36g2000hse (DOT) googlegroups.com>,
webmaster (AT) asmt (DOT) com wrote:

It's not really possible to give an accurate answer since you haven't
said how payments are stored, how you're getting balances, etc.

The normal / easiest approach is to probably store all the Payment
records in one file, marking them as either "Paid" and "Future". As the
Payments are paid the record is changed from "Future" to "Paid". (You
could use a third option of "Unpaid".)

At any stage you can see those who are 'behind' by simply performing a
Find on records with a PaymentDate <= today AND marked as "Future".

You can also see those 'ahead' by performing a Find on records with a
PaymentDate > today AND marked as "Paid".

These can also be used via Relationships to enable you to total obtain
the total of Paid and Future payments.


Note: Do not use FileMaker's "Today" function. Instead use the
Status(CurrentDate) function to obtain today's date.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

On Jun 26, 1:18 am, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com>
wrote:
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.

In article <1182968643.256653.124230 (AT) q75g2000hsh (DOT) googlegroups.com>,
WebMaster <webmaster (AT) asmt (DOT) com> wrote:

Now that sheds more light on the problem and gives me the clues to
where you are heading in the wrong direction. )

Both of these problems are almost certainly caused by misunderstanding
the use of Summary fields. Summary fields are great for PRINTED or
Previewed reports where you are grouping subsets or grand totals of
records, and would be fine if you were printing a report for all / some
of the students from the Ledger file (you may want to leave the
existing fields for that purpose).

Unfortuately Summary fields are (almost) useless for on-screen
calculations. They calculate across ALL records, not just the related
ones, which is why you've discovered that for Problem #1 you first have
to find the student records in the Ledger file for them to work (you're
basically limiting "all" to the Found Set).

Instead you have to make use of FileMaker's ability to aggregate fields
data across a Relationship - using the simple functions like Sum, Max,
Average, etc. in a similar way to totalling groups of cells in an Excel
spreadsheet.


PROBLEM #1 - the PayOff calculations
----------
In the Student file you can create the same Calculation fields that
work via the a Relationship (the same one the Portal uses) to total the
appropriate values from just the related records.
eg.
TotalCharges Calculation, Number Result
= Sum(rel_LedgerRecords::Charges)

TotalPayments Calculation, Number Result
= Sum(rel_LedgerRecords::Payments)

Payoff Calculation, Number Result
= TotalCharges - TotalPayments

Putting the related Ledger file field inside the Sum() function means
that only the RELATED Ledger record fields will be totalled - each
Student will calculate only their own totals.

These fields can then be put on the layout in the Student file -
outside the Portal, perhaps underneath the appropriate Portal columns.
They will display different values depending on which Student record
you're looking at. They can also be used when performing Finds (eg.
Find all Students records with Payoff > 100).

Note: There are other ways to achieve the same result, but this is
probably the easiest to understand.



PROBLEM #2 - the Balance Due calculation
----------
The DateTest and LateFee fields are both correct and should be in the
Ledger file since they are working out whether the individual Ledger
record is "late".

The next bit, the PostToBalanceDue Calculation, is where the problems
of using Summary fields rears it's head again. In fact you don't need
the "PostTo" field at all since you can directly total the Ledger
records from the Student file.

I don't fully understand your mathematics here, but I almost certain
that this field should again be moved to the Student file so that it
can use the relationship link to obtain the appropriate totals from all
the related records for the student.
eg.
BalanceDue Calculation, Numebr Result
= If(Sum(rel_LedgerRecords::ExpectedPayments) = 0,

Sum(rel_LedgerRecords::Charges) -
Sum(rel_LedgerRecords::Payments),

Sum(rel_LedgerRecords:ateTest)
+ Sum(rel_LedgerRecords::LateFee)
- Sum(rel_LedgerRecords::Payments)
+ Sum(rel_LedgerRecords::EnrollmentFeePayment)
+ Sum(rel_LedgerRecords:ownPayment)
)

This Calculation may need some tweaking to match what you're trying to
achieve. Again, put this field on a Layout in the Student file.



PROMBLEM #3 - the printed report
-----------
You may not care baout this one, but it might give you a better
understanding of Summary fields.

As stated above, Summary fields are great for printed reports, and for
this purpose they do need to be in the Ledger file (assuming you also
need to print the values of each individual Ledger record, you
shouldn't print Portals) ... but they need to be used correctly.

Once you've defined the Summary fields you need to put them in the best
place on the Layout for what you're trying to achieve. For example, if
you are wanting a report brokendown / sub-summarised by StudentID and a
grand total at the end, then you would need a Layout that might be
something like:

LEDGER REPORT (by Student)


[rel_StudentRecords::StudentName]


[Charges] [Payments]


Student Totals:
[TotalCharges] [TotalPayments]


Report Totals:
[TotalCharges] [TotalPayments]



The two TotalCharges and two TotalPayments fields are the some ones,
just reused in different parts of the Layout - it's these Layout parts
(and correcting sorting of the records) that determines what values are
printed.

To obtain the report you would Find the records for the Student(s) you
want to report on, sort them by the StudentID and print the report
Layout.

The important bit here is to SORT the records so that they are
correctly grouped for the Sub-summary Layout parts. When using a Layout
that has multiple breakdowns / sub-summaries you have to be careful
about the Sort Order used - it must match the order of the Leading
Sub-summary parts of the layout (or the reverse of the Trailing
Sub-summary parts).

When sorted correctly, the printed report would give a sub-total for
each student and a grand total at the end for both the Charges and
Payments fields. When sorted incorrectly you will get meaningless or
blank results.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)