5 Replies Latest reply on May 10, 2010 4:53 AM by Jon1009

    Searching for Duplicates



      Searching for Duplicates




      Iam new to filemaker and been trying to do this for a couple of days with no joy.


      I want to add a button on my form which checks the text of a field i just entered agaisnt the rest of the datebase for duplicates and if found changes the value of the field i just entered automatticly to another value


      I guess it would be some sort of IF statement?


      (Iam using fm10 on vista)




        • 1. Re: Searching for Duplicates

          Howdy Jon,


          Trap the value, trap the UniqueID,  do a find, then count the number of found records.  If it's more than 1, find the record you started with and change the value.



          SetVariable [$n ; Value: $n+1]

          SetVariable [$text ; Value: YourTextField]

          SetVariable [$ID ; Value: YourUniqueIDField]

          Enter Find Mode

          SetField [YourTextField ; $text ]

          PerformFind [ ]

          Exit Loop If ( Get (FoundCount) = 1)

          Enter Find Mode

          SetField [YourUniqueIDField ; $ID ]

          SetField [YourTextField ; $text & "-" & $n ]       or whatever you're incrementally adjusting it to...



          Think it through and understand the concepts, then adjust as needed to fit your design.  If you have few records, this should work pretty quickly.  If you have a large record count, we can work on speeding it up a bit by playing with the find steps.  Remember that finding '!' searches for duplicates...you can use that tool as well.

          • 2. Re: Searching for Duplicates

            Hey Ninja thanks for your help


            Ive been playing around with that for a few hours but cant seem to get it to work.


            when I run the script says "The provided fine criteria is not valid. enter a valid request before proceeding" and if i click continue it just keeps counting ie MyText-1-2-3-4. I think i entered the code right.



            Set Variable [ $n; Value:$n+1 ]
            Set Variable [ $text; Value:test::Text ]
            Set Variable [ $ID; Value:test::ID ]
            Enter Find Mode [ ]
            Set Field [ test::Text; $text ]
            Perform Find [ ]
            Exit Loop If [ Get ( FoundCount ) =1 ]
            Enter Find Mode [ ]
            Set Field [ test::ID; $ID ]
            Set Field [ test::Text; $text & "-" & $n ]
            End Loop



            • 3. Re: Searching for Duplicates

              Could you give an example of the text you might enter, and what you would change it to if a duplicate is found?


              Meanwhile, another possible approach is to make a relationship of this table to itself based on the testText field. In your script you could then test for Count(related::testText). Makes your graph one TO more messy, but your script a lot less messy.

              • 4. Re: Searching for Duplicates

                Hello Fitch,


                What I would like to do is select the 5 Letter client code from a drop down for example SMG01 I have about 150 different codes at the minutes.

                which would then updates customer id field to SMG01-100510-01  (Client Code - The date - 01)  (which i have working now) the thing I cant seem to do is to check if SMG01-100510-01 exists then changes the 01 to 02 etc.


                the 2 fields iam using to create the code are Client Code - Date


                ive tried messing around with if statments but cant figure it out





                • 5. Re: Searching for Duplicates

                  Seem to have got it working not sure if its the best way to do it.


                  What ive done is created 2 extra fields Sample_Code and Sample_Number_Today attached this script to the dropdown menu of the client code


                  Set Field [ Logbook::Sample_Code; Logbook::Client Code & SerialIncrement ( "-000000" ; 10^4 * Day ( Logbook::Date ) + 10^2 * Month ( Logbook::Date ) + Mod ( Year ( Logbook::Date ) ; 100 ))  & "-" ] Set Field [ Logbook::Sample_Number_Today; Count ( Logbook 2::Sample_Code)+1 ]


                  then brought in the Sample_code & Sample Number into Veromara ID


                  Sample_Code & Sample_Number_Today


                  So pretty
                  Sample_Code holds AMG01-100510-
                  And Sample_Number_Today Holds the counts


                  Is this a good way to do it are would you recommend another?


                  Thanks for help :)