View accounts like hierarchy / treeview

(0) Share

Report

Posted on by Ryan McCormick

420

I need to create a solution to display accounts like a hierarchy or in a tree view. I was able to create a solution to display all of the accounts in the system this way by modifying something I found here (http://www.c-sharpcorner.com/UploadFile/desaijm/database_hierarchy_with_datarelation02192006170040PM/database_hierarchy_with_datarelation.aspx) to work with CRM, but I need a way to only show a single "branch" of the tree depending on what account the end user has selected. They only want to see the related accounts when an account is selected and I'm not sure how to do it. Any guidance?

*This post is locked for comments

I have the same question (0)

All responses (21)

Answers (0)

Yaniv Arditi 3,990 on at

Like (0)

Report

Hi,

You can develop a custom .ASPX page that will receive the select Acount GUID and display the selected Account and its subordinate Accounts using the .NET TreeView control.
This will display the Accounts similarly to the way Windows Explorer display folders.

Hope this helps,

Yaniv Arditi

Was this reply helpful? Yes No
Hassan 90 on at

Like (0)

Report

Hi Ryan,

Pythagoras Communications Ltd has developed a tree view control that does exactly what you are trying to achieve. For a quote please email. jo.savidge@pythagoras.co.uk

Regards,

Hassan Pythagoras Communications Ltd

Was this reply helpful? Yes No
Ryan McCormick 420 on at

Like (0)

Report

Hi Yaniv,

Thank you for your reply. That is exactly what I am trying to achieve. I am able to get the selected account GUID, but I am not sure of an efficient way to find/select only the related accounts. Some, but not all, "branches" may have 5 or 6 levels. So what I need is to find all of the parents and all of the children of the selected account. No problem if the user selects a "root" node or account (no parents) or even an account at the lowest level (no children) to find all of the related accounts. The part I can't figure out is how to dynamically, and efficiently, find all of the parent accounts above and the children accounts below if they select an account that isn't a root node or at the lowest level.

I thought I could just pick a random number of times to "loop" through and find related records, but that isn't a "real" dynamic solution. It's just betting that there won't be any accounts say 10 layers deep or something, and it is terribly slow to return records that way. Select all records where parentaccountid = GUID, then for each of those results select all records where parentaccountid = returned from the last loop GUID, etc, etc... Try that about 10 times to get to the root of the node. It would work, slowly, but I am looking for a better solution.

You are right about a custom ASPX page that is definately the way to go. Thank you again for your reply.

Was this reply helpful? Yes No
Ryan McCormick 420 on at

Like (0)

Report

Thanks anyway Hassan, but I need to build a solution not buy one. Post the source code if you really want to help.

Was this reply helpful? Yes No
Yaniv Arditi 3,990 on at

Like (0)

Report

In order to achieve better performances, consider a direct SQL query to the Account FilteredView. The query will receive the top Account GUID and use a recursive method the retrieve the Account's branch much faster than the API.

Good luck,

Yaniv Arditi

Was this reply helpful? Yes No
Ryan McCormick 420 on at

Like (0)

Report
Yaniv,

Thank you again for responding. I completely agree with you that a direct query to the FilteredAccount view is the way to go. The problem still exists though that the query will not receive the top account GUID, but the selected account GUID. The user may or may not have selected the top account to view. They may have selected a "child" account and that is where my confusion/problem lies. I think that anyone reading this thread will get a better understanding of the problem thanks to your responses. I do appreciate it.

Consider this ...

Account 1

Account a

Account b

Account 2

Account 3

Account c

Account d

Account 4

Account e

Account 5

Account f

Account g

Account 6

Account 7

What you have described so far is the scenario where the user always selects "Account 1" or "Account 5" in this model. If that were always the case then I would do exactly as you have suggested and use a recursive method to keep querying the FilteredAccount view to return the accounts where the returned GUID(s)=parentaccountid until there were no more records returned and I would have the entire "branch" of the tree. What that method doesn't provide a solution for is where the user selects any account other than one of the top level accounts like "Account 2" or "Account c".

The users ultimately don't want to keep clicking the "parent account" link on the account form or do a custom search to find all of the related accounts. They just want to click a button on the toolbar and see only the branch of the tree from the account they are viewing. I have made it so that they click a button and see the entire account tree in a modal dialog window and it works very quickly, but luckily for my company (and unluckily for me so far in regards to a solution) there are a large number of accounts, and they are seeing a lot of "useless" information when viewing the entire account tree.

I'm really not even trying to use the treeview control, although it looks nicer and you can expand/contract levels, it is just too slow to populate the entire tree. Maybe if I can find a fast solution to returning all of the records for a branch I can use it. Right now I am returning all of the accounts as XML, transforming it to indent the records, then displaying it in an XML control on the ASPX page. Just can't figure out how to get only related records instead of all.

Was this reply helpful? Yes No
Yaniv Arditi 3,990 on at

Like (0)

Report

When I wrote top level I meant the selected Account GUID. As the query receives a GUID and works in a recursive way, it doesn't 'mind' if the Account is the root Account in the hierarchy tree or the top Account in a lower branch.
I will try to construct the required query and post it here.

Was this reply helpful? Yes No
Ryan McCormick 420 on at

Like (0)

Report

Here is what I have. Maybe it will help to get an answer to what I need.

Create a new C# web application project in your favorite VS version. Rename the ASPX page to AccountHierarchy. Drag an XML control onto it.

Paste this code into the AccountHierarchy.aspx.cs window under Page_Load (Replacing [YOUR_SERVER] and [YOUR_ORG]):

DataSet dsResult = new DataSet();
                string connString = "Data Source=[YOUR_SERVER];Initial Catalog=[YOUR_ORG];Integrated Security=True";
                using (SqlConnection myConnection = new SqlConnection(connString))
                {
                    String query = "SELECT accountid, name, parentaccountid FROM FilteredAccount";
                    SqlCommand myCommand = new SqlCommand(query, myConnection);
                    SqlDataAdapter myAdapter = new SqlDataAdapter();
                    myCommand.CommandType = CommandType.Text;
                    myAdapter.SelectCommand = myCommand;
                    myAdapter.Fill(dsResult);
                    myAdapter.Dispose();
                }
                dsResult.DataSetName = "Accounts";
                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);
                Xml1.TransformSource = "AccountListing.xslt";
                Xml1.DocumentContent = dsResult.GetXml();

Add a new item to the project and select an XSLT file and name it AccountListing.xslt then select everything and paste this instead (Replacing [YOUR_SERVER] and [YOUR_ORG] again):

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output omit-xml-declaration="yes" />
<xsl:template match="/Accounts">
  <xsl:call-template name="AccountListing" />
</xsl:template>
<xsl:template name="AccountListing">
  <ul>
   <xsl:apply-templates select="Account" />
  </ul>
</xsl:template>
<xsl:template match="Account">
  <li>
   <a href="http://[YOUR_SERVER]/[YOUR_ORG]/SFA/accts/edit.aspx?id={accountid}" />
   <xsl:value-of select="name"/>
   <xsl:if test="count(Account) > 0">
    <xsl:call-template name="AccountListing" />
   </xsl:if>
  </li>
</xsl:template>
</xsl:stylesheet>

Sign the assembly and run/debug it and you will have a list of all of the accounts in your CRM system displayed like a hierarchy. If you want it to run on the server then copy the AccountHierarchy.dll file from the bin folder of your project to the bin folder on your CRM server (usually c:\inetpub\wwwroot\bin). Next copy the AccountHierarchy.aspx, AccountListing.xslt, and web.config files to the ISV folder on your CRM server (usually c:\inetpub\wwwroot\ISV). I got an error when I tried to run it on the server the first time because of the web.config file so I commented out the line <authentication mode="Windows"/> with a  after it. Then you change your isv.config file to add a button to the UI that points to your AccountHierarchy.aspx page.

Now what I need is to only select one branch of the entire tree. I have a GUID of an account being viewed/edited and I need to only return related records and display it like a tree. Any ideas???

Was this reply helpful? Yes No
Ryan McCormick 420 on at

Like (0)

Report

Yaniv,

That would be GREAT!!! I just have not been able to figure it out. Thank you in advance!

Ryan

Was this reply helpful? Yes No
Andrew Zimmer 340 on at

Like (0)

Report

Awesome post guys!  Really good stuff.

Here is a query you can give a try. Hope this helps:

with RecursionCTE (parentaccountid, accountid, [name])
as
(
select R2.parentaccountid, R2.accountid, R2.name from filteredaccount as R2
    where r2.accountid = 'D0E4DC4D-1C09-DE11-A512-0003FF800564' -- Enter GUID HERE
UNION ALL
     select R1.parentaccountid, R1.accountid, R1.name from filteredaccount as R1
     join RecursionCTE as R2 on R1.parentaccountid = R2.accountid
)

select R1.accountid, R1.name, R1.parentaccountid
   from filteredaccount as R1
   JOIN RecursionCTE as R2
   on R1.accountid = R2.accountid

Was this reply helpful? Yes No