Identifying multiple contacts using excel and advance find

All replies

• 

  

  0

  Sign in to vote

   

  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
• 

  

  0

  Sign in to vote

  Is there any easier way, I am unfamiliar with SQL Query Analyzer.

   

  Thursday, February 28, 2008 3:37 PM
• 

  

  1

  Sign in to vote

  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

  3. Select each row and add an OR operator to finish the query.
  4. Run the Query
  5. 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.

   

  7. 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'/>";})

   

  8. 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
• 

  

  1

  Sign in to vote

  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