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

    synergy46

      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:

      DESPortal.png

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

      sub-summary report.

       

      DESReport.png

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

       

      Thanks

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

          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
            ron.harris

            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
                synergy46

                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.

                 

                Ron

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

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

                   

                  The report design looks like this:

                   

                  DESReportDesign.png

                  The Sub-summary is sorted on TYPE. 

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

                   

                  Thanks

                   

                  Ron

                  • 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
                      synergy46

                      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

                       

                      ron

                       

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

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

                        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
                          synergy46

                          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
                            Mike_Mitchell

                            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.   

                             

                            HTH

                             

                            Mike

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

                              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.

                               

                              script_example.png

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

                                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

                                 

                                Ron

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

                                  See if the attached works for you.

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

                                    Almost got it... but no cigar. 

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

                                     

                                    DESMember.png

                                     

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

                                     

                                    DESReport.png

                                    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