4 Replies Latest reply on Jul 27, 2011 11:14 AM by dcbriscoe

    Showing the first duplicate record only

    dcbriscoe

      Title

      Showing the first duplicate record only

      Post

      Please can someone help? I'm sure there is a very easy solution to this problem, but I just can't find it. I'm running FilemakerPro 10.0v3 on a Mac (10.6.8).

      What I need to do is to extract the first record from a specific site from a report that includes duplicates from the same site, so that I can produce GIS data.

      I can get the items table to extract all the relevant occurences into a report, together with the easting & northing data for the relevant site from a separate table, but what I can't then get the report to do, is to show ONLY the first time that the site occurs.

      There are loads of scripts out there to delete duplicate records, but I don't want to do that. I just want to hide the other 2-25 records from the same site. I have tried adapting the delete scripts, but they are not giving me the answer I need.

      Up to now, I have been exporting the whole report into Excel and filtering it there, but I am unable to check whether Excel has done it correctly (I have found flaws from time to time) and would prefer to do it in FMP. Also it's getting quite big to handle in Excel.

      All suggestions most gratefully received!

      Yours ever

      Diana

        • 1. Re: Showing the first duplicate record only
          philmodjunk

          There are several options you can use.

          One is to use a list value report where a sub summary part replaces the body. If the sub summary part's "when sorted by" setting specifies the field that stores the value that identifies each record as a possible duplicate, you can put your data fields in this sub summary part and sort your records by this field. Because you no longer have a body layout part, the sub summary "condenses" your list of records down to just one for each value in the "sorted by" field.

          Another option is to use one of those scripts that deletes duplicates and replace the delete record step with an omit record step.

          There are also script examples you can find that "mark" either the duplicates or the first records by setting a value in a field. In those cases, you can then perfrom a find to find just the records that were/were not marked in this fashion.

          • 2. Re: Showing the first duplicate record only
            dcbriscoe

            Dear Phil,

            Many thanks for the quick reply.

            Sorry, I am having a senior moment -- I haven't come across this "sub summary" item before. Is there a tutorial in the Knowledge / Forum which explains how to do it?

            I did try using a "mark" and also a "delete duplicates" script, but all the site entries came up as duplicates and I couldn't get it to show a unique one. (Part of the trouble is that I have lots of different items from the same site, so the first type in the main table is different from the specific type that I am trying to extract and then refine to a single reference to that site -- hope that makes sense.)

            Many thanks

            Yours ever

            Diana

            • 3. Re: Showing the first duplicate record only
              philmodjunk

              Determining what combination of values makes the records "duplicates" is a key part of the process. You may need to define a field that combines (concatenates) the values of all the relevant fields into one field so that you can sort on this field to group duplicate records.

              The calculation might look like this: Field1 & " " & Field2 & " " & Field3...

              or you can use the list function: List ( Field1 ; Field2 ; Field3.... )

              A sub summary is a layout part you can add to your layout. Enter layout mode, select Part setup and then click the create button and you'll get a dialog where you can add a sub summary part to your layout.

              Here's a tutorial link here in the forum on summary reports. It includes a step by step near the end for creating a layout where the body part has been replaced by a sub summary layout part.

              Creating Filemaker Pro summary reports--Tutorial

              It's an old link, so posting responses to it won't automatically pop it back up into recent items. Thus, I can easily miss any such responses. Instead, post such questions back here and I'll be able to see and respond more easily.

              • 4. Re: Showing the first duplicate record only
                dcbriscoe

                Dear Phil,

                Thanks for the link -- I have downloaded the tutorial and will have a go at working through it. I may not have a chance to try it for a couple of days, so please excuse me in advance if I go quiet for abit and then come back.

                Many thanks again

                Yours ever

                Diana