Advanced Find Query From the Ribbon

I had an interesting request come in from a client.  They wanted to be able to run a query across two N:N relationships to return all account holders for a certain account.  They use CRM for referral tracking, and have it pushed down to their front line staff for this purpose, who do not have much in the way of time when dealing with a customer, so for this (and other reasons) they didn’t want to have them running an Advanced Find query.  We just wanted to punch in an account number and have the customers associated with that account pop up.  The three entities involved in this case were contact (OOB), Member Account (custom, not the OOB Account entity), and Account Relationship (custom, specifies the relationship between the Contact and the Member Account).

I had done something before with inserting fetch XML queries into a subgrid, so I figured that it might be possible to do the same thing at the main grid level using input from a ribbon textbox…..and it is!  I would assume, like alot of things with CRM, this isn’t supported, but who knows.

 

So here is what I did: first I set up a textbox up in the ribbon.

I set up the CommandDefinition of the textbox to execute some javascript (bel0w) and to includ the <CrmParameters Value=”CrmProperties”/> tag in the <Action>.

<CommandDefinition Id=RibbonSearchRibbonSearch.HomepageGrid.contact.MainTab.RibbonSearch.TextBox1.Command>

<EnableRules />

<DisplayRules />

<Actions>

<JavaScriptFunction FunctionName=findAccountsLibrary=$webresource:new_ribbonadvancedfind>

<CrmParameter Value=CommandProperties />

</JavaScriptFunction>

</Actions>

</CommandDefinition>

Now I have a place to enter the account number.  I then went out to advanced find and created my query, and downloaded the fetch xml.  In this case I actually had three entities, Contact, Member Account, and I had created a joining entity, Account Relationship to specify whether an account holder was the primary, approved to withdraw from the account, et cetera.  (I realize I could have just set them up to query in the quick find from the Account Relationship entity, but for reasons I won’t go into here that didn’t work really well.)  So my Advanced Find query looked something like this:

This let user CRM to generate the FetchXML I wanted to insert into the main grid by clicking the Download Fetch XML button, and all I need to do with my JavaScript is insert the fetch with the “00000000000” replaced by the value in the textbox, and wahlaa!

So how do we do that?

If we go and look at the HTML of the grid, we find that the grid on the main form has an Id of “crmGrid”.  Using a bit of  JavaScript, I looped through the keys and found a “controls” key, and inside of there,  a method called setParameter.  I had seen the same structure in subGrids, and found that there was a “fetchXml” parameter that the setParameter method could update in the main grid as well.  All I have to do is run a tiny little bit of JavaScript to call the setParameter method.  My JavaScript function findAccounts, called out in the RibbonDiff xml snippet I showed above, looks like the following:

function findAccounts(comProp)

{

    var crmGrid = document.getElementById(“crmGrid”).control;

    var fetch = ‘<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”true”>’

+‘<entity name=”contact”>’

+‘<attribute name=”fullname” />’

+‘<attribute name=”telephone1″ />’

+‘<attribute name=”contactid” />’

+‘<order attribute=”fullname” descending=”false” />’

+‘<filter type=”and”>’

+‘<condition attribute=”statecode” operator=”eq” value=”0″ />’

+‘</filter>’

+‘<link-entity name=”new_accountrelationship” from=”new_individual” to=”contactid” alias=”aa”>’

+‘<link-entity name=”new_memberaccount” from=”new_memberaccountid” to=”new_memberaccount” alias=”ab”>’

+‘<filter type=”and”>’

+‘<condition attribute=”new_accountnumber” operator=”eq” value=”‘ + comProp[“Value”] + ‘” />’

+‘</filter>’

+‘</link-entity>’

+‘</link-entity>’

+‘</entity>’

+‘</fetch>’;

crmGrid.setParameter(“fetchXml”, fetch);

crmGrid.refresh();

}

All I do is pull the Value out of the CommandProperty and insert it into the fetch, and then insert the fetch into the grid’s fetchXml parameter and refresh the grid.  So if I have a Member Account with the Account Number of “1”, to which Bob Johnson and Bill Johnson are associated through an N:N relationship as account holders, I can punch in 1 in the ribbon textbox, hit the enter key, and have the results returned to me without having to open Advanced Find at all!

This would have been pretty easy to figure out, but some of the documentation on the ribbon controls is pretty spotty and there is no documentation at all for modifying the main grid like this, so I suspect MS will not support this code and it could be broken by an upgrade, et cetera and so on.

In the mean time, its pretty cool to be able to run Advanced Find queries like this.  Could be useful in a number of different scenarios with proper rework into a real product to make it so the fetchXml wasn’t hard coded in, et cetera.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: