Answered by:
Identifying multiple contacts using excel and advance find

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 PMModerator -
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?
- Open the advanced find > Press Ctrl + N to open it in a new IE Window.
-
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
- Select each row and add an OR operator to finish the query.
- Run the Query
- 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:
- Put all emails on the 3rd column (C).
- put this in the Column B - <condition attribute="emailaddress1" operator="eq" value="
- put this in Column D - "/>
- Create a simple Concatenate function in Column A – Concatenate( B1 , C1 , D1 )
- Drag the result downward so the function will apply to all rows.
- 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.
- 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'/>";})
- 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 '@' 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