Posted on 6. January 2012

Adding an Advanced Find Query to a Form Sub grid

I've yet to see a clear post on how to add an Advanced Find Query as a subgrid on an entity form for CRM 2011. This is something that was quite common in CRM 4 due to the lack of sub grid support - but with CRM 2011 occationally we come up against the limitation of only showing related records and fields of only a single relationship away from the root entity.

Here are the steps:

1. Find the SavedQueryId of the default advanced find view for the entity you are reporting on. Use the following SQL against the MSCRM database to find it:

Select Name,SavedQueryId,ReturnedTypeCode,FetchXml,LayoutXml from SavedQuery where QueryType=1 and IsDefault=1
Order by ReturnedTypeCode

2. Create a webresource named 'new_SubGridFetchXml.htm' of type 'Web Page (HTML)'

Provide the following html:


<HTML><HEAD><TITLE></TITLE>
<SCRIPT type=text/javascript src="ClientGlobalContext.js.aspx"></SCRIPT>
<SCRIPT type=text/javascript>
    function submitForm() {
        var form = document.forms[0];
        var context = GetGlobalContext();
        form.action = context.getServerUrl() + '/AdvancedFind/fetchData.aspx';
        form.LayoutXml.value ='<LAYOUTXML>';
        form.FetchXml.value = '<FETCHXML>'
        form.submit();
         }
</SCRIPT>
<META charset=utf-8></HEAD>
<BODY onload=submitForm()>
<FORM method=post action="">
<INPUT name=FetchXml type=hidden> 
<INPUT name=LayoutXml type=hidden> 
<INPUT name=EntityName value=contact type=hidden> 
<INPUT name=DefaultAdvFindViewId value=<SavedQueryID> type=hidden> 
<INPUT name=ViewId value=<SavedQueryID> type=hidden> 
<INPUT name=ViewType value=4230 type=hidden> 
<INPUT name=SortCol value=<SortColumnLogicalName>:1; type=hidden> 
<INPUT name=UIProvider type=hidden> <INPUT name=DataProvider type=hidden> </FORM></BODY></HTML>

Adjust the <SavedQueryID> reference to the default advanced find query id found in step 1.

Adjust the <SortColumnLogicalName> to be the logical name of the field you want to sort by.

Replace the LayoutXml and FetchXml strings with the values returned from the query in 1 - you can then adjust them to suit your particular needs.

IMPORTANT:

If you add the web resource to a sub 'virtual path', you must alter the reference to the ClientGlobalContext.js.aspx to include the corresponding number of '../'

Don't worry about the script error when saving the page in the WebResource editor.

Your finished page would look something like


<HTML><HEAD><TITLE></TITLE>
<SCRIPT type=text/javascript src="ClientGlobalContext.js.aspx"></SCRIPT>
<SCRIPT type=text/javascript>
    function submitForm() {
        var form = document.forms[0];
        var context = GetGlobalContext();
        form.action = context.getServerUrl() + '/AdvancedFind/fetchData.aspx';
form.LayoutXml.value ='<grid name="resultset" object="2" jump="lastname" select="1" icon="1" preview="1"><row name="result" id="contactid"><cell name="fullname" width="300" /><cell name="telephone1" width="125" /></row></grid>';
form.FetchXml.value = '<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"/></entity></fetch>';
            form.submit();
         }
</SCRIPT>
<META charset=utf-8></HEAD>
<BODY onload=submitForm()>
<FORM method=post action="">
<INPUT name=FetchXml type=hidden> 
<INPUT name=LayoutXml type=hidden> 
<INPUT name=EntityName value=contact type=hidden> 
<INPUT name=DefaultAdvFindViewId value={00000000-0000-0000-00AA-000000666400} type=hidden> 
<INPUT name=ViewId value={00000000-0000-0000-00AA-000000666400} type=hidden> 
<INPUT name=ViewType value=4230 type=hidden> 
<INPUT name=SortCol value=fullname:1; type=hidden> 
<INPUT name=UIProvider type=hidden> 
<INPUT name=DataProvider type=hidden> </FORM></BODY></HTML>


3. Add the webresource to the form that you want to show it on.

4. Publish and Go!

Usual disclaimers apply - and this is not a fully supported solution since it uses un-documented functionality

Comments

pingback roman20007.wordpress.com
10:30 PM on Thursday, April 18, 2013

Pingback from roman20007.wordpress.com

Whale page creation | Roman's Blog

Comments are closed