7 Replies Latest reply on Aug 9, 2011 5:31 PM by eibcga

    List Aggregate Function or?...

    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

        • 1. Re: List Aggregate Function or?...
          philmodjunk

          The biggest challenge to what you've requested here is that running totals are dependent on the current found set and the sort order. Change either the order or the found set and the values in each record change to match.

          If we can first copy the running total value into a stored field, this becomes a fairly simple task. A script trigger set on the relevant data fields in ledger (such as amount), can store the current value in a number field. Then a portal to this table can sort values on this number field to display the values in descending order. Either the relationship or a portal filter (if you have Filemaker 11) can restrict the values shown to just those of a specified date range.

          • 2. Re: List Aggregate Function or?...
            eibcga

            I understand the concept that running totals (i.e., the summary field LEDGER::balance) are dependent on the current found set and the sort order.  While the found set will vary based on what particular account I'm interested in (e.g., only accounts that have perpetual balances, like assets or liabilities, as opposed to accounts that have periodic balances, like revenues or expense accounts), the sort order will always be by JOURNAL::date.  Using GetSummary is unstored, so that won't help.  I don't know how to make a field that will refer to the running total field and make the result stored.

            • 3. Re: List Aggregate Function or?...
              philmodjunk

              Use a script trigger OnObjectSave set on the amount field (Suprised you don't have separate debit and credit fields here.)

              Set Field [Ledger::StoredBalance ; Ledger::RunningBalance ]

              For existing records, you can use Replace Field Contents with the calculation option to copy the running balance into a simple number field.

              • 4. Re: List Aggregate Function or?...
                eibcga

                Thanks PhilModJunk.

                Actually, I do have separate fields for debit and credits, but these are for presentation purposes only on layouts.  However, data entry for each LEDGER child record of the JOURNAL parent table is done in the LEDGER::amount field (as a positive or negative amount as appropriate, i.e., positive for debit, negative for credit).  The LEDGER::debit and LEDGER::credit fields are simply calculation fields.  If the amount entered in the LEDGER::amount field is positive, then show the amount in the LEDGER::debit field.  If the LEDGER::amount is a negative amount, then show the amount in the LEDGER::credit field.

                Anyway, I will play around with your suggestion to see what results I can get.  While I can answer the above questions by either scanning the account running balances manually (error prone), or exporting the LEDGER records to Excel and doing a MAX or RANK calculation on all balances for a year, I was hoping FileMaker could do all this for me somehow.

                Thanks again for the help.

                • 5. Re: List Aggregate Function or?...
                  philmodjunk

                  What I am describing is a trimmed down version of exporting the data to excel that should enable you to do all of this from within FileMaker. The script won't store the correct value unless you have the correct records in your current found set and in correct order at the moment it executes.

                  • 6. Re: List Aggregate Function or?...
                    eibcga

                    Exactly thanks.  This is what I was hoping could be done, but was not sure how.  

                    • 7. Re: List Aggregate Function or?...
                      eibcga

                      I just tried your suggestions and they work perfrectly.  Thanks again.  I didn't need to use a portal or relationships to get this working.  I just used my existing relationships and:

                      - in table view, sorted the LEDGER table by JOURNAL::date on the found set for the account in question

                      - ran Replace Field Contents on a new LEDGER::stored_balance field calculated based on LEDGER::balance, and

                      - while in LEDGER table view, sorted the LEDGER::stored_balance field in descending order.

                      Works as intended.

                      Since I actually only needed to get this information for all financial transactions prior to the current year, I have kept your script trigger suggestion for future reference.