1 2 Previous Next 17 Replies Latest reply on Nov 23, 2013 8:26 AM by DavidJondreau

    How can I display only dupes of one field from the found set of a sub-summary report?

    savingprivatedata

      UPDATE;
      I think my wording here, has misrepresented what I'm trying to do. I'd like to apologize to everyone who used their brain power to help me.
      I've posted an updated version of what I'm trying to do, by re-wording it.
      Yes, the new post looks like I'm trying to get different results than this post, but it's just a case of me, not knowing how to put what I'm trying to accomplish, into words.
      Thanks for all of your help and your patience.

      NEW POST;
      https://fmdev.filemaker.com/message/133316#133316

       

       

      Is there a way to use the results of a sub-summary report, as a source of data for another sub-summary report?

      In the example below, I created a sub-summary report to collapse each instance of Location > Equipment_SerialNumber so it only displays one instance of that combination. So, if there were 5 tests, my sub-summary report only shows one instance of Location > Equipment_SerialNumber, instead of all five.

       

      I need to find all of the results where an Equipment_SerialNumber shows up more than once.

      I need to find all of the results where an Equipment_SerialNumber shows up more than once from THESE RESULTS, not the underlying table, where it will show FIVE results for each test.

       

      Please look at ALL of the examples in RED before making suggestions.

      I have a report that shows every test number for every Equipment Serial Number, at every Location.

      I'm using sub-summaries to display one instance of Location / Store. I need to find dupes of the Equipment_Serials field FROM THE RESULTS of the sub-summary report, NOT the full set of everything from the underlying table.

      #1 BEFORE sub-summary sort;

      Location A > Equipment_SerialNumber1 / Aug2013 / Test15

      Location A > Equipment_SerialNumber1 / Aug2013 / Test16

      Location A > Equipment_SerialNumber1 / Aug2013 / Test 17

      Location B > Equipment_SerialNumber2 / Aug2013 / Test20

      Location B > Equipment_SerialNumber2 / Aug2013 / Test21

      Location B > Equipment_SerialNumber3 / Sep2013 / Test26

      Location C > Equipment_SerialNumber1 / Oct2013 / Test30

       

      #2 Will show up as;

      Location A / Equipment_SerialNumber1 / Aug2013

      Location B / Equipment_SerialNumber2 / Aug2013

      Location B / Equipment_SerialNumber3 / Sep2013

      Location C / Equipment_SerialNumber1 / Oct2013

       

      #3 I want to be able to show ONLY THE DUPES of equipment on the report;

      Location A / Equipment_SerialNumber1/ Aug2013

      Location C / Equipment_SerialNumber1 / Oct2013

       

      BECAUSE of the sub-summaries however, I can only get EVERYTHING to show up, like #2.
      I'd LIKE to have it display as #3, though.

      Any suggestions?
      Thanks!

        • 1. Re: How can I display only dupes of one field on a sub-summary report?
          usbc

          Kevin Frank has posted various ways of marking duplicates and thus original / unique.

          You can then just build your report based on the originals.

          http://www.filemakerhacks.com/?p=1829

          • 2. Re: How can I display only dupes of one field on a sub-summary report?
            savingprivatedata

            USBC, I appreciate the feedback. 

            I don't think this addresses what I'm trying to do, though.

            I'm having a hard time actually expressing what I'm trying to do.

             

            UPDATE - It looks like THIS article from that site, might address my issue.  After playing around with these methods, I'll post whether or not they worked for this particular issue.
            http://www.filemakerhacks.com/?p=8581

             

            Basically, I need to take the results of a sub-summary report and use that as the basis of ANOTHER sub-summary report that displays only the records where there are multiple instances of ONE field.

             

            The problem with trying to use ANY METHOD I've run across, is they all seem to be based on the original records.  I need to collapse those results and then display only the newly collapsed records that display multiple instances of a single field (EQUIPMENT).

             

            Thanks!

            • 3. Re: How can I display only dupes of one field from the found set of a sub-summary report?
              ch0c0halic

              One possible way is to create a calculation for the unique combination of location and equipment.

               

              Unique_loc_eq =

              location & “ “ & equipment

               

              Perform a find for duplicates in this unique field

               

              Enter Find Mode()

              Set Field (“Unique_loc_eq” ; “!”)

              Perform Find()

               

              Then do the subsummary report on the found set.

              • 4. Re: How can I display only dupes of one field from the found set of a sub-summary report?
                savingprivatedata
                ch0c0halic

                Thanks!  This doesn't address the issue, though.  It only creates a field that displays a single instance of ALL Location / Equipment records;

                (Based on #1 in my original post)

                Location A > Equipment 1

                Location B > Equipment 2

                Location B> Equipment 3

                 

                I need it to look more like;

                Location A / Equipment1 / Aug2013

                Location C / Equipment 1 / Oct2013

                • 5. Re: How can I display only dupes of one field from the found set of a sub-summary report?
                  savingprivatedata

                  It seems like I need to take the results of a sub-summary report and USE THAT as a data-source or virtual table, so I can run ANOTHER sub-summary on it. (to achieve the above)

                  • 6. Re: How can I display only dupes of one field on a sub-summary report?
                    usbc

                    Hi,

                    Sorry that in my first response I suggested to base your report on the "Originals". (meant to say "Duplicates")

                    I'm pretty sure that if you use one of Keven's methods and then base the report on the duplicates, it will get you what you want.

                    It produces a new field which contains the result "Original" -or-  "Duplicate". Then you do a search which includes only the dupes or omits originals.

                    You can also then count the number of duplicates and display that, too.

                     

                    As an aside, I think that using the exclamation point search will yes show the dupes but will also include the original.

                    • 7. Re: How can I display only dupes of one field from the found set of a sub-summary report?
                      pminich

                      I don't think that sub-summary reporting is the way to go for this.

                       

                      It looks like you really have the makings of 3 distinct but related tables:

                      • Locations
                      • Equipment
                      • Test Dates

                       

                      You want to create a relationship between test dates and equipment and have a calc that counts the number of test records for each piece of equipment. You can then create a table based on the Equipment list and filter out any pieces that have less than 2 tests. If you sort the test date/equipment relationship on test date descending order then the most recent test will be the first related record.

                       

                      You would also want to indicate the location that each piece of equipment is from so that you can pull in the location name.

                       

                      My personal feeling is that sub summary reports are fantastic tools but a lot of times there are more efficient ways to get better results. Summary and subsummary reports are resource intensive and when you start dealing with large data sets tend to bog down if you are doing anything except printing. Doing it this way requires a little more work on the back end but gives a better result.

                      • 8. Re: How can I display only dupes of one field from the found set of a sub-summary report?
                        savingprivatedata

                        pminich - the equipment # is actually a serial number (equipment1, equipment2, equipment3, etc...).  So, I'm not talking about a finite number of pieces of equipment.  It's literally millions of serial numbers.  Not filemaker serial fields, but actual serial numbers on pieces of equipment.
                        One of my concerns with your method, is this growing into a multi-billion record database (several multi-million record tables).

                         

                        I'm not trying to display equipment#'s (serials) with more than one test.  Most of the equipment has more than one test.

                        I'm trying to find instances where the equipment shows up at multiple locations.  That's why I chose the sub-summary report option.

                         

                        Sorry if I'm not expressing my intentions clearly.  It basically boils down to; my summary report lists all instances of EQUIPMENT# (SERIAL) / LOCATION

                        Now I need it to list a SECOND TIER of FIND/SORT where it displays only the SINGLE INSTANCE of EQUIPMENT# (SERIAL) / LOCATION where there are MULTIPLE instances of EQUIPMENT# (SERIAL) / LOCATION.

                        NOT instances where there is only one EQUIPMENT# (SERIAL) / LOCATION.

                        AFTER the original summary.

                         

                        I'm going to ruminate on your method though, and see if I can go in that direction.
                        Thank you!

                        • 9. Re: How can I display only dupes of one field on a sub-summary report?
                          savingprivatedata

                          usbc,

                          Thanks.  The problem here, which is the problem I've been having all along, is that I'm looking for dupes AFTER the results of the sub-summary report.  When I use the "!" or any other method, it seems to base the results on ALL of the records, not the results of my sub-summary.  ALL of my records are dupes because all of the equipment# (serial#) have multiple tests for each location.
                          I am looking through Kevin's posts, though.  The one I listed above, might still be a possibility...

                          • 10. Re: How can I display only dupes of one field from the found set of a sub-summary report?
                            pminich

                            I think that this is the most efficient way to meet your objectives. I have been down this road with summary reporting and it seems like it should be the answer but it really isn't. Frustrating, I know.

                             

                            This is the best way that I am aware of to locate duplicate records like you want to do. Believe me, doing it this way is way less resource intensive than trying to do it with summary reporting.

                             

                            As to the number of records I use this technique on a database the aggregates sales data from multiple Point Of Sales systems and have many tables with 10 million plus records. Performance is not an issue. One thing that I would recommend is that instead of using a straight calculation for the record count have it auto enter the calculation (replace as needed) so that the result is stored as a static number that gets updated. Relationships and Finds based on calculations are dog slow and should be avoided wherever possible.

                             

                            You should also look at using the operators to build your relationships (< > x ≠) these may help you. Usually building as much into the relationship helps the performance of the system.

                            • 11. Re: How can I display only dupes of one field from the found set of a sub-summary report?
                              wimdecorte

                              Seems like a good fit for the virtual list technique: collect the aggregate data by looping through the data (or using GetNthRecord, ExecuteSQL,...) and weed it out to just what it needs to be, then use a virtual list to display it to the user.

                              • 12. Re: How can I display only dupes of one field from the found set of a sub-summary report?
                                savingprivatedata

                                pminich,

                                This method doesn't actually give me the results I need, though.  It bases its results on all of the records in the table? 

                                 

                                I need my results to reflect a collapsed version of the existing records. 

                                 

                                If a EQUIP_Serial_1 at store A puts  out 5 tests, your method counts it as 5 tests?  I need that result to equal ONE test. 

                                THEN,  I need the next step to find the dupes of the Equipment# (serial#) from those results.

                                • 13. Re: How can I display only dupes of one field from the found set of a sub-summary report?
                                  savingprivatedata

                                  wimdecorte,

                                  I've tried using ExecuteSQL (I was a SQL person before I started Filemaker, so I'm more comfortable with it) but there aren't enough commands used in FM, to accomplish this, as far as I've seen.

                                   

                                  HOWEVER, I've never constructed a virtual list.  From what little I've read, this appears to mean a value list, as opposed to a listview.  If that's the case, it won't accomplish what I'm trying to do.

                                   

                                  I think part of the problem with my explanation is the fact that I used the term EQUIPMENT# as opposed to serial number.  I didn't want everyone to think I meant FILEMAKER SERIAL NUMBER.  So I tried to express the EQUIP# as a number in my original example.  I may edit this thread to reflect that fact, to cut down on confusion.

                                   

                                  I refuse to believe that a concept this simple, can't be reached with Filemaker.

                                   

                                  Thank you so much for your reply.  I appreciate it!

                                  • 14. Re: How can I display only dupes of one field from the found set of a sub-summary report?
                                    wimdecorte

                                    savingprivatedata wrote:

                                     

                                     

                                     

                                    HOWEVER, I've never constructed a virtual list.  From what little I've read, this appears to mean a value list, as opposed to a listview.  If that's the case, it won't accomplish what I'm trying to do.

                                     

                                    No, the technique can be used to populate a "virtual table" if you will.

                                     

                                    http://www.filemakerhacks.com/?tag=virtual-list

                                    1 2 Previous Next