Sunday, March 8, 2009

Account Hierarchies in CRM 4.0

I recently collaborated with Ryan McCormick and Yaniv Arditi on the Dynamics forums at https://community.dynamics.com. We created a hierarchical representation of accounts in CRM that can be added as either a tool bar button or as an IFrame within account. The page can also display an icon next to the account or it can just display the account name. The entries link to the actual account records.

Without Icons





With Icons




Query
When pulling hierarchy data, one concern is performance. Each level in the hierarchy would require a call to the CRM webservice. Instead we went with a RecursionCTE query that used the filtered views.

with RecursionCTE (parentaccountid, accountid, [name])
as
(
select R2.parentaccountid, R2.accountid, R2.name from filteredaccount as R2
where r2.accountid = @accountid
UNION ALL
select R1.parentaccountid, R1.accountid, R1.name from filteredaccount as R1
join RecursionCTE as R2 on R1.parentaccountid = R2.accountid
)
select R1.new_pictureurl, R1.accountid, R1.name, case when r1.accountid =@accountid then null else R1.parentaccountid end as parentaccountid
from filteredaccount as R1
JOIN RecursionCTE as R2
on R1.accountid = R2.accountid


Adding Nodes to Treeview
I used an ASP.Net TreeView to display the hierarchy. I loaded the data into a dataset using a relationship and iterated through the dataset, adding nodes. I also checked to see that only four levels of nodes are shown. The reset are collapsed so the screen doesn’t become too cluttered.

… …
dsResult.Tables[0].TableName = "Account";
DataRelation relation = new DataRelation("ParentChild",
dsResult.Tables["Account"].Columns["accountid"],
dsResult.Tables["Account"].Columns["parentaccountid"],
true);
relation.Nested = true;
dsResult.Relations.Add(relation);

DataTable dt = dsResult.Tables[0];
DataRow[] rows = dt.Select(string.Format("accountid = '{0}'", acctId));
if (rows != null && rows.Length > 0)
{
DataRow row = rows[0];
TreeNode node = GetNode(row, true);
TreeView1.Nodes.Add(node);
DataRow[] childRows = row.GetChildRows(relation);
if (childRows.Length > 0)
AddChildNodesRecursive(node, childRows, relation, 1);

}
}
}


private void AddChildNodesRecursive(TreeNode parentNode, DataRow[] dataRows, DataRelation relation, int depth)
{
foreach (DataRow row in dataRows)
{
TreeNode node = GetNode(row, false);
parentNode.ChildNodes.Add(node);

DataRow[] childRows = row.GetChildRows(relation);
if (childRows.Length > 0)
AddChildNodesRecursive(node, childRows, relation, depth + 1);

if(depth >= _maxTreeDepth - 1) // set to 4
node.Collapse();

}
}



Displaying Image and URL
I set the navigate URL to the URL of the account. There is also an option to set an image URL to display an icon for the account. If none exists then a default icon can be used.

private static TreeNode GetNode(DataRow row, bool isRoot)
{
TreeNode node = new TreeNode();
node.Text = " " + row["name"].ToString();
node.Target = "_new";
node.NavigateUrl = string.Format("/{0}/SFA/accts/edit.aspx?id=" + row["accountid"].ToString(), ORG); ;

if (row["inet_imageurl"] == DBNull.Value)
// Set to some default Image
node.ImageUrl = "/isv/photos/defaultimage.gif";
else
node.ImageUrl = row["inet_imageurl"].ToString();

return node;
}




Opening Window without Toolbar and Sized Correctly
I ran into a problem with the way the account window opens. The target is set to _new so it will open in a new window, however you don’t have any control over how the window is displayed (width, height, show toolbar, ect). I had to intercept the button click and use JavaScript to open the new window. Looking back, it would have been better to render the HTML myself rather than relying on the tree view control. However, it does the job.

I had to set the onclick of the TreeView to


function postBackByObject()
{
if(window.event.srcElement.href != null
&& window.event.srcElement.href != ""
&& window.event.srcElement.href.substr(0,4).toLocaleLowerCase() == "http"
&& window.event.srcElement.nameProp.substr(0,11).toLocaleLowerCase() != "webresource")
{
window.open(window.event.srcElement.href,'popup','toolbar=no,location=no,directories=no,status=yes,scrollbars=yes,menubar=no,resizable=yes,width=1000,height=560');
}

// clicks that are expanding the treeview come in with webresource - let them occur
if(window.event.srcElement.href != null
&& window.event.srcElement.href != ""
&& window.event.srcElement.href.substr(0,4).toLocaleLowerCase() == "http"
&& window.event.srcElement.nameProp.substr(0,11).toLocaleLowerCase() != "webresource")
{
event.returnValue = false;
return false;
}
}

Adding to IFRAME
To add the page as an IFrame add a tab to the account entity and add a section. Next, add an IFRAME. Set "Pass record object type-code and unique identifier as paramters" as true. Also allow cross side scripting.



Set the necessary number of rows. Allow scrolling as necessary.




The IFrame shows up in a new tab.




Adding to Toolbar
To add the item as a toolbar item within account, add the following segment to the ISVConfig and re-import it.




See the attached code for the full implementation. Special thanks to Ryan McCormick and Yaniv Arditi for their help.

-Andrew


This release is provided "AS IS" and contains no warranty and confers no rights.

1 comment:

rdt said...

Hi Andrew,

A useful and concise solution!

Thanks,

Yaniv Arditi