Peter 'Happy' Thomas (happypete) wrote,
Peter 'Happy' Thomas
happypete

Progress

Requirements: See last post.
Concept of operations:
  1. Retrieve contact information from Outlook store.
  2. Create "link table" to associate contacts to numbers, preserving information about which type of number it was.
  3. Populate link table from contact data.
  4. Create query to filter phone numbers that appear only once.
  5. Collect information about which numbers to delete.
  6. Make a backup of original contacts.  (Yes, gentle reader, I did think of this before I actually touched my "live data." Data corruption is how we got in to this mess to begin with. I don't want to make it worse.)
  7. Make updates to Outlook store
  8. Rejoice.

Elaboration:  Item 5 suggests some sort of user interface, a form to present "candidate" bogus numbers

Status: 

  1. Retrieve contact information from Outlook store
    Linked to Contacts--this was actually trivial.
  2. Create "link table" to associate contacts to numbers, preserving information about which type of number it was.
    My first UNION query:

    SELECT [Display Name], Phone As [Number] , "Phone" As [Type] From Contacts Where Phone is Not null and [display name] is not null
    UNION ALL SELECT [Display Name], [Mobile Phone], "Mobile Phone" From Contacts Where [Mobile Phone] is Not null and [display name] is not null
    UNION ALL SELECT [Display Name], [Pager Phone], "Pager Phone" From Contacts Where [Pager Phone] is Not null and [display name] is not null
    UNION ALL SELECT [Display Name], [Home2 Phone], "Home2 Phone" From Contacts Where [Home2 Phone] is not null and [display name] is not null
    UNION ALL SELECT [Display Name], [Assistant Phone Number], "Assistant Phone Number" From Contacts Where [Assistant Phone Number] is not null and [display name] is not null
    UNION ALL SELECT [Display Name], [Business Fax], "Business Fax" From Contacts where [Business Fax] is not null and [display name] is not null
    UNION ALL SELECT [Display Name], [Home Fax], "Home Fax" From Contacts Where [Home Fax] is Not null and [display name] is not null
    UNION ALL SELECT [Display Name], [Other Fax], "Other Fax" From Contacts Where [Other Fax] Is not null and [display name] is not null
    UNION ALL SELECT [Display Name], [Telex number], "Telex Fax" From Contacts where [telex number] is not null and [display name] is not null
    ORDER BY Number;

    [n.b. the ALL keyword is not required, because my [Type] field and the implicit knowledge that [Display Names] will should guarantees uniqueness among the results of the UNION clauses, but Bill Gates the documentation assures me that ALL is faster because it avoids even checking for duplicates.]  Have to think about Display Names potentially not being unique--it's guaranteed in Outlook Address Books, but not in IPM.Contact items.

  3. Populate link table from contact data.
    The populate table is a trivial 1-1 append from the UNION.
  4. Create query to filter phone numbers that appear only once.
    The duplicate detection is a little weirder, but actually not horrible; not my first "inner query," but I haven't actually had a use for HAVING before :

    SELECT NameToNumber.[Phone Number], NameToNumber.LinkID
    FROM NameToNumber
    WHERE (((NameToNumber.[Phone Number]) In (SELECT [Phone Number] FROM [NameToNumber] As Tmp GROUP BY [Phone Number] HAVING Count(*)>1 )))
    ORDER BY NameToNumber.[Phone Number];

  5. Collect information about which numbers to delete.
    Now I have to start thinking. Hard. Time to go to my real work.
  6. Make a backup of original contacts.
  7. Make updates to Outlook store
  8. Rejoice.
    May be while before rejoicing>
Subscribe

  • Farewell

    photognome died of heart failure yesterday. I don't have words right now.

  • My tweets

    Tue, 11:00: Pulling a full 250 KW and over 1,000 miles per hour charge rate at the new Reston teslamotors #SuperCharger. @ Wieh……

  • My tweets

    Fri, 19:57: Mixed meat meatballs, sauteed vegetables, loaded mashed potatoes with da_valentine and friends @ Corolla, North Car……

  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 1 comment