1 2 3 4 Previous Next 52 Replies Latest reply on Jul 23, 2016 11:10 AM by Mike_Mitchell

    FM 15 ADV Reporting too much data


      I have 2 tables:  Members and DES

      Members is 1 to many with DES

      DES has a portal on the Members layout

      This works.


      The problem:

      When I produce a sub-summary report I get ALL the DES records for each members. 

      I want to get just the first (top row) in the portal in the report.


      I suspect that I need a different DES TO ?????

      Below is the portal for Rudolph Graham:


      Below is the DES Sub-Summary Report..  Notice the presentation of each portal row in the

      sub-summary report.



      Question:  "How do I show just Graham's 'top' portal record (Reinstated) in the report?



        • 1. Re: FM 15 ADV Reporting too much data

          New to FMP, please excuse my ignorance; perhaps you can use max(date) in your query or

          sort by date desc and select top 1?

          • 2. Re: FM 15 ADV Reporting too much data

            In another thread I read this...


            PHILMODJUNK Wrote:

            "If you use sub summary layout parts and remove the body layout part, we can produce a summary report with just one line for each summarized group of records."

            • 3. Re: FM 15 ADV Reporting too much data
              Stephen Huston

              If you Sort the Relationship (not the portal, but the relationship setup itself) so the related record you want will be first (latest date?),  don't need a portal on your report, just one instance of the related fields.

              They will show the "top-most" data of the related records.

              • 4. Re: FM 15 ADV Reporting too much data

                I think I wasn't clear on what I am trying to accomplish.

                For the member Rudolph Graham I want to produce in the report just the top value on in the DES portal on the Members Window; the REINSTATED record.   I DO NOT want to get the other portal records (Expelled and Suspended).  


                What I do not want is shown in the 2nd image above.  I WANT  Reinstated and not Expelled or Suspended.


                And, the report does NOT have a portal on it.  It is just a LIST.



                • 5. Re: FM 15 ADV Reporting too much data

                  The relationship is all ready sorted by DES::Date.  So, it doesn't look like that helps.


                  The report design looks like this:



                  The Sub-summary is sorted on TYPE. 

                  Surely there is a way to get just the 'top', most recent DES record?





                  • 6. Re: FM 15 ADV Reporting too much data
                    Markus Schneider

                    Create a script that goes from members to the 'real' table (DES?), search for the specific member and desired state, then go to the report-layout, sort. Voila!

                    The script can get the member-ID from the member table via $Variable

                    • 7. Re: FM 15 ADV Reporting too much data

                      Yes, that would work if I had just a single member.  But, I have 150+

                      members and each needs to show just the most current DES (portal) record in

                      the sub-summary report.


                      As you can see, the sub-summary report is sorted on DES::Type which creates

                      the sub-summary report but also does not differentiate between the most

                      current DES record and the lesser dated DES records.... grrrrr


                      thanks for the thought




                      On Tue, Jul 12, 2016 at 11:22 PM, Markus Schneider <noreply@filemaker.com>

                      • 8. Re: FM 15 ADV Reporting too much data

                        Why can't you just go to the list layout and perform a find where DES::Type = "Reinstated"?


                        If you don't tell FileMaker what records you are looking for, it has no idea that you only want the "Reinstated" ones...

                        • 9. Re: FM 15 ADV Reporting too much data

                          Yes, I 'could' go to the layout and find DES::Type="Reinstated" BUT, there are 150+ members and some have a most current of Reinstated.  Others may have a current record of "Deceased"  etc.... I have no way of knowing what that most current record will be for any given user and the report should show the most current DES::Type no matter what it is.


                          I want to have FM group by DES::Type and then show the most current DES::Type record; and not show the rest.

                          • 10. Re: FM 15 ADV Reporting too much data

                            If you reference the DES::Type field from the context of Members outside of a portal (don't put the focus on a portal row), it will automatically pull the first record. You can use this to your advantage. Couple of different ways to accomplish this:


                            1) Put a calculation field (unstored) in the Members table equal to DES::ID (whatever your unique ID is). Use a ListOf Summary field to assemble all those IDs into a list. Go to the report layout (presumably based on DES) and loop over the list, creating a Find request for each ID. Perform the Find and you'll have all the "first" DES records from your original found Members set. (Caution: May be slow.)


                            2) As in 1, create the DES::ID calc field in Members. Create a new relationship to DES based on that ID field. Use Go to Related Record (entire found set). This will also isolate all DES records corresponding to the "first" record for each  member.


                            3) Write a script to find all DES records for the current Members found set (possibly using GTRR). Sort the list by member ID and then by date. Loop over the records, omitting all but the first one.


                            One cat, many skinning methods.   





                            • 11. Re: FM 15 ADV Reporting too much data

                              Got it.  I misunderstood.  You want the most recent DES record, not just the "Reinstated" one.


                              There are a few ways to approach this, Mike explained a few of them in the post above.  I've used the ListOf summary field approach, but it does add overhead to the table.


                              Here is an example of looping over the members and gathering the PK IDs of the "current" DES record based on the relationship that is sorted by date into a return separated list, then going to the DES layout and doing a looping "Extend Found Set" to find all of the DES records in your list.



                              • 12. Re: FM 15 ADV Reporting too much data

                                Thank you for the code and clear explanation.


                                While I am sure this would work, I find it surprising that there isn't some kind of Filemaker 'setting' or 'workaround' that would facilitate what I am after without the coding. 


                                After all, "Is this situation not uncommon"?


                                Thanks again



                                • 13. Re: FM 15 ADV Reporting too much data

                                  See if the attached works for you.

                                  • 14. Re: FM 15 ADV Reporting too much data

                                    Almost got it... but no cigar. 

                                    When I add a 2nd 'Type' (Expelled) ...




                                    The report fails to show the most current 'Expelled' and instead shows ALL Expelled



                                    I wonder, "SInce I want each member and their most current TYPE (Expelled, Suspended etc) perhaps I should be basing the report on Members and not DES...???

                                    1 2 3 4 Previous Next