Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Removing duplicates

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Removing duplicates

    Ok here is my problem. I am converting A4V7 to A5. The client�s clerks were not the most attentive on entering data and the result is multiple duplicate entries with only slight data variations. The customer table has MANY duplicates ( some with data fields filled, some not ) and the numbers assigned to the customers are used to populate several other tables that are used to link the various tables to a customer summary set for a summary display. I define duplicates as first, middle, last name for this example. When I do a lookup by Last, First, Middle I may get the blank customer data or the filled data. I need to eliminate the empty data customers from the customer table and update the other tables with the Good customer number.

    When I run the �mark duplicates� genie I get a display that may show a master with or without other populated data fields and the �duplicate� with exactly the opposite filled or unfilled data. Ex. One side will have DOB and other will be blank.

    When I run the mark duplicate genie with Last, First, Middle, I get 1016 duplicates out of 16000+ records. Most of the records are NOT really duplicate i.e. different DOB etc. so it is just time consuming to go thru them unmarking those. But this leaves me with only the marked bad records and no connection to the good records.

    What I need to do is generate a table that has two fields containing the duplicate customer numbers. The client will then print this list out and go thru the table verifying which record is Good and mark the Bad for purging. I will then run a update that will search the auxiliary tables and change the customer number from the Bad number to the Good number. Then I will purge the Bad customers from the table.

    Any advice will be greatly apprciated by myself and the client.

    Don

    #2
    Re: Removing duplicates

    I find .mark_dups2() to be useful in such a case. You'll see all the like records, not just the duplicates of the first instance.

    dim tbl as P
    tbl = table.open("customer")
    tbl.mark_dups("Last + First + Middle")
    tbl.close()

    Then open the default browse (or create a special browse with the desired fields) and filter for marked().
    There can be only one.

    Comment


      #3
      Re: Removing duplicates

      Thanks Stan will try this method


      Don

      Comment


        #4
        Re: Removing duplicates

        Or, from the CP, right click on the table, select Utilities/Delete Duplicates.

        See attached image...

        Delete Dupes.png
        Peter
        AlphaBase Solutions, LLC

        [email protected]
        https://www.alphabasesolutions.com


        Comment


          #5
          Re: Removing duplicates

          Thanks I need to be able to get a list from the dups so the client can filter them for the GOOD records, then I have to make a script to move the good numbers to replace the bad numbers thru an update. once done then I can delete the real dups to clean up the table.

          Comment


            #6
            Re: Removing duplicates

            Don, I spent ages specifying the duplicate testing process.
            It is not as simple as just comparing fields as you know.
            Example;
            Mc Davis
            Mc. Davis
            McDavies
            Macdavies

            Addresses and postcodes or Zip codes can be of great assistance, so the comparison string can be constructed using a whole load of combinations, which can then be compared.

            There are problems with reversed first and last names, as well as optional spellings, so suggest you consider SOUNDEX in the process. Not fool proof, but will identify some of the more obvious ones.
            Then if like me you have to determine which record has the more valuable data attached to it, you will have the fun of combining data.
            In my example the client record had legal attachments, and they were added at different times, so as the application(not mine) allowed duplicates, then Daisy Smith and Daisy Smithe may or may not have been the same person.
            See our Hybrid Option here;
            https://hybridapps.example-software.com/


            Apologies to anyone I haven't managed to upset yet.
            You are held in a queue and I will get to you soon.

            Comment


              #7
              Re: Removing duplicates

              thanks Ted. Yes I am VERY well aware of the complexities of dulpicate removal. I was using thier ID # to try to eliminate the possibility of having dups, but some of these customers have multiple IDs so the client is now verifying IDs every time they come in. In addition those using multiple IDs are reported to the local police (per their request) to be handled by them. Thank goodness all I have to do is collect the data and then let the client find the REAL duplicates to remove.

              I have already written the script to transfer the correct customer number to the transactions (6 tables) that were entered under the duplicate customer so that part was not hard. Using Stan's dups2 I was able to collect the list of dups and now the client is parsing them to get the ones we need to purge.

              TIA

              Don

              Comment


                #8
                Re: Removing duplicates

                Just went through that a couple moths ago with 33000 businesses that unduped came to 13500 businesses and then what to do with the people attached to eac of the dupes where some were also attached to the real as well as the dupes. Took the better part of 3 days to search out 99% and fix em. Client fixed the rest. There were notes everywhere in this mess as well.

                I made it so one would have to absolutely try to mess it up again and it records who did it too.
                Dave Mason
                [email protected]
                Skype is dave.mason46

                Comment


                  #9
                  Re: Removing duplicates

                  And there we have the problem. We know who did it, but getting them to correct it is another problem.
                  I have always produced reports on a weekly basis so that there is at least a chance of correcting say 10 dupes, as opposed to waiting until the end of the year and finding loads and loads.
                  When that happens, the elephant becomes too big to eat and everybody scarpers.
                  See our Hybrid Option here;
                  https://hybridapps.example-software.com/


                  Apologies to anyone I haven't managed to upset yet.
                  You are held in a queue and I will get to you soon.

                  Comment

                  Working...
                  X