locked
Identifying multiple contacts using excel and advance find RRS feed

  • Question

  •  

    I know how to change multiple records - but the one questions i have is how to take a listing of email addresses in Excel and use those email addresses to identify the records that need to be changed.

     

    I know how to find a handful of records using the "Advance Find" feature, but is there a way to do it using 100+ email addresses without making 100+ separate criteria for searching?

     

    Thursday, February 28, 2008 2:35 PM

Answers

  • Hey Adi,

     

    10 out of 10 for lateral thinking - I love the approach - I build concatenated bits of code in Excel all the time!  Thanks a million for the approach - just what I am looking for.

     

    I am now using this to reconcile batches of unsubscribes and underliverable email.

     

    I tried this method and just edited 600 records matched against an excel file.  While this approach taxes the client machine's CPU (55%+) - the limitation seems to be on the Page File Usage.  My laptop slowly built up PF Usage up to ~1.9 MB and then maxed out.  So keep an eye on the PF and if it builds too high, shut down the pop-up and the adviced find windows, then start again.

     

    I found that adding the javascript command to my favourites saves time.

     

    [I found I needed to search/replace the '@' symbol in the email addresses for '@'  so the address myemail@domain.com becomes myemail@domain.com.] -> Correction, it works fine without doing this.

     

    Also using the =TRIM() command in Excel helps to clean up the addresses before running the query.

     

    Adi - if you are reading this drop me a mail at fraserm @ bwinc.com (without the spaces) - I'd like to send you a thank you card!

     

    If anybody form Microsoft is reading this - this should certainly be a feature in the next release (but a lot more streamlined than this work around!)

     

    Cheers,

     

    Fraser

     

    Friday, May 30, 2008 12:18 AM

All replies

  •  

    Dear,

     

    you can check SQL Query Analyzer and get the emails etc.. select top 100 / criteria  from table.

     

    Regards,

    Imran

     

    http://microsoftcrm3.blogspot.com

     

    Thursday, February 28, 2008 3:12 PM
    Moderator
  • Is there any easier way, I am unfamiliar with SQL Query Analyzer.

     

    Thursday, February 28, 2008 3:37 PM
  • Hi.

     

    You can reverse engineer the advanced find and build the UI form from a fetch you constructed using the excel data.

    If you have good technical skills apply the following steps: 

     

    Are you ready?

     

    1. Open the advanced find > Press Ctrl + N to open it in a new IE Window.
    2. Construct a simple fetch template for the contact entity with an OR operator. For example
      Attribute -- Operator -- Value
      Email equal dynamics@gicrm.com
      Email
      equal some@one.com
    1. Select each row and add an OR operator to finish the query.
    2. Run the Query
    3. Enter the following code in the address bar and retrieve the fetch.

    javascript:void( alert( document.getElementsByName("fetchxml")[0].value ) )

          4.   Focus the alert > Ctrl A + Ctrl C > paste the result into a notepad.

     

          Here is the result you should get:

     

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

       <entity name="contact">

          <attribute name="fullname"/>

          <attribute name="telephone1"/>

          <attribute name="contactid"/>

          <order attribute="fullname" descending="false"/>

             <filter type="and">

                    <filter type="or">

     

                         <condition attribute="emailaddress1" operator="eq" value="dynamics@gicrm.com"/>

                         <condition attribute="emailaddress1" operator="eq" value="test@gicrm.com"/>

     

                    </filter>

             </filter>

         </entity>

    </fetch>

     

    As you can see the inner <filter type="or"> holds all the conditions of type <condition attribute="emailaddress1" operator="eq" value="dynamics@gicrm.com"/>.

     

    Now use the excel sheet to build all the conditions.

    Here are the steps to building a 100 email conditions:

    1. Put all emails on the 3rd column (C).
    2. put this in the Column B - <condition attribute="emailaddress1" operator="eq" value="
    3. put this in Column D - "/>
    4. Create a simple Concatenate function in Column A – Concatenate( B1 , C1 , D1 )
    5. Drag the result downward so the function will apply to all rows.
    6. Copy the results in Column A to the notepad and construct the fetch like this:

     

    Start:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

       <entity name="contact">

          <attribute name="fullname"/>

          <attribute name="telephone1"/>

          <attribute name="contactid"/>

          <order attribute="fullname" descending="false"/>

             <filter type="and">

                    <filter type="or">

     

    Paste Column A from excel

     

                                </filter>

             </filter>

         </entity>

    </fetch>

     

     

               

                Are you still with me? J , all we need to do now is reverse engineer the fetch into the advanced find form builder

                And run the Query.

     

    1. Open a fresh advanced find form > Ctrl + N > Look for: Contacts > Paste the following code in the address bar.

    The code opens a window where you can paste the FetchXml you created.

     

    javascript:void(new function(){var w=document.open("about:blank","","width=330,height=350,left=150,top=150");w.document.body.innerHTML="<textarea id=ta style='width:300;height:300'></textarea><br><input type=button value=reverse onclick='opener.advFind.FetchXml=document.all.ta.value'/>";})

     

    1. Click on the reverse button > click Find

     

    Seems a lot, you can make it work J.

     

    Good Luck

    Adi

     

    Thursday, February 28, 2008 6:04 PM
  • Hey Adi,

     

    10 out of 10 for lateral thinking - I love the approach - I build concatenated bits of code in Excel all the time!  Thanks a million for the approach - just what I am looking for.

     

    I am now using this to reconcile batches of unsubscribes and underliverable email.

     

    I tried this method and just edited 600 records matched against an excel file.  While this approach taxes the client machine's CPU (55%+) - the limitation seems to be on the Page File Usage.  My laptop slowly built up PF Usage up to ~1.9 MB and then maxed out.  So keep an eye on the PF and if it builds too high, shut down the pop-up and the adviced find windows, then start again.

     

    I found that adding the javascript command to my favourites saves time.

     

    [I found I needed to search/replace the '@' symbol in the email addresses for '&#64;'  so the address myemail@domain.com becomes myemail&#64;domain.com.] -> Correction, it works fine without doing this.

     

    Also using the =TRIM() command in Excel helps to clean up the addresses before running the query.

     

    Adi - if you are reading this drop me a mail at fraserm @ bwinc.com (without the spaces) - I'd like to send you a thank you card!

     

    If anybody form Microsoft is reading this - this should certainly be a feature in the next release (but a lot more streamlined than this work around!)

     

    Cheers,

     

    Fraser

     

    Friday, May 30, 2008 12:18 AM