14 Replies Latest reply on Dec 16, 2009 1:14 PM by comment_1

    Find Exact Duplicates

    JoeTaureau

      Title

      Find Exact Duplicates

      Post

      I've searched this forum and Google and unfortunately, i didn't find anything regarding finding exact duplicates.

       

      I'm well aware of the "!" symbol to find all the duplicates in FM, but the problem with said symbol is that it's not precise enough.

       

      Exemple: 

       

      Let's say i have a list of entries like this : 

       

      - payment card

      - payment account

      - payment in advance

      - payment card

      - premium

      - premium dollars investment currency

      - premium interest rate

      - premium

       

      If i use the ! symbol, FM will return all of 'em as being duplicates. However, what i want to do is find all the exact duplicates, so he really should just give me both entries "payment card" and "premium".

       

      Is there a way to do this, to find exact duplicates with a script ?

        • 1. Re: Find Exact Duplicates
          comment_1
            

          JoeTaureau wrote:

           If i use the ! symbol, FM will return all of 'em as being duplicates.


          How on earth did you manage that?! What is your field's type, what is the language chosen for indexing, and what level of indexing is being used?


          • 2. Re: Find Exact Duplicates
            philmodjunk
              

            Is the entire text in one field or more than one?

            Is this a number field instead of text?

             

            When I put "Premium" in the text field of one record and "Premium Dollars" in the text field of another, using the ! operator does not find these two records--which is what I would expect.

             

            If this is a single text field and you can confirm (See Manage | Database | Fields) that is a text field and not a number field with text entered into it, you may have a corrupted index. Turn indexing off and then back on (This is the storage tab in field options.) and see if this fixes the problem.

            • 3. Re: Find Exact Duplicates
              ninja
                

              JoeTaureau wrote:

              If i use the ! symbol, FM will return all of 'em as being duplicates.


               

              Howdy,

               

              This may be a misinterpretation of results.

              You get a number of items shown as duplicates...and they may indeed all be...but not of each other.

              If all of the field contents you have listed have exact duplicates of themselves then you'll get a found set of all of the items that have exact duplicates of themselves.  But it doesn't mean that they are all duplicates of each other.

               

              If I type in "Premium", "Premium", "Premium Payment" and "Premium Payment" then search for "!", I get all four.  This doesn't mean that "Premium" = "Premium Payment", just that both unique contents exist multiple times.

               

              • 4. Re: Find Exact Duplicates
                StellaLuna
                  

                Multiple entries in one field can match.  each line within a field is treated independently, like it tries to match in a relationship.  You need to use calculation with Exact() if there is possibility of multiple entries in one field.  Otherwise a field with only the word Premium will match a field with Premium and Regular on next line (in same field).

                 

                So the 'duplicate' using ! doesn't really find duplicates meaning EXACT duplicates.

                • 5. Re: Find Exact Duplicates
                  JoeTaureau
                    

                  Wait a minute... you're all telling me that FM actually do find exact duplicates when using the ! symbol, but he doesn't show 'em one after the other ?

                   

                  So basically, i have a database with 73000 entries in it and when i tried the ! symbol, i had the list (well, part of the 6200 entries found) i mentionned above. So, if i understand correctly, the results shown are NOT showing the duplicates one after the other, but randomly ?!

                   

                  So the "payment account" entry is shown one time on the screen, but there's actually the same exact entry somewhere else lower (or higher), like 6000 entries lower, as an example ?

                   

                  So what i need to do is use the ! symbol and then SORT them alphabetically so they show up one after the other ?

                   

                   

                  • 6. Re: Find Exact Duplicates
                    ninja
                      

                    Howdy,

                     

                    Yes.

                     

                    You've found the duplicates, but they are kept in the same order they were in when you were looking at all the records.

                    {Caveat: Dunno how autosort would affect this}.

                     

                    Do your find for "!", sort by that same field, then let us know what you've found....you may find that "!" did exactly what you thought it would and now you have a list with the duplicates all grouped together.

                     

                    StellaLuna's point is well made also.  I assumed based on the simplicity of the examples you gave that you were not using multiple lines in your fields.

                    • 7. Re: Find Exact Duplicates
                      JoeTaureau
                        

                      @Ninja

                      I did what i thought i had to do/confirmed by you and here's a snippet of what i had (after sorting them by A-Z)

                       

                       

                      Those that doesn't belong there, that are alone, does this means that there's a problem with the index of the database ? I tried to reindex said database and i still get the same results tho... so any ideas why those rogue entries are showing up in the duplicate find ?

                      • 8. Re: Find Exact Duplicates
                        comment_1
                           You probably have another record containing the line "about", but not as the first line of the field - so it will be further down in the sort order.
                        • 9. Re: Find Exact Duplicates
                          philmodjunk
                            

                          I suggest reading Stella Luna's post. I can see were you have text separated by carriage returns and that's the most likely explanation for what you see. The ! operator is finding instances where a line of text matches a duplicate line of text in another record, but the additional lines of text are keeping them from sorting so that the duplicate entries are adjacent.

                           

                          You might need to set up a "self" relation to identify the presence of duplicate records.

                           

                          Link your table to a second table occurrence of itself by your text field:

                           

                          YourTable::YourTextField = YourTable 2::YourTextField

                           

                          Now you can place a portal to YourTable 2 with the field YourTable 2::YourTextField next to your text field and you should find the matching duplicates listed in the portal.

                           

                          Many folks get confused over the terms Table and Table Occurrence. To learn more, click the following link:

                          Table vs. Table Occurrence (Tutorial)

                          • 10. Re: Find Exact Duplicates
                            ninja
                              

                            I think we've found why the search for "!" seemed to acting strangely...now for the big question:

                            When you find the duplicates...what are you tying to do with/to them?

                             

                            From the screen shot you posted, it doesn't seem like you're trying to cull duplicate records...so what is the next step?

                            With your use of text separated by carriage returns, some following tasks may be a bit more complex.

                             

                            With the record count that you've stated previously, it seems like a decent sized job to split that field into related records (which would make finding duplicates a bit more easy/intuitive)...so what are you trying to accomplish and let's find a good path from here to there that works for you.

                            • 11. Re: Find Exact Duplicates
                              JoeTaureau
                                

                              Yeah after some digging, i've found the duplicates somewhere else where there's an entry with multiple sub-entries separated by a ¶. So basically, the ! is doing exactly what it was designed for, it's the way the users enter those entries that causes the problem.

                               

                              Sorry for doubting the dup entry search of FM :P heheh

                              • 12. Re: Find Exact Duplicates
                                comment_1
                                  

                                If you want to find exact duplicates of multi-line entries, you could define a calculation field =

                                 

                                Substitute ( YourField ; ¶ ; "" )

                                 

                                and search for duplicates there.

                                • 13. Re: Find Exact Duplicates
                                  ninja
                                    

                                  @comment:

                                   

                                  This would find fields where all lines are the same and in the same order, yes?

                                   

                                  If the entries are the same but not in the same order it wouldn't count it as a duplicate?

                                  • 14. Re: Find Exact Duplicates
                                    comment_1
                                      

                                    Yes, because in this field all the text is on a single line.

                                     

                                    ---

                                    BTW, I should have been more careful with the word "exact": depending on the language set for the field, "about" and "ABOUT" could be considered duplicates.