AnsweredAssumed Answered

List Aggregate Function or?...

Question asked by eibcga on Aug 7, 2011
Latest reply on Aug 9, 2011 by eibcga

Title

List Aggregate Function or?...

Post

I was looking at the online help for List at http://www.filemaker.com/11help/html/func_ref1.31.4.html#1036551.  It shows an example for related records when the values are 100, 200, and 300.  Is it possible to have FileMaker tell me what the maximum value is from this list?

Here's what I'm trying to accomplish: I would like a List (or a portal), sorted by balance in descending order, of the highest balances (running totals) of an account that ever occurred (or during a certain calendar year), and the dates on which they occurred.

For example:  From a bank checking account:

List of highest balances of account Bank Checking during the calendar year 2010:

Date        Account Balance

March 27, 2010    $25,000

January 3, 2010      $8,000

October 8, 2010     $7,900

and so on...

The list answers the questions, what was the account's highest balances, and on what dates did these highest balances occur?  In this case, based on the above list, the Bank Checking account's highest balance ever was $25,000 on Mar 27/10, the next highest balance was $8,000 on Jan 3/10, and so on.

I would prefer to have the above list so that it would list the highest account running totals of an account up to a specified date.  For example, during the calendar year 2010, what are the highest account balances for the year 2010 (or during some other year).  I figured that all I need to do there is to change the found set in the LEDGER table so that the LEDGER's records (sorted by JOURNAL::date) are for anything occurring between certain dates.  The problem I then have is how do I exclude the highest balances on the list for 2010 that were dated in 2009?  How do I ensure that the running total calculates correctly when the period is only for a specified period to answer the question, what was the bank's highest account balance during calendar year 2010, taking the year's opening balance into consideration?

I have a financial LEDGER child table of journal line items, and a JOURNAL parent table.  In the LEDGER table is a summary field LEDGER:balance, Total Of LEDGER:amount, Running Total.  Since the highest balance is date sensitive, the found set in the LEDGER table must be sorted by the JOURNAL::date field.

Any ideas?

Thanks very much.  Beginner using FMPA11.0v3

 

Screen_shot_2011-05-14_at_9.56.33_PM.png

Outcomes