4 Replies Latest reply on Dec 6, 2010 12:25 PM by pdoak_1

    Problems with Get Summary from related table occurence



      Problems with Get Summary from related table occurence


      Background Info: I am using the following

      1. Filemaker pro Advanced 11
      2. Mac OS X 10.6.5
      3. Single user mode
      4. I have been using Filemaker Pro for a couple of month

      I have two tables both of which summarise data by ClientID.  I wish to produce a layout which uses the total of the two summarised data for other calculations within the layout.  I can produce two separate layouts which correctly reflect the summary data either by using Get Summary or by creating a "summary" field.  I can even produce the correct summary data for each field on one layout using the Summary field created in each table.  However, when I try and capture the summary data from the other table using Get Summary, it does not pick up any data at all for the field using Get Summary from the other table.

      I am sure that I am making a basic error but cannot see what the problem is.  Any suggestions would be appreciated.

      Many thanks

        • 1. Re: Problems with Get Summary from related table occurence

          The basic problem is that GetSummary uses the second "break" field to determine the correct subtotal to compute. The records must first be sorted by this break field in order to group them correctly for computing such a sub total. Basically, it works the same as placing a summary field inside a Sub Summary part. The "sorted by" field of the sub summary part serves the same purpose as the "break" field parameter of the GetSummary Function.

          It's intended for use with local fields as the sort order is specified at the layout level, but a reference to a related table such as RelatedTable::FieldName takes place at the data level.

          It should still be possible to get the summary values you want, but not with GetSummary. If you want, tell us more about how your database is designed and what totals and/or subtotals you need to compute for your report.

          • 2. Re: Problems with Get Summary from related table occurence

            I have included links to the following files so hopefully you can show me where I am going wrong:

            I am trying to get the CashSum total by clientid to show up the layout.  I can get the MTMSum by ClientID but the MTMSum is included within the table transcapital_allocationdata_INSTRUMENTCOSTTABLE.  I can also get the MTMSum by client by using GetSummary as shown in the field Net Equity.  When I try and use GetSummary for the CashSum field which is in another table it returns nothing in the layout.

            Once I can get the CashSum total by ClientID, I wish to add it to the MTMSum to obtain total assets by client.  From here, I can then hopefully calculate % of MTM/(CashSum+MTMSum) by client.

            Any help much appreciated.

            • 3. Re: Problems with Get Summary from related table occurence

              It's a bit hard to decipher so check these details:

              You have this relationship:

              transcapital_allocationdata_INSTRUMENTCOSTTABLE::_kf_ClientID = transcapital_allocationdata_instrumentcosttable_AccountCash 3::_kf_ClientID AND
              transcapital_allocationdata_INSTRUMENTCOSTTABLE::ClientAccnt = transcapital_allocationdata_instrumentcosttable_AccountCash 3::clientAccnt

              It would seem that this calculation defined in Instrumentcosttable defined to evaluate from the context of transcapital_allocationdata_INSTRUMENTCOSTTABLE will produce the desired subtotal:

              Sum ( transcapital_allocationdata_instrumentcosttable_AccountCash 3::CashMovement )

              This assumes that you want the total CashMovement of all records in AccountCash with the same client id and client account as a given record in InstrumentCost. This may not be a correct assumption on my part.

              • 4. Re: Problems with Get Summary from related table occurence

                That worked.  Thank you very much.