Account Hierarchies in CRM 4.0 also useful for CRM 2011

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 name or it can just display the account name by iteself.  The entries link to the actual account records.

 Without Icon

With Icon 

 

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 link to the account edit screen for the selected node.   There is also an option to set an image URL to display an icon for the account.  I added a custom attribute to hold the icon URL.  If no URL 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              <asp:TreeView   onclick="BLOCKED SCRIPTpostBackByObject()" ID="TreeView1" runat="server" ImageSet="Arrows">… …

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 occurif(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 and allow scrolling.

The IFrame shows up in a new tab.


Adding to ToolbarYou can also opt to add the item as a toolbar item within account.  Simply, add the following segment to the ISVConfig and re-import it.

<ImportExportXml version="4.0.0.0" languagecode="1033" generatedBy="OnPremise">
  <IsvConfig>
    <configuration version="3.0.0000.0">
      <Entities>
        <Entity name="account">
          <!-- The Account Tool Bar -->
          <ToolBar ValidForCreate="0" ValidForUpdate="1">
            <Button Icon="/_imgs/treeOn.gif" Url="/ISV/AccountHierarchy/Default.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>
</ImportExportXml>

Read more »

activities, crm2011, customization, reports, subgrid, views

Back in the days before we had Microsoft Dynamics CRM 2011 available, it was a commonplace customization to show entities related to the parent entity directly on the parent’s form by utilizing an iFrame. Making information such as latest history items (nowadays called closed activities) quickly visible to any user opening the form is often justified, as one key functions of a CRM system is to share information about what interaction has taken place with the customer. Referencing the URL of the related view on the iFrame was not exactly supported, but it was a relatively safe customization to apply nonetheless.
Due to popular demand, Microsoft introduced an official method for achieving this UI customization in CRM 2011 through the use of the form sub-grid element. As a part of the entity forms redesign, the subgrids have now become an out-of-the-box feature on several default entities, such as accounts, contacts and opportunities.

Different navigation points, different views

It’s important to note that subgrids don’t use the entity associated view definition, which is applied when traditionally navigating to the view by using left side menu items on an entity form. Instead they allow you to separately choose a filter to “view only related records”, in combination with any of the system views available for the entity in question (but not the associated views, as those are “special” views). 9 times out of 10 you’ll want to keep the filter on, as showing non-related records on the entity’s form would under normal circumstances defy the standard UI logic of how Dynamics CRM presents records in different windows.
OK, fair enough, so that’s why the columns in a subgrid aren’t updated after you edit the entity related view, like you used to do in CRM 4.0 and previous versions. We can live with that. In order to provide a consistent user experience, I would recommend that these two views are set up so that they have identical contents. This is because an “oldskool” CRM user may navigate through the left side menu by habbit, whereas a person new to Dynamics CRM will probably prefer to just scroll through the form. Sadly there’s no “save as” functionality available on the entity related view, and you can’t promote a normal view to become a related view (since there’s only one of them). This means you have to manually configure the two views to be indentical in terms of attributes, column order, width, sorting and (in some cases) filters.

Rolling up the records

Another thing that may surprise a seasoned Dynamics CRM consultant until he learns the tricks of the latest version is that the aforementioned feature has further implications specific to accounts and opportunities in particular. As we’ve come to know, these entities have special capabilities enabled in the activity views: the roll-up functionality. Instead of being restricted to only activities directly related to a record, we can actually see a bit further. Let’s take a simple example of an account and it’s open activity associated view:

It’s that “Include Related Regarding Records” selection above the grid which allows us to view activities not only related to the account itself but also the ones regarding a contact of the account and an opportunity related to it. Pretty neat, as it’s often the people working at an account that we associate communication and activities to, such as emails and appointments.
Now, let’s take advatage of the new CRM 2011 functionalities and look at the activity subgrid that’s conveniently available in the out-of-the-box configuration of an account form:

Huh? Where did my activities go? They’re still there, but this particular navigation path will not allow you to view them, since you’re on a subgrid and, as we previously concluded, subgrids can’t show the entity associated view. This means there’s no way for you to apply the “Including Related Regarding Records” functionality over here.
I’ll be the first to admit I’ve fallen for this trap in customer demos more than once. The menu anchor for accessing the Notes & Activities subgrid is just too tempting to click, when what you really intended to do was to view the fully featured activity associated view and access a complete list of the related activities. If the difference between view columns was a minor inconvenience, then this is downright misleading to many users I’m sure.
The quick solution for this would be to just remove the activity subgrids from the account and opportunity entity forms where the results can be contradicting, thus forcing the user to navigate through the old fashioned menus into the related activities views. Another option would be to perform the old iFrame trick and just embed this view onto a form iFrame, which does sound a bit 4.0-ish. The last option is to go and vote on Microsoft Connect, requesting MS to include the full roll-up functionality for subgrid views in a future version of Dynamics CRM.

Activity roll-up across an account hierarchy

Let’s kick it up a notch further and look at a more complex scenario. It’s not uncommon to have accounts that have a hierarchical relationship with one another. Typically these are either subsidaries or branch offices of the parent account, which need to be treated as separate entities, even though they may form a single customer relationship when it comes to business activities. Below is an example of a hierarchy where there are three different levels of accounts, with each of them being a candidate for actities. And let’s not forget, accounts act as the parent record for contacts and opportunities, opportunities act as parents to quotes, orders and so on.
In this example we have four activities (the orange boxes) that have been added to various levels of the customer data hierarchy. Ultimately we’d of course like to be able to get a 360 view of the customer, regardless of which particular data object the user has associated the activity with. Let’s start by opening the Sub-account and navigating to the associated activities view, since we already have established that you can’t trust the sub-grids to view all data. On this window we get the full view of all four activities:

Great! Let’s try the opportunity record next:

Works the way it should, I see the activities from the opportunity as well as the related order record. Now, let’s go up to the very top of the hierarchy and open the Top level account record.

Wait – why is it so empty here at the top? I can see what’s goint on at the very bottom level of the hierarchy, the Sub-sub-account’s task, but why is there nothing from the middle tier? The reason for this is that none of the records visible in the Sub-account’s activity view were actualy set as regarding that particular account record. The special filter in the activity view was able to pick them up based on a parental relationship to the account (I’m assuming). However, that filter doesn’t roll up into the account hierarchy structure in general, which means the Top level account can only see activities from its own contacts, opportunities and then any of the account records beneath it. Had the Sub-sub-account’s task been related to a contact down in the hierarchy, even that one wouldn’t be visible up at the top.
As a final demonstration of the power of associated views over sub-grids, let’s see what things would have looked like on the middle tier for the Sub-account, had I accidentally clicked on the Notes & Activities anchor instead:

“Hmm, nothing much going on with this account, better call the account manager to see what’s the problem here…”

Reports to the rescue

The relational data model of a CRM system doesn’t always manage to make the underlying information structure fully visible to the casual user who is browsing the records instead of managing them. In order to eliminate the complexity of relationships and hierarchies between various entities and truly roll up data to provide that 360 view, it’s advisable to reserve an adequate share of your CRM implementation and development budget for creating summary reports that present exactly the information that the users are expecting to get out of the system.
Even with the standard reports that come with Microsoft Dynamics CRM, you can already get a more accurate overview of the activities in a hierarchical account structure. The Account Summary Report, which can be run against any account record, will allow you to view information from sub-accounts as well as related records. You just need to remember to change the default filters to “include”:

Looks great and also prints out nicely onto PDF’s that can be shared with users who are not yet familiar with accessing Dynamics CRM directly. You could of course email them a link to the report, but that URL doesn’t preserve any filter selection applied.
If you know your way around working with Visual Studio and BIDS, then creating a custom report that displays all the activities and embedding it on a form iFrame should be perfectly possible. Just don’t ask me about how to construct the query needed for pulling the activity data from all the different entities (let alone if you’re on CRM Online and need to use Fetch XML instead of SQL), since I’m not an expert on the topic. However, if you have any examples of such, please feel free to drop a comment below on how you’ve simplified activity data presentation for the CRM end user in your solutions.

Read more »

Posted in: Customization