1 2 3 Previous Next 42 Replies Latest reply on Aug 7, 2017 12:15 AM by donolee

    Easy Way to check for a duplicate

    alexissindicic

      Hello Filemaker (awesome) community,

       

      i've read a lot of topic in this forum about finding duplicates values and i merely found a way to import unique data (using a processing table and a search function).

       

      Right now i need to be able to find if a record (a website) is already in my database and flag it if it is the case.

       

      My idea would be to have a function that does this :

       

      1. get the field i want to check for duplicate and put it into a variable
      2. open a new window and do a search of this term (though variable) in the database
      3. If result is >0 then i have a duplicate
      4. Otherwise it is a new record

       

      Do you think it is smart ? is there an easier way for this check ?

       

      Many Thanks for your help !

        • 1. Re: Easy Way to check for a duplicate
          user19752

          Using ExecuteSQL() may be some kind of "easy" way, since it does not need any window nor relation.

          • 2. Re: Easy Way to check for a duplicate
            donolee

            Hi alexissindicic


            Your idea is good. You will get the result.

             

            My suggestion would be:
            1. Import in the same table as creation of new records

            2. New Field in the table to check duplication ( FieldName: FlagDuplicate)
                 FlagDuplicate  = Auto Enter Calculation on creation of value, if it is more than once, set to "1" else, set empty.


                 SQL inside Calculation of FlagDuplicate ("1" if found duplication of data)

                 If ( GetAsNumber(ExecuteSQL ("Select COUNT(*FieldName) From *Table WHERE FieldName = ?" ; "" ; "" ;      FieldName))> 0 ; "1" ; "")

             

            3. Delete Duplication
                 Perform find where FlagDuplicate = "1"
                 Delete Found Records.

             

            *Can be done using 1 script only - Import, check and delete duplication.

            • 3. Re: Easy Way to check for a duplicate
              alexissindicic

              Hey user19752

               

              I am not really good in SQL (never used it )

              • 4. Re: Easy Way to check for a duplicate
                alexissindicic

                Wooow, Looks great indeed thanks donolee i'll try it

                • 5. Re: Easy Way to check for a duplicate
                  fmpdude

                  Assuming you had a table called "USERINFO", you could find duplicate first names easily, like this:

                   

                  SELECT USR_FNAME FROM USERINFO GROUP BY USR_FNAME HAVING COUNT(*) > 1

                   

                  Note that this question has been asked and answered many times on the forum. Try searching before posting.

                  1 of 1 people found this helpful
                  • 6. Re: Easy Way to check for a duplicate
                    TomHays

                    Before SQL was available we used a self-join.  (The table joined to another table occurrence of itself.)

                    For this technique to work, your table will need a unique ID field.

                     

                    MyTable       MyTable__Dups

                    -----------       --------------------

                    ID

                    website <--=--> website

                     

                    In the relationship, leave MyTable__Dups unsorted so that the match records will be listed in order of data entry.

                     

                    Create a calculated field in the table.

                     

                    cDuplicateFlag = Case(

                      ID = MyTable__Dups::ID; 0;

                    1

                    )

                     

                    Whenever you want to search for duplicates, search on the cDuplicateFlag field for 1.

                     

                     

                    Edit:

                     

                    While you could make cDuplicateFlag an auto-enter calculation instead, it would not be robust against deletions of the original record.  When it is an auto-enter field, if you delete the original record, all later records will still be regarded as duplicates and you will wind up deleting every instance of the website on the next duplicate purge.  Keeping it an unstored calculation will ensure that the first instance of the website will always be considered the original one to be preserved.

                     

                    If you ware sure that the first instance of the website will never be deleted, then making it an auto-enter calculation would give better performance when you do a Find on the field.

                     

                     

                    -Tom

                    1 of 1 people found this helpful
                    • 7. Re: Easy Way to check for a duplicate
                      TomHays

                      If you want to explore the scope of your problem without doing any scripting, adding fields, or calculations...

                       

                      Enter Find mode.

                      Go to the field.

                      Type in an exclamation point. (That is the special character that means "find duplicates".)

                      Perform Find.

                       

                      Sort by the field so that duplicates are grouped together.

                       

                      If you are into scripting, you can loop over records in this found set.

                      Keep track of each unique record you encounter.

                      Delete (or mark or memorize ids for) subsequent records with that value.

                       

                      -Tom

                      1 of 1 people found this helpful
                      • 8. Re: Easy Way to check for a duplicate
                        dale_allyn

                        You may also like to look at this post for a novel way to search for duplicates. There's a demo file and good documentation worth exploring (for some applications).

                         

                        HTH

                        1 of 1 people found this helpful
                        • 9. Re: Easy Way to check for a duplicate
                          fmpdude

                          That's probably the easiest way in FMP as Tom is describing.

                           

                          Just be careful not to "delete all" or you'll delete your duplicates AND your original data value! That's why a script is generally used in this context.

                          1 of 1 people found this helpful
                          • 10. Re: Easy Way to check for a duplicate
                            RickWhitelaw

                            If result>1 indicates a duplicate.

                            1 of 1 people found this helpful
                            • 11. Re: Easy Way to check for a duplicate
                              alexissindicic

                              Hey Tom !

                               

                              This way of using the "!" is so great thank you very much !

                              I will also try to understand the SQL function and use it, i think that it may help me a lot in the future

                              • 12. Re: Easy Way to check for a duplicate
                                user19752

                                Rereading the first post, you seems want to check existence of a value in a field (before making duplicated records), not to check duplicated records.

                                 

                                Such case, using relation

                                global = key field

                                then put all fields from related (right side table) on layout based on global (left side table)

                                you enter value on global, other fields values appear if already exist a record for the value.

                                If not appear, you can continue entering other fields with allowing create record on right side of the relation.

                                (The relation can be self relation)

                                • 13. Re: Easy Way to check for a duplicate
                                  alexissindicic

                                  Ok so i'm trying to execute donolee  SQL method (after all if i need to become better at it !)

                                   

                                   

                                  Context :

                                  In order to understand the formula i'd like to do a simple exercise using this function : retrieve the number of rows of an external table (Outreach_Linkreators) from where i am (i am actually in another table called Outreach_linkreators_processing_table) in a Dialog box.

                                   

                                  donolee told me to use :

                                   

                                  If ( GetAsNumber(ExecuteSQL ("Select COUNT(*FieldName) From *Table WHERE FieldName = ?" ; "" ; "" ;      FieldName))> 0 ; "1" ; "")

                                   

                                  So i "translated" it into

                                   

                                  If (GetAsNumber(ExecuteSQL ("Select COUNT(Site_name) From Outreach_Linkreators WHERE Site_name = test" ; "" ; "" ; Site_name))> 0 ; "1" ; "")

                                   

                                  When i try to validate the formula it shows an error on the last FieldName saying that the table could not be found.

                                   

                                  So i wonder what mistake i could have make...

                                   

                                  I played a bit with the function and ended with this one that i could put into a dialog box to see if it works

                                   

                                  GetAsNumber(ExecuteSQL ("Select COUNT(Site_name) From Outreach_Linkreators WHERE Site_name = test" ; "" ; "" ))

                                   

                                  This function seems to be valid ... but it displays a ? instead of a 1 as it should (there is only 1 Site_name called test in my database)

                                   

                                  Do you see where i'm wrong ?

                                  • 14. Re: Easy Way to check for a duplicate
                                    user19752

                                    Text literal in SQL should be quoted as

                                    Select COUNT(Site_name) From Outreach_Linkreators WHERE Site_name = 'test'

                                     

                                    using ? is pathing parameter, keep it as is, but field name need table name in some context.

                                    If (GetAsNumber(ExecuteSQL ("Select COUNT(Site_name) From Outreach_Linkreators WHERE Site_name = ?" ; "" ; "" ; Outreach_Linkreators::Site_name))> 0 ; "1" ; "")

                                     

                                    2 of 2 people found this helpful
                                    1 2 3 Previous Next