7 Replies Latest reply on Sep 20, 2012 2:34 PM by Terri

    Found Set - No Duplicates

    brodlang

      Title

      Found Set - No Duplicates

      Post

      I need a way to create a found set of only unique records.

      E.g: I have 100 records with 10 fields.  I need to make a found set based on only 5 of the fields.  But I don't want any duplicates.  If the data across these 5 fields is different in any way from all the other records, I would have a found set of 100.  But if 10 should have the exact same data across these 5 fields, then my found set should be 91 records.

      These found sets will become "Headings" or parents to the records they originally came from. 

      In another life I could do this simply by inserting the found records into a new table that woudn't allow duplicates.  Does FMP have a similar function, or do you have any suggestions that aren't too technical?  My programming skills are limited.  Doing this in a few different steps is acceptable if necessary.

       

      Thanks.

      BL  

        • 1. Re: Found Set - No Duplicates
          philmodjunk

          Define a text field in your new table as a sixth field along with the original 5.

          Give it an auto-entered calculation such as:

          Field1 & "|" & field2 & "|" & Field3 & "|" & Field4 & "|" & Field5

          Specify a unique values validation and select "validate always".

          Import your records into this new table and your duplicates will be omitted.

          Note that it is also possible to set up a list view layout based on your original table that lists each unique record only once by using a sub summary layout part and removing the body layout part.

          • 2. Re: Found Set - No Duplicates
            raybaudi

            May we assume that this two records are different ?

            Field1.....Field2.....Field3.....Field4.....Field5....Field6.....Field7.....Field8.....Field9.....Field10
               1..........2............3............4............5.........6............6............6............6............6
               2..........2............3............4............5.........6............6............6............6............6 

            with a found set based on the first five fields ?

            • 3. Re: Found Set - No Duplicates
              brodlang

              Thanks, Phil.  I'll try both ways.  It may take me awhile.

              Ray - Yes I agree, those are two different records. A good example.

              • 4. Re: Found Set - No Duplicates
                brodlang

                Phil - YOU ARE AWESOME!  WORKS LIKE A CHARM!

                • 5. Re: Found Set - No Duplicates
                  Terri

                       I have a modification to this question. 

                       I have two tables with a relationship for Style_Number. In the main table I have multiple records for one style number. And each of these records are unique except for that style number. I need to import into the second table only one of these records. It doesn't matter which one, just the first one or whatever the case may be. Either way, if there are 5 records in the first table that have the same style number I only need one of those imported into the second table.

                       Is there a way to do this?

                       Thanks!

                  • 6. Re: Found Set - No Duplicates
                    philmodjunk

                         Option 1:

                         On the second table, open manage | database | Fields and find the Style_number field. Double click it to open Field Options. On the Validation tab, specify unique values and validate always.

                         Then use import records to import all your records from the first table in to the second. The validation settings will automatically filter out the duplicates.

                         Option 2:

                         Since you already appear to have this relationship:

                         Table 1::Style_Number = table 2::StyleNumber

                         Enable "allow creation of records via this relationship" for Table 2.

                         Write a script that loops through all the records in Table 1 with this script step in the body of the loop:

                         Set Field [Table 2::Style_Number ; table 1::Style_number ]

                    • 7. Re: Found Set - No Duplicates
                      Terri

                           Option 1 worked. Thank you!