Using the Advanced Find for FetchXML builder
Some time ago I talked about the program "FetchXML builder" to build FetchXML queries: http://ronaldlemmen.blogspot.com/2006/09/fetchxml-builder.html, but take a look at the next solution.
You can just open the Advanced find page and build the query as you like. Then run the query to see if it returns the data as you wish it should. If you are satisfied with the results and you want to know what query was sent into the CRM Framework, then press F11 to get the address bar and enter this script and press enter.
javascript:alert(resultRender.FetchXml.value);
If you get a warning about leaving the page, just press 'ok' and then the query which is sent to the framework opens up in a popup. Unfortunately you cannot select the text to copy and paste. But with Windows XP and Windows Server 2003 you can copy all text on the popup by clicking somewhere on the popup (not on the button "OK" ofcourse) and pressing ctrl+c. Now in notepad you can paste the text of the FetchXML.
Good luck!
Update: Thanks to Piotr in the comment section I have learned the javascript prompt command. Try this instead of the alert:
javascript:prompt("my query:", resultRender.FetchXml.value);
21 comments:
Great tip! Especially the part about being able to copy the contents of a popup to the clipboard via ctrl-c (I had no idea!) :-)
http://advantageworks.blogspot.com
Or you can use prompt function, like:
javascript:prompt("my query:", resultRender.FetchXml.value);
This will give you the query ready to copy :)
Thanks Piotr,
I've updated the post.
Ronald
this is fantastic mate..saved my day
Hi all, I just came across this because i was trying to use the advancedfind.aspx as a pop up page to show a list records that has many-to-many relationship with contacts.
I was wondering if i had the xml for my search query, how i can encorporate the xml with the aspx page to actually run a search without having to select my query and filling in the values.
Is there a way of using an ISV button in CRM that calls the advancedfind.aspx?.......
with the xml or a way of setting the xml behind the above.
Hi Kevin,
You can create an html page which does a post to the advanced find search result page which supplies exactly the same values as the advanced find search screen would supply. One of the values is the fetchxml.
Somewhen I will write a blog post around this, but this should get you started.
Ronald
Dear Ronald,
I kinda got this working and was wondering if you want a copy of this html page that will be invoked via a button from the contacts window and stick a parameter in the url. This later will take the parameter paste it into the FetchXml and send this form to the fetchData.aspx and show the results.
My email is 1kevinchu1@gmail.com please contact me if you wish to get a copy of this tiny code. Thank you for your help and hope to speak to you soon.
Regards,
Kevin Chu
Hi Ronald,
can you help me out here?
I need to select a fulfilled order via a lookup. It seems like the lookup itself filters out records.
(like selecting an inactive account pe.)
Regards,
Boudewijn
Hi Boudewijn,
A lookup does filter the inactive records. You are not able to select these records in a lookup.
Ronald
You can also create a bookmark so you dont have to enter the javascript into the address bar every time.
Set your bookmark to:
javascript: var x = prompt("my query:", resultRender.FetchXml.value);
The "var x = " part prevents IE from navigating away from the advanced find page.
Hi Ronlad,
Thanks for all ur posts.. it is of gr8 help for all developers
Could you pls let me know how to combine two fetch xml queries using union clause.....
This will be of gr8 help for me....
Hi Shilpa,
There is no way to create a fetchxml query which contains a union. You'll need to do multiple fetch requests and combine the results in a dataset.
Hope this helps,
Ronald
Hi Ronald,
Thanks for ur reply.
Would u pls help me on how to use "distinct" in fetch XML query.
Thanks in advance
Hi Shilpa,
You can get all the information around the fetchxml query on this page in the SDK: http://msdn.microsoft.com/en-us/library/bb930489.aspx
You'll see that there is an attribute for Distinct. You would use it like this:
// Retrieve all accounts.
string fetch1 = "<fetch mapping='logical' distinct="true">";
fetch1 += "<entity name='account'><all-attributes/>";
fetch1 += "</entity></fetch>";
Ronald,
i am trying to get the complete list of users with direct or indirect reporting to the logged in user. for the same i am tring to write the fetch xml statement but stuck in how to convert the sql CTE queries to fetch xml query ??
do you have any suggestion on how to do that ?
I used advanced find window and got the results and i pressed F11 and got the addredd bar and pasted the javascript code in the address bar it prompted me to close the existing window but did the get the query it shwed the error ad unhandeled exception , can u please help me on this... and aslo i need the query ID of that query how can i get that ..
please help me on this..
Hi,
I was using your tips but on the popup the result that i get is this
"Specified argument was out of the range of valid values.
Parameter name: Expected 'safe string' data type for 'RequestUri' parameter in 'Request.QueryString'. The raw request was 'GET /_common/error/errorhandler.aspx?ErrorCode=&RequestUri=%2fDevScope%2fAdvancedFind%2fAdvFind.aspx%3fEntityCode%3d10006%26QueryId%3d%257BCCDA2C4D-57DE-DD11-A7CA-00155D00802A%257D%26ViewType%3d1039javascript%3aprompt%28%2522my%2520query%3a%2522%2c%2520resultRender.FetchXml.value%29%3b&BackUri=' called from . "
What is the problem?
Vishwas,
This would make quite a difficult query for the fetchxml. I would look into using reporting for this purpose.
With kind regards,
Ronald
Hi Ronald,
I cant use promt on ie8, ı dont know why. But there is another good solution on ie8 which is
javascript: clipboardData.setData("Text", resultRender.FetchXml.value);
When you allow access fetch query is in your clipboard :)
Ersin,
Does anyone know why after upgrade of CRM from 3.0 to 4.0 we have a new workflowbase table that takes up 80% of the database space (1.3G)? What is in it? Can we clean it up?
I come back to this post on a regular basis - it's such a cool hint, though I'm almost too lazy to remember the syntax! Either way - Thank you so much!
Perhaps another little tip worth mentioning - when you need the LayoutXml for operations, just replace it giving you:
javascript:alert(resultRender.LayoutXml.value);
Same great principle - works perfectly! :)
Post a Comment