10 Replies Latest reply on Nov 7, 2012 12:45 PM by Sorbsbuster

    Summary of Portal data

    obidon

      Title

      Summary of Portal data

      Post

           Afternoon

           Filemaker Advance 12v3 on an iMac with OS 10.7.5

           I have a simple table which contains 2 data fields.  'observation' and 'code'.  Observation is text and Code is populated by a popup menu with the choices C1, C2, C3.

           I have include a summary field which counts 'code'.  This has been included in a sub-summary and summary part.  The intention is to tell me how many of each code type i have.  In a simple report list, this is working as expected. 

           The problem is that I need to display the summary data on a layout when viewing the table through a portal.  Can somebody point me in the right direction please

            

            

        • 1. Re: Summary of Portal data
          philmodjunk

               If you want just the count of records appearing in your portal and the portal is unfiltered, just add your summary field directly to the layout and it will display the number of records showing in the portal. If you filter your portal with a filter expression (requires FileMaker 11 or newer), then you need place this summary field inside a one row portal with the same relationship and filter expression as the larger multi-row portal.

          • 2. Re: Summary of Portal data
            obidon

                 Hi Phil

                 The portal I see will show all related records.  The records are sorted in specific way.  I don't want to filter or sort them any other way.

                 I have the ::summary field "total count" on my 'portal' layout.  I need to create a summary count of the C1, the C2 and the C3 records

            • 3. Re: Summary of Portal data
              philmodjunk

                   Such sub totals via a related table were rather tricky before the release of FileMaker 12 with the new ExecuteSQL calculation. I suggest using that function to get your counts of the number of related records that have each of the specified values in the code field.

              FMP 12 Tip: Summary Recaps (Portal Subtotals)

                   When working with ExecuteSQL, I recommend two resources that are readily available:

                   SeedCode's free SQL Explorer: http://www.seedcode.com/cp-app/ste_cat/sqlxfree

                   and The PDF file, "ODBC and JDBC Guide", that can be opened from your Help menu in FileMaker 12. The documentation of the SELECT statement--the only statement supported by ExecuteSQL, found in this document, spells out the correct syntax to be used with this new function.

              • 4. Re: Summary of Portal data
                Sorbsbuster

                     As per Phil's suggestion: if you only have the 3 values you can create a 3-line portal.  Actually, 3 x 1-line portals below each other.  Set the Summary Count field  in each, but filter the first one to only show records where the value = C1, the second C2, etc.  You can leave your existing portal as-is.  Sounds too simple, though...

                • 5. Re: Summary of Portal data
                  philmodjunk

                       What Sorbuster describes is how I would set this up in Filemaker 11.

                  • 6. Re: Summary of Portal data
                    obidon

                         Afternoon Gents,

                         I'd like to avoid SQL if I can, its far above my current knowledge.

                         I have created three portals, one for each code type.  These have been replicated to provide the summary data I require. See the attached screenshot

                         This screen will be hidden from the user,  but the '3 summary count fields' are required for a report.  I like to keep things neat.  Is there away of using a calculation to extract the data and avoid portal on the report screen?

                    • 7. Re: Summary of Portal data
                      Sorbsbuster

                           The one-row-one-field portal is only a trick.  Why do you not put the summary count field on the report where you want it to be, but just actually have it sitting in a portal?  They can both be the same size, so the user wouldn't need to know that the field was actually the sole component of a portal.

                      • 8. Re: Summary of Portal data
                        obidon

                             Thanks Guys

                             I think thats what I'll do for now, although you know this will bite me on the ass in 6 months when I have to revisit the scheme

                              

                        wink

                        • 9. Re: Summary of Portal data
                          philmodjunk

                               One of the reasons that I recommended ExecuteSQL is that it is more flexible. A single calculation field using it can list all existing values with a count or other aggregate result for each and if you later add/change the values used in this table, this calculation can still work to display the totals as it works directly from the data instead of using a filter that tends to "hardwire" you to a specific value or at least a specific number of values (one portal for each value...)

                          • 10. Re: Summary of Portal data
                            Sorbsbuster

                            "this will bite me on the ass in 6 months when I have to revisit the scheme"

                                 And the Case for The Defence, M'Lud :  "if you only have the 3 values you can create a 3-line portal."