5 Replies Latest reply on Dec 18, 2013 11:02 AM by philmodjunk

    Trying to report unique instances of duplicates after constraining find

    StephenWeiss

      Title

      Trying to report unique instances of duplicates after constraining find

      Post

           Trying to report unique instances of duplicates after constraining find
           I have the following structure;
      FIELDS;
           UniqueID
           Equip_ID
           Location
           TestNumber
           TestDate
            
           This table records each test number that a piece of equipment performs, along with the date of the test and the location.
            
           There are often multiple tests per piece of equipment on a given day.
           I'm trying to report the following;
           1) Multiple instances of a Equip_ID in a given time period.
           2) Locations transmitting from Equip_ID in a given timer period.
            
      I want to be able to take this;
            
      Equip_ID Location | TestNumber TestDate
           1 NewYork 111 10-5-2013
           1 NewYork 112 10-5-2013
           1 NewYork 113 10-5-2013
           2 Utah 245 10-5-2013
           3 California 345 10-6-2013
           3 California 346 10-6-2013
           4 Oklahoma 400 10-6-2013
           4 Oklahoma 401 10-6-2013
           5 Florida 405 10-7-2013
           6 Illinois 450 10-7-2013
           6 Illinois 451 10-7-2013
           6 Illinois 452 10-7-2013
           7 NewYork 500 10-7-2013
           7 NewYork 500 10-7-2013
           8 Florida 530 10-8-2013
           8 Florida 531 10-8-2013
           1 NewJersey 575 10-10-2013
            
      And display single instances of LOCATIONS that transmitted tests from more than one Equip_ID;
      For the month of Oct.2013;
      Equip_ID Location TestDate
      1 NewYork 10/2013
      7 NewYork 10/2013
      5 Florida 10/2013
      8 Florida 10/2013
            
           AND
            
      Display Equip_ID that transmitted from multiple locations
      For the month of Oct.2013;
      Equip_ID Location TestDate
      1 NewYork 10/2013
      1 NewJersey 10/2013
            
           THANKS IN ADVANCE!

        • 1. Re: Trying to report unique instances of duplicates after constraining find
          philmodjunk

               Set up 2 layouts just for these 2 reports.

               First report:

               Remove the body layout part and replace it with a sub summary layout part. Select Equip_ID as the "when sorted by" field. Put the fields you want to see in this sub summary layout part. Sort your records by Equip_ID and this sub summary layout part will be visible and you'll get one row of data for each Equipment ID.

               2nd Report

               Do the same, but add a sub summary layout part and specify Location as the When sorted by field. Sort your records with a sort order that sorts them first by Equipment ID and then by location.

               For more on summary reports: Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Trying to report unique instances of duplicates after constraining find
            StephenWeiss

                 Thanks PhilModJunk, 

                 Using a sub-summary report was the first method I tried however, this doesn't address the issue of displaying only single instances of duplicate Equip_IDs for each Location in a given time period.

                 It will only display a single instance of every Equip_ID in that time period.  Because almost each Equip_ID has multiple tests from the same Location, this will just display almost everything.

                 I believe it's the same with the Location version.

                 I appreciate the response, though!  Thank you!

            • 3. Re: Trying to report unique instances of duplicates after constraining find
              philmodjunk

                   A sub summary layout part "when sorted by Equip_IDs"

                   can be used for this purpose if you sort by location, then by equipment ID. You'll get one row of data for every item of equipment tested at each location.

              • 4. Re: Trying to report unique instances of duplicates after constraining find
                StephenWeiss

                     Again, thanks!
                     I've originally tried this as a sub-summary report, and when this was suggested on another filemaker forum, I just accepted this as the answer.
                     The problem is, even when I narrow it down to one date range and use the sub-summary report, I still get thousands and thousands of rows to sift through, before I find the one Equip_ID with multiple stores.
                     It kind of looks like this, only with thousands of rows to scroll before I find the Equip_ID with multiple Locations for the date range;

                          Equip_ID  |  Location  |  Date

                          36794  |  0614  |  11/2013

                          36795  |  0256  |  11/2013

                          36796  |  0778  |  11/2013

                36797  |  4331  |  11/2013

                36797  |  0287  |  11/2013

                          36798  |  0024  |  11/2013

                          36799  |  8788  |  11/2013

                          36800  |  3455  |  11/2013

                          36801  |  0074  |  11/2013

                          36802  |  0364  |  11/2013

                          36803  |  6321  |  11/2013

                           

                          I'm trying to get to the point where I only see this, instead;
                           

                          Equip_ID  |  Location  |  Date

                36797  |  4331  |  11/2013

                36797  |  0287  |  11/2013

                           

                      

                • 5. Re: Trying to report unique instances of duplicates after constraining find
                  philmodjunk

                       To repeat from an earlier post, Just keep the sub summary layout part. Delete the Body layout part and you won't get thousands of rows of data.You'll get just the sub summary rows.