9 Replies Latest reply on Mar 27, 2011 5:26 PM by DonWest

    Still not getting "Delete Duplicates" script just right

    DonWest

      Title

      Still not getting "Delete Duplicates" script just right

      Post

      Followed answer ID 3441, but issue is in the present db the first 3 records are dupes, and the script correctly marks only #2 and #3 with an "X" in the "Mark" field, but then places an "X" in every "Mark" field thereafter. I think I must be missing something on how to tell it that it needs to go to the next set of duplicates and examine/mark.

      So here's the scenario.....work order numbers for technicians (text field called "Ticket" containing numbers and letters), this is a unique number, but as the daily work order report may show the same order number from day to day until it's closed, there are numerous duplications of the "Ticket" field, and I want to eliminate them.

      Followed script as:

      Show All Records

      Sort Records [Restore, No Dialog] sort by Ticket in Ascending order

      Go to Record/Request/Page [First] 

      Replace Field Contents [No dialog, 'Mark', ""]

      Loop

      Set Field ['Global','Ticket']

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

      If ['Global' = 'Ticket']

      Set Field ['Mark', "X"]

      Else

      Set Field ['Global', 'Ticket']

      End If

      End Loop

      Perform Find [Restore] Find records when 'Mark' = "X"

      It's pretty much word for word  of the script on the help page, but if you follow what I'm seeing...these records for example

      1234

      1234

      1234

      6789

      6789

      etc

      etc

      The script leaves the first 1234 'Mark' empty and places an "X" in the next two as the script is requesting. But then it places an "X" in 'Mark' for every record thereafter. What am I missing in the script to have it move on to the 6789, decide which of those are dupes, then move on to the next set, etc.?

      THANKS

        • 1. Re: Still not getting "Delete Duplicates" script just right
          raybaudi

          Show All Records
          Sort Records [Restore, No Dialog] sort by Ticket in Ascending order
          Go to Record/Request/Page [First]
          Replace Field Contents [No dialog, 'Mark', ""]
          Set Field ['Global','Ticket']

          Loop
              Go to Record/Request/Page [Next, Exit after last]
              If ['Global' = 'Ticket']
                 Set Field ['Mark', "X"]
              Else
                 Set Field ['Global', 'Ticket']
              End If
          End Loop

          Perform Find [Restore] Find records when 'Mark' = "X"

          • 2. Re: Still not getting "Delete Duplicates" script just right
            DonWest

            I don't know where this is getting glitched. Both scripts act identical, and I now noticed that when "Mark" and "Global" are blank to begin the script, the script returns a "no records match this request" error, and there is no "X" in any "Mark" field. When I run the script the second time, the first record's "Mark" is blank, but not only the duplicates of that, but all subsequent records receive an "X" in the "Mark" field. Exactly the same with the original script I tried, plus the one you posted above. I've been reading through the script and trying variations (and using a Halt command in various places to see what happens in the script at a given point). The way it's written it should return the expected result, but I'm not sure why it treats all subsequent records as duplicates (it's not taking the first of the new set as a baseline and comparing the next records from there)....

            • 3. Re: Still not getting "Delete Duplicates" script just right
              raybaudi

              Remember that Global stands for a real Global field, not only so named... go to check that really it is a global field.

              P.S.: with recent versions of FileMaker, you could avoid to create a global field, using instead an $var.

              Show All Records
              Sort Records [Restore, No Dialog] sort by Ticket in Ascending order
              Go to Record/Request/Page [First]
              Replace Field Contents [ No dialog; YourTable::Mark ; "" ]
              Set Variable [ $var; Value: YourTable::Ticket ]

              Loop
                  Go to Record/Request/Page [ Next, Exit after last ]
                  If [ $var = YourTable::Ticket ]
                     Set Field [ YourTable::Mark ; "X"]
                  Else
                     Set Variable [ $var; Value: YourTable::Ticket ]
                  End If
              End Loop

              Perform Find [Restore] Find records when 'Mark' = "X"

              • 4. Re: Still not getting "Delete Duplicates" script just right
                DonWest

                I'll try the new script you posted, but as for "global" I'm getting lost. The FileMaker help ID 3441 states to create a field called "global" and states to make it the same type as the comparing field (i.e. "ticket"). In this case a text file. If I opt for global under storage options and run the script it puts the same value in every record under the "global" field, not a matching comparator against ticket (which is to say if the ticket is "ABC123" then every global field entry becomes that. This isn't what I am seeking. There are 300+ records, with many duplicates, but maybe only 140 unique records, the rest are dupes. I keep looking but can't get this solved yet.

                Hold on a minute....I just went back to it and see something I didn't quite follow the first time. When making the "Global" field storage options to "global" and running the script, the records I see after the final "perform find" show the global field with all the same vaule, which is what confused me. The found set is not the complete set of records, but appearently the true duplicates, as when I "show all" and sort, I see the "X" placed in duplicate records, while the original record remains without the "X". SO....it looks like this does work, I think I need to read up on the "global" definition so that I understand how it placed the data in the field. Although as I don't really need to see that field after the script, perhaps it's best to hide it in the layout, just let it operate? I'd still like to try your other script and see if it's a better automation. Meanwhile, thanks for the tips, I finally think I'm getting somewhere....

                • 5. Re: Still not getting "Delete Duplicates" script just right
                  DonWest

                  Yes, I think it's SOLVEDSmile I don't think I fully comprehend the "global Storage" function, and that's where I lost it. I had used FM Pro 5.5 for many years, having created a very nice db for a friend's restaurant/take-out order. I created (by today's standard) a script that deleted duplicates in his customer database, which ultimately worked well. I just re-launced the FM Pro 5.5 and examined field options. Indeed, no global storage in that version, which is why it probably kept eluding me here; the global option in 5.5 is actually a "field type definition" something I hadn't used before and therefore probably just ignored it, now I am beginning to learn its value.. I finally upgraded to FM Pro 11 a little while back, and this is my first real scripting need for deletion of duplicates. If I'm beginning to understand what "global" does....it will paste the value into every record identically, which is why when the script is done, I see the value of the last record's ticket number in every global entry. If I could watch the script in slow motion, I assume that value changes as we go from one record to the next. As soon as the script gets to a different ticket number, that value becomes the new global entry throughout the file, correct? It does this until every record is checked, and then stops, leaving the global value at the entry of the last ticket number.

                  All in all, I thank you for putting up with an outdated db writer :) I will try to get up to snuff on the latest, and learn its amazing functions. FM Pro has always been a powerful program (my eye doctor's entire patient record is based on it, as well as a friend's recording studio's invoicing and recording management db).

                  • 6. Re: Still not getting "Delete Duplicates" script just right
                    LaRetta_1

                    I highly suggest against marking a field (known as flagging).  It will break in multi-user mode.  And you will always have to 'remove' the mark through all records.  As your number of records grows, this can slow you down.

                    Also, you shouldn't have to Show All Records and loop through all records; instead perform a find for ! in the Ticket Number field.  This will give you a found set of duplicates.  If you delete your duplicate as you loop, it will also be faster than marking the duplicates then searching again for them and deleting them.

                    Even faster still, and more reliable, is to use a relationship.  However, I would rather address the underlying issue here ... you will always be dealing with duplicates as it is.  Simply, records should not be created only to then be deleted.  It indicates that your process and structure might not be optimum.   Instead of cleaning up after the fact, you might consider how NOT to create the duplicates or NOT deleting them at all.

                    It sounds like you are missing a table, specifically:

                    this is a unique number, but as the daily work order report may show the same order number from day to day until it's closed, there are numerous duplications of the "Ticket" field, and I want to eliminate them.

                    You might consider a Tickets table (unique, auto-enter, FM-generated TicketID) and then a WorkOrders table (which SHOULD have the TicketID in many different records, as work is done on a specific TicketID).  Your report would then be in Work Orders and you would have a leading part based upon TicketID.  You don't need the body at all. 

                    But I get ahead of myself ... can you explain more of your situation or provide a link to your file or relational graph so we can see your setup?  Regardless, please reconsider your method of marking records.

                    • 7. Re: Still not getting "Delete Duplicates" script just right
                      DonWest

                      Thank you, LaRetta for your observations and comments. I'll look those over. However, "tickets should not be created only to then be deleted" is a valid point, but I do not control the entire process. I'm working on a special report from Excel files generated by the Dispatch department. They create these open ticket reports daily, and as days progress without a ticket being closed, it appears on a subsequent day's report, thus causing the duplication when I convert to FM Pro. This is, for the time being, a short term project I'm working on. The Excel files do not give me the lattitude in data manipulation that a true database does, thus the conversion to FM. I'm being asked to examine work orders involving specific equipment types, geographic locations of the tickets, and assigned technicians...FM has proven a valuable asset in finding and sorting the requested data.

                      I will look more heavily into your suggestions, and see if I can't ultimately create a template in FM for the Dispatch system to use. I know they have been having limitations and issues with the software that's presently used to create the work orders and reports....

                      • 8. Re: Still not getting "Delete Duplicates" script just right
                        LaRetta_1

                        You may not be able to control the entire process but you can control what you do with the data.

                        Since you don't need all of the records from Excel then don't import them to begin with.  Go to File > Manage > Database and the Work Order table.  Select the Options button for the Ticket Number field and select the Validation tab.

                        Validate data in this field:  select ALWAYS.   Uncheck 'allow user override'

                        In Require:  Select 'unique'

                        Now when you import, specify 'add' all as new records.  Because you have validation on the Ticket Number that it be unique, if the Ticket Number already exists in your Work Orders table, it will not import again and you will end up with small list of unique Ticket Numbers.  You don't even need to show all records before the import - validation automatically applies to all records in the table.

                        Validation will fail on incoming Excel records which are duplicates and they simply won't be imported.  In this way, you will always have only one of every ticket number and it will be much faster than any other method.

                        Note: You must delete all duplicates before you begin this process.

                        • 9. Re: Still not getting "Delete Duplicates" script just right
                          DonWest

                          Very interesting, thanks. Yes, there's lots about the power of FM that I'm not up to speed on. I will have more Excel data to work with later in the week, I'll try your validation method. It will certainly speed up the process....