8 Replies Latest reply on Oct 22, 2008 6:39 AM by TSGal

    Need script to find records with duplicates in two fields and delete one and then repeat

    Karen_1

      Title

      Need script to find records with duplicates in two fields and delete one and then repeat

      Post

      Phew!!  It's been a long time since I wrote a script and never with 8.5. 

       

      I need a script to find records with duplicates in the position number and count fields that will then delete the content of the count field and then repeat for all other records with duplicates in the position number and count field. 

       

      I am having some success with doing this the first time, but my find keeps on finding the same records including the duplicated I just cleaned up.

       

      Any help?

        • 1. Re: Need script to find records with duplicates in two fields and delete one and then repeat
          TSGal

          Karen:

           

          Thank you for your post.

           

          I'm having a little difficulty understanding your entire post, but this should get you pointed in the right direction.

           

          There are a couple of ways to find duplicate records.  It sounds like you are finding the duplicate records, but since you keep finding the same records, it doesn't sound like you are removing the records the first time.  If you want to keep the records but don't want them to be found again, then create a "flag" field so your script can update it so you don't find it the next time.  Does that make sense?

           

          It may help to know exactly what you are doing.  Send your current script, what is working, and what isn't working.  We can work on it from there.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Need script to find records with duplicates in two fields and delete one and then repeat
            Karen_1
              

            Thank you,

             

            I would prefer to not add a field unless I have to.  This is one of many databases that is used to project our personal services costs.

             

            Problem:  Some positions because of doublefills and job shares have more than one record and each record has a count of "1" in the count field.  Because I can't have a count of more than 1 per position, I need to delete the "1" in the count field in subsequent duplicate records.

             

            Script: 

            Enter Browse Mode

            Show All Records

            Perform Find [Restore]     find ! in POSNO and 1 in COUNT fields   Is the error in the find?

            Sort Records [No dialog] - sorts on POSNO

            Go to Record/Request/Page [First]

            Go to Field [POSITION DATA BASE::COUNT]

            Select All

            Clear [Select]

             

            This works for the first record.  If I repeat or loop it, it drops the record cleaned up last time, but finds the duplicate record that was cleaned up last time that now doesn't need to be cleaned because the two records are duplicated in both fields (POSNO and COUNT).  Then it deletes the COUNT in the second of the two records.  With each repeat it drops only the one record just cleaned up and not the pair of records.  So what I'll end up with is a list of positions with no position count.

             

            Help!!

             

            • 3. Re: Need script to find records with duplicates in two fields and delete one and then repeat
              TSGal

              Karen:

               

              Thank you for the clarification.

               

              Clear [Select]

               

              ... will only clear the contents of that one field for that one record.  Add the following step:

               

              Replace Field Contents [No dialog;POSITION DATA BASE::COUNT; Current contents]

               

              When you select this script step, check the option "Specify target field".  select the "POSITION DATA BASE::COUNT" field and click OK.  Next, check the option "Perform without dialog".  Finally, click the bottom "Specify" and make sure the radio button is selected for "Replace with "current contents"".

               

              So, after you clear the value in that one record, you are replacing the contents across all records with the current contentsof the COUNT field (which is empty) across the entire found set of records, and don't remind me.

               

              Try this out, and see if this is what you want.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: Need script to find records with duplicates in two fields and delete one and then repeat
                Karen_1
                  

                Thank you.  The problem with that solution is my listing has many sets of position numbers and this solution would delete all position counts.  I only want to remove the second count for each set of duplicate position numbers.  I am convinced the problem is in my find.  For when I find again I shouldn't see any of the first set of duplicate positions records for which I deleted the count in one of them, but I do. 

                 

                 

                • 5. Re: Need script to find records with duplicates in two fields and delete one and then repeat
                  Karen_1
                    

                  I've solved the problem by adding a character to the end of the position number in the record which I've deleted the duplicate count for now.  That way the find doesn't find duplicates on the position numbers and I've eliminated the duplicate count.

                   

                  Thank you anyway.

                  • 6. Re: Need script to find records with duplicates in two fields and delete one and then repeat
                    TSGal

                    Karen:

                     

                    We're getting closer...  :-)

                     

                    Okay...  to remove the second count for each set of position numbers, we'll modify your script a bit more.

                     

                    Enter Browse Mode []

                    Perform Find [Restore]

                    Sort Records [No dialog, POSNO]

                    Go to Record/Request/Page [First]

                    Set Variable [$counter; Value: 1]

                    Set Variable [$key; Value: ""]

                    Loop

                       If [$key<>POSNO]

                          Set Variable [$key; Value: POSNO]

                          Set Variable [$counter; Value: 1]

                       Else

                          Set Variable [$counter; Value: $counter + 1]

                       Endif

                       If [$counter = 2]

                          Go to Field [POSITION DATA BASE::COUNT]

                          Select All

                          Clear [Select]       

                       Endif

                       Go to Record/Request/Page [Next; Exit after last]

                    End Loop

                     

                    ==========

                     

                    We find the duplicate records as before, sort them and put them in order.  We initialize a couple of variables and enter a loop.

                     

                    We check to see if the variable $key is equal to POSNO.  If not, then we set the Variable $key to POSNO and reset the counter to 1.  Otherwise, we increase the counter by one.

                     

                    If the counter is 2 (second occurrence), we clear the contents of that field.  Either way, we move to the next record, and evaluate again.  Again, only the second record of a set of POSNO entries will have the COUNT removed.

                     

                    Once the end of the file is reached, we exit the loop and the script ends.

                     

                    Let me know if this is more what you want.

                     

                    TSGal

                    FileMaker, Inc. 

                    • 7. Re: Need script to find records with duplicates in two fields and delete one and then repeat
                      Karen_1
                        

                      Perfect!!!!  Thanks a million.

                       

                      Is there a book that would help this dummy with scripts?

                      • 8. Re: Need script to find records with duplicates in two fields and delete one and then repeat
                        TSGal

                        Karen:

                         

                        I'm glad you got it to work.

                         

                        I have seen many books on FileMaker Pro, and most of them cover scripts.  I don't remember if any of them stood out more than the other.

                         

                        Can anybody out there recommend a good book(s) for FileMaker scripts?

                         

                        TSGal

                        FileMaker, Inc.