11 Replies Latest reply on Oct 13, 2014 3:31 PM by WiserGuy

    Report filtering (omit duplicates)

    WiserGuy

      Title

      Report filtering (omit duplicates)

      Post

      Hi,

      I was helped a few weeks ago to create a portal where I wanted certain fields to be filtered out if there was multiple occurrences of them. PhilModJunk advised how to use a new table occurence to get the most recent record, and a portal filter to omit all but the newest ((http://forums.filemaker.com/posts/b3d5188388).

      But how to accomplish the same in a report (where there are other filters as well)?. See attached capture of my report where there are four different certificates for a specific rig. Among these, there are two fire certificates on the same serial number. The oldest fire certificate is superseded by the newer, and shouldn't be listed at all. The idea is to enter a date range at the top to get an overview of all certificates up for renewal within the date range. If I use a date range filter from 2010.10.01 - 2017.10.01, only the old certificate will show, making the report useless.

      Any ideas how to script a filter for this, or ideas for design restructuring?

      /WG

      Capture.PNG

        • 2. Re: Report filtering (omit duplicates)
          philmodjunk

          This may be the same advice that Steve is sharing, but I'd take a look at using a Summary report with Sub Summary layout parts to omit the older instances of the same certificate/license. By removing the body layout part, you can get one row of data for each group of records and if you sort those groups by a date field in descending order, a field in the sub summary should show data from the most recent instance of that record.

          • 3. Re: Report filtering (omit duplicates)
            WiserGuy

            Thanks both.

            I'm not sure how I can sort my relations to get this, but I will try and see.

            I have tried using sub summary parts, but this doesn't seem to help if I change the date filter.

            In my example report, the fire certificate due 2017.01.01 should be omitted which I can do by sub summaries. But if I add a filter to check everything due let's say up until March 2017, it will show again because the 2018.01.01 is filtered out, hence it's the newest.

            But as there are a newer (even though it's filtered out) the old shouldn't show up at all. Any ideas?
             

            • 4. Re: Report filtering (omit duplicates)
              philmodjunk

              I don't see any relationships in your example screen shot. What I see is a list view of 4 records. Sorted relationships would not play any role in what I am suggesting nor would you use any portals for this data either. You'd use a list view layout based on that portal's table with finds and sorts controlling what records are used to contribute data to your report.

              • 5. Re: Report filtering (omit duplicates)
                WiserGuy

                My apologizes, but I'm not following you now. What I tried to tell previously was that Steve's link suggested sorting by table relations.

                The sub summary approach (on a report) works OK without any filtering, but if I filter on dates, there might be "old certificates" showing because the report shows all the newest certificate within the filtered recordset, even though there are a newer one outside the recordset.

                For all I know you understand my problem perfectly well, but it's me being to lost to get you points, but let me try to explain differently:

                 

                Table tbDocs records:

                       
                • Owner
                •      
                • RigNo
                •      
                • SerialNo
                •      
                • TypeOfCert
                •      
                • Date
                •      
                • DueDate

                This should be listed in a report grouped by:

                       
                • Owner      
                              
                  • RigNo           
                                     
                    • SerialNo
                    •           
                              
                  •      
                       

                 

                If I understand you correctly, you suggested to add a new sub summary TypeOfCert below SerialNo and sort this decending due date to get only the newest which works, but as said, does not work well if the report is filtered on dates, as other "newest" might turn up instead.

                Sorry if I completely out of bounds here...

                • 6. Re: Report filtering (omit duplicates)
                  WiserGuy

                  I can create a self-join relationship and with a calculation field identify all duplicates on TypeOfCert. But is it possible to encapsulate that into a calculation where it only calculate duplicate if both SerialNo and TypeOfCert is duplicate?
                   

                  • 7. Re: Report filtering (omit duplicates)
                    philmodjunk

                    The purpose of using the Sub Summary layout part is to hide the older records for the same certificate. You would remove the body layout part and just keep the sub summary layout part.

                    Your sub summary layout part could specify "TypeOfCert" to group records by that type.

                    Your specified sort order could be:

                    Owner
                    TypeOfCert
                    Date

                    and you'd specifiy a descending order for Date.

                    • 8. Re: Report filtering (omit duplicates)
                      WiserGuy

                      Yes, if I use the sub summary as you suggest, the report the report will fire up only the latest of each TypeOfCert. In the attached example, only the red fire certificate will show up. But if I change the due date filter (green) to October 3. 2017, the one in blue will show up instead.

                      Let's say we are in 2017, and I need a report for everything due for recertification in 2017. Using this method, the sn12345 asset will be called in for a recertification of the fire certificate, even though this certificate actually is superseded with a newer one, not due until 2018.
                       

                      • 9. Re: Report filtering (omit duplicates)
                        WiserGuy

                        I finally managed to solve this:

                               
                        1. New calculated field (SerialNoAndTypeOfCert) to concatenate serialno and TypeOfCert
                        2.      
                        3. New calculated field to (DueDateAndID) concatenate due date and ID (highest number is the current certificate)
                        4.      
                        5. New table occurence (Cert|Same)with a self-join relationship between the new field in step 1
                        6.      
                        7. Sort table decending on DueDateAndID)
                        8.      
                        9. New calculated field to check for duplicate (DupCheck) using calc: Cert::DueDateAndID= Cert|Same:: DueDateAndID ;"Unique";"Duplicate"
                        10.      
                        11. Added Unique as a set field in the report script
                        12.      
                        13. Using body part

                        Thanks for all help!

                        • 10. Re: Report filtering (omit duplicates)
                          philmodjunk

                          Glad you got it working, but I'm puzzled as to why you would be filtering your records by that particular date in the first place as you would want the "most current" data on each asset.

                          • 11. Re: Report filtering (omit duplicates)
                            WiserGuy

                            Something is probably lost in translation here :)

                            If I have one particular equipment which we call in every second year for recalibration, this will have a new certicate in the system every 2 years. Let's say we have one

                                   
                            • September 10. 2010 - due September 10 2012
                            •      
                            • September 11. 2012 - due September 11 2014
                            •      
                            • September 6. 2014 - due September 6 2016

                            If I on November 14 2014 wants to find out which certificate are due for recalibration the next two months, I will filter on find due from * to December 14 2014. Using the subset variant, this would show the newest record in the find recordset, the one due in September 2014. This has already been recertified (on September 6) and we don't want to call this one in before September 2016.

                            Does that explanation make sense?