View accounts like hierarchy / treeview

This question is not answered

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?

All Replies
  • 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

  • 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

  • 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.

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

  • 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

  • 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.

  • 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.

  • 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 <!-- before it and 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???

  • Yaniv,

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

    Ryan

  • 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

  • I updated the code to use an ASP.Net treeview.  It is a little more configurable than the basic HTML.  You can make it collapsible and allows for images if you have a picture in your system (or not).  I set it to use a default image and set it to only display two tree levels.

    using System;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    using System.Data.SqlClient;
    using System.Xml;

    public partial class _Default : System.Web.UI.Page
    {
        private int _maxTreeDepth = 2;
        private const string ORG = "ORG";

        protected void Page_Load(object sender, EventArgs e)
        {
            // sake of testing
            Guid acctId = new Guid("D0E4DC4D-1C09-DE11-A512-0003FF800564");
            if (!string.IsNullOrEmpty(Request.QueryString["id"]))
                acctId = new Guid(Request.QueryString["id"].ToString());
            DataSet dsResult = new DataSet();
            string connString = string.Format("Data Source=SERVER;Initial Catalog={0}_mscrm;Integrated Security=True", ORG);
            using (SqlConnection myConnection = new SqlConnection(connString))
            {
                String query = " 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.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 ";

                SqlCommand myCommand = new SqlCommand(query, myConnection);
                SqlDataAdapter myAdapter = new SqlDataAdapter();
                myCommand.CommandType = CommandType.Text;
                myCommand.Parameters.Add("@accountid", acctId);
                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);

            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);
                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);
                parentNode.ChildNodes.Add(node);
               
                DataRow[] childRows = row.GetChildRows(relation);
                if (childRows.Length > 0)
                    AddChildNodesRecursive(node, childRows, relation, depth + 1);

                if(depth >= _maxTreeDepth - 1)
                    node.Collapse();

            }
        }

        private static TreeNode GetNode(DataRow row)
        {
            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); ;
            node.ImageUrl = "http://static.asp.net/NeuAvatarHttpHandler.ashx?username=samy&forceidenticon=False"; // stole logo for sake of demo
            return node;
        }


    }

  • You rock!  Thank you very much Andrew this is excellent work.

    It is almost there.  I'm just missing the parent nodes (really the root account of the branch to use as the anchor in the query).  I thought it was a done deal when one of our DBA's said that he wrote a function to get the "root" node (highest level parent GUID) in another system and would add it to our development environment, but when I called it from the page I kept getting an authorizations error.  So my plan is to try and duplicate his SQL function in C# and get the root node then use your recursive query to get all of the children.

    Thanks for sharing your knowledge of CTE's, recursive queries, and C#.  Your posts did help and I have learned a lot.  It is very much appreciated.

  • No problem, Ryan.  It was a fun night project.  You and Yaniv had 90% of the work done for me already. 

    If you add to the code or completely redesign it for that matter, ship it my way.  I'm sure it would look pretty cool if a designer got a hold of it (not me).  It could use some better collapse/expand images, ect.

    -Andrew

  • Hi Andrew,

    Nice work! Thank you for sharing your efforts with us.

    Yaniv Arditi

  •  

    I'm still waiting on feedback from the users to see if there are any more changes that need to be made, but here is the solution as of right now.

     

    Let me know if this can be done more efficiently, C# isn't a "first language".

     

    using System;

    using System.Collections;

    using System.Configuration;

    using System.Data;

    using System.Web;

    using System.Web.Security;

    using System.Web.UI;

    using System.Web.UI.HtmlControls;

    using System.Web.UI.WebControls;

    using System.Web.UI.WebControls.WebParts;

    using System.Data.SqlClient;

     

    namespace FilteredAccountHierarchy

    {

        public partial class _Default : System.Web.UI.Page

        {

            private int _maxTreeDepth = 2;

            private const string ORG = "ORG";

     

            protected void Page_Load(object sender, EventArgs e)

            {

                try

                {

                    Guid acctId = new Guid("607B9079-0C21-DD11-A76C-0019B9E31FBD");

                    if (!string.IsNullOrEmpty(Request.QueryString["id"]))

                        acctId = new Guid(Request.QueryString["id"].ToString());

                    DataSet dsResult = new DataSet();

                    String connString = String.Format("Data Source=SERVER;Initial Catalog={0}_mscrm;Integrated Security=True", ORG);

                    using (SqlConnection myConnection = new SqlConnection(connString))

                    #region Get Root Record

                    {

                        myConnection.Open();

                        //Had to just pick a random number I guess

                        for (int i = 0; i < 10; i++)

                        {

                            String query = "select parentaccountid from FilteredAccount where accountid=@accountid";

                            SqlCommand myCommand = new SqlCommand(query, myConnection);

                            myCommand.CommandType = CommandType.Text;

                            myCommand.Parameters.AddWithValue("@accountid", acctId);

                            SqlDataReader reader = myCommand.ExecuteReader();

                            while (reader.Read())

                            {

                                if (!reader.IsDBNull(0))

                                    acctId = reader.GetGuid(0);

                            }

                            reader.Close();

                        }

                        myConnection.Close();

                    #endregion

     

                        #region Get Child Records

                        String query2 = " 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.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 ";

                        SqlCommand myCommand2 = new SqlCommand(query2, myConnection);

                        myCommand2.CommandType = CommandType.Text;

                        myCommand2.Parameters.AddWithValue("@accountid", acctId);

                        SqlDataAdapter myAdapter = new SqlDataAdapter();

                        myAdapter.SelectCommand = myCommand2;

                        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);

                    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);

                        this.TreeView1.Nodes.Add(node);

                        DataRow[] childRows = row.GetChildRows(relation);

                        if (childRows.Length > 0)

                            AddChildNodesRecursive(node, childRows, relation, 1);

                    }

                }

                catch (Exception ex)

                {

                    Response.Write("The application terminated with an error." + "</br>");

                    Response.Write(ex.Message);

                    // Display the details of the inner exception.

                    if (ex.InnerException != null)

                    {

                        Response.Write(ex.InnerException.Message);

                    }

                }

                    #endregion

     

            }

     

            private void AddChildNodesRecursive(TreeNode parentNode, DataRow[] dataRows, DataRelation relation, int depth)

            {

                foreach (DataRow row in dataRows)

                {

                    TreeNode node = GetNode(row);

                    parentNode.ChildNodes.Add(node);

     

                    DataRow[] childRows = row.GetChildRows(relation);

                    if (childRows.Length > 0)

                        AddChildNodesRecursive(node, childRows, relation, depth + 1);

     

                    if (depth >= _maxTreeDepth - 1)

                        node.Collapse();

     

                }

            }

     

            private static TreeNode GetNode(DataRow row)

            {

                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); ;

                return node;

            }

     

        }

    }

     

    I just used the autoformat "arrows 2" for the treeview control.  Here's the code for the page...

     

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="FilteredAccountHierarchy.aspx.cs" Inherits="FilteredAccountHierarchy._Default" %>

     

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

     

    <html xmlns="http://www.w3.org/1999/xhtml" >

    <head runat="server">

    <style type="text/css" media="screen">

            a,a:link,a:visited,a:active

            {

                  font-family: Tahoma, Verdana, Arial;

                  color: #15428b;

                  text-decoration: none;

                  font-size: small;

            }

            a:hover

            {

                  color:#0000ff;

                  text-decoration:underline;

            }

     

            body

            {

            background-color: #EAF3FF; 

            }

        </style>

        <title>Account Hierarchy</title>

    </head>

    <body>

        <form id="form1" runat="server">

        <div>

            <asp:TreeView ID="TreeView1" runat="server" ImageSet="Arrows">

                <ParentNodeStyle Font-Bold="False" />

                <HoverNodeStyle Font-Underline="True" ForeColor="#5555DD" />

                <SelectedNodeStyle Font-Underline="True" ForeColor="#5555DD"

                    HorizontalPadding="0px" VerticalPadding="0px" />

                <NodeStyle Font-Names="Tahoma" Font-Size="10pt" ForeColor="Black"

                    HorizontalPadding="5px" NodeSpacing="0px" VerticalPadding="0px" />

            </asp:TreeView>

        </div>

        </form>

    </body>

    </html>

     

    And here is the ISV.config.xml to add the button (without extras)...

     

    <ImportExportXml version="4.0.0.0" languagecode="1033" generatedBy="OnPremise">

      <Entities>

      </Entities>

      <Roles>

      </Roles>

      <Workflows>

      </Workflows>

      <IsvConfig>

        <configuration version="3.0.0000.0">

          <Root>

            <MenuBar>

              <CustomMenus>

                <Menu>

                </Menu>

              </CustomMenus>

            </MenuBar>

          </Root>

          <Entities>

            <Entity name="account">

              <MenuBar>

              </MenuBar>

              <!-- The Account Tool Bar -->

              <ToolBar ValidForCreate="0" ValidForUpdate="1">

                <Button Icon="/_imgs/treeOn.gif" Url="/ISV/FilteredAccountHierarchy/FilteredAccountHierarchy.aspx" PassParams="1" WinParams="1" WinMode="2">

                  <Titles>

                    <Title LCID="1033" Text="Account Hierarchy" />

                  </Titles>

                  <ToolTips>

                    <ToolTip LCID="1033" Text="View Account Hierarchy" />

                  </ToolTips>

                </Button>

                <ToolBarSpacer />

              </ToolBar>

              <Grid>

                <MenuBar>

                </MenuBar>

              </Grid>

            </Entity>

          </Entities>

        </configuration>

      </IsvConfig>

      <EntityMaps />

      <EntityRelationships />

      <Languages>

        <Language>1033</Language>

      </Languages>

    </ImportExportXml>

     

    Hope this may help someone else and I owe a huge THANK YOU to Andrew and Yaniv for their input and insight.