5 Replies Latest reply on Oct 24, 2014 2:49 PM by AjEGfmTech

    GetSummary for Portal Records



      GetSummary for Portal Records


      I am looking to find a way to get the Summary Total and Summary Count of related portal records.

      I have a simple database setup.
      Visits ---> Items

      Each 'Item' record has a 'Result' field that receives one of three scores: 0, 1 or 2.

      I want to show the current percentage score for the visit while the Item scores are being filled out in the portal.

      I could do this fairly easily by creating two summary fields, one for total and one for count, and then creating a calculation field that uses them to determine the percent score. I have this working now (though the summary fields and calculation field take two clicks outside of the portal before they update?). The only thing is that I don't want the '0' scores to count towards the overall percentage.

      For example.
      - Item record A, result = 1
      - Item record B, result = 0
      - Item record C, result = 2

      The total score for the visit equals 3, the total possible result score should equal 4, so 75%, not 6, which would be 50%. The '0' score should be thrown out because it represents an Item that was ignored.

      The only way I can think of doing something like this would be to make use of a calculation field instead of a summary field. With the calc field I can potentially build a filter to evaluate if a Item record score is greater than zero before it gets included in the summary. However, when I try to create a GetSummary calculation field to get the summary of the portal scores, the field is empty. I have the GetSummary calculation use the same break field as what the portal is sorted by, but that hasn't fixed the issue.

      It makes me wonder, can a GetSummary calculation field on the main layout show the summary of a field in portal records?

        • 1. Re: GetSummary for Portal Records
          My approach would be slightly different I think. I would most likely create a calculation field that only returns the result if the result is greater than 0. Then, I would point my summary fields to that calculation instead of the result field. I think you will then be able to directly reference the summary fields without the need for the getsummary function.
          • 2. Re: GetSummary for Portal Records

            ExecuteSQL is an option if you have FileMaker 12 or newer: FMP 12 Tip: Summary Recaps (Portal Subtotals)

            • 3. Re: GetSummary for Portal Records

              @Matt: Thanks, that's a good idea. Can I presume that your suggestion (and Phil's) confirms my suspicion, that a GetSummary calculation field on the main layout CANNOT show the summary of a field in portal records?

              @Phil: Thanks for the suggestion, but ExecuteSQL is just beyond me at this point. I saw in that post that you do describe a convoluted method of trying to achieve what ExecuteSQL can with built-in FM tools and capabilities. Hopefully I can find an alternate way of doing this that doesn't require something that sounds so complicated (and fragile?).

              Solution: Change the Question

              I'm going to do something similar to what Matt suggested by taking the '0' results out of the equation. Instead of having '0' represent the  'unanswered' item, I am just going to create a new field ('Answered') with a 0/1 result or just a no/yes result, and use that to filter out the unanswered items from the result score. That should take of this relatively simply.

              Followup Question: Slow Summary Updating

              I do have one followup question. In the existing setup, I have the summary fields for the portal records on the main layout. They update correctly and show the correct 'Total' and 'Count' summary, BUT, I have to click twice outside of the portal for the update to occur. In fact, the summary Total field will go blank while editing the portal result scores, until I click outside the portal and then click outside the portal again.

              Strangely enough, the summary fields have the same behavior, even when inside the portal with the records.

              Why is that and is there anything I can do differently to have the summary fields update immediately, as soon as a portal record score is changed?

              Thanks to both of you for all of your help.

              • 4. Re: GetSummary for Portal Records

                Sometimes you can get the same total and with much smoother updating if you replace the summary field in the child table with a calculation field in the parent table that uses an equivalent aggregate function such as Count or Sum.

                • 5. Re: GetSummary for Portal Records

                  Thanks a bunch Phil! That worked like a charm! So simple and clean too.

                  Caveat: While it does not specifically solve the original post question, it does provide a clean and simple solution to the database issue.