3 Replies Latest reply on Aug 25, 2014 12:56 PM by philmodjunk






           I want to summarise activities times. There are 3 related tables: Person < Date < Activities. The Activity times and their summary are shown in a portal on the Person layout. 2 questions:

           1. I have the portal records in reverse, ie. Latest record at the top. The fields in the portal are as expected except the summary field. I have one of these fields to summarise time for each particular activity. Oddly each time eg. Activity A gains an extra time the summary field increments correctly, but it does this in ascending order, rather than descending order of the portal records.

           2. From the daily summary fields I'll like to do custom summaries by 'person' and by 'date'. Should I make a custom 'summary' field that calculates based on: a) a checkbox value list of person(s) & b) a case/if calc of date between a start and end date? Or is there a better way?

        • 1. Re: Timings

               1) I gather that this is a Running Total Summary--which will work in reverse of what you want due to the reverse order in your portal. To get the correct order, add a second summary field with the same settings except do not specify that it be a running total. Then use a calculation field with this expression to get a "reversed" running total in your portal:

               NewSummaryField - RunningTotalSummaryFIeld

               The new summary field will compute a "grand total" for the records in your portal and this calculation will then get a descending running total by subtracting from that grand total.

               2) This sounds like something better produced from a summary report using sub summary layout parts instead of a portal: Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Timings

                 The problem with: NewSummaryField - RunningTotalSummaryFIeld

                 is that the reverse running summary that you achieve only gives the result with the next record, ie. if you'd added 3 seconds of activity, the cumulative total would only show this on the next record.

                 Could I not instead use: Time: categorX+GetNthRecord (CategorXSummary; Get ( RecordNumber ) - 1 ). The catch is, how do I start this summary because for the 1st record there won't be a Get ( RecordNumber ) - 1?

            • 3. Re: Timings

                   Let ( R = Get ( RecordNumber ) ;
                           If ( R > 1 ; GetNthRecord (CategorXSummary; Get ( RecordNumber ) - 1 )

                   But I do not recommend using GetNthRecord in this fashion for any but very small numbers of records. You can easily max out system resources and get ? results.

                   You may be able to use a method that uses getNthRecord as an auto-enter calculation to copy a value from the preceding record and this avoids this issue, but can create problems in getting these totals to update correction in all situations.