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

    Searching for Duplicates

    Jon1009

      Title

      Searching for Duplicates

      Post

      Hello,

       

      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)

       

      Thanks

      Jon

        • 1. Re: Searching for Duplicates
          ninja

          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.

           

          Loop

          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...

          EndLoop

           

          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
            Jon1009

            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.

             

             

            Loop
            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
              fitch

              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
                Jon1009

                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

                 

                Cheers

                 

                Jon

                • 5. Re: Searching for Duplicates
                  Jon1009

                  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 :)