Skip to main content

Show Column Total from a CRM Grid

Update: Andriy a33ik Butenko pointed out that my solution won't work on all views because the specific column will probably not be in all of the views. I updated the solution to check if the column exists first, if not it will prompt the user that this functionality can't be done on the selected view.

A user on the CRM forums asked if it was possible to be able to show a column total from highlighted records in a CRM grid. I went ahead and wrote some javascript to implement this functionality. The javascript for this is fairly simple and will be executed from a button in the ISV Config for an entity grid. I will be using the native Opportunity grid and I will be calculating the Est. Revenue column from that grid.

First, we can't do this functionality if our column doesn't exist in the view, so we will create a function to check if our column exists (estimatedvalue in this case).  We will first create a function called 'doesColumnExist':
 
function doesColumnExist(columnName)
{

}

Then find the table that holds all of the column headers:

var gridBar = document.getElementById('gridBar');

Then find all of the headers:


var headers = gridBar.getElementsByTagName('TH');

Then loop through each header and see if the fieldname property matches the column being passed into the function:

for (var i = 0; i < headers.length; i++)
{
        var header = headers[i];
        if (header.fieldname == columnName)
        {
            return true;
        }
}
return false;

So our function now looks like this:

function doesColumnExist(columnName)
{
    var gridBar = document.getElementById('gridBar');
    var headers = gridBar.getElementsByTagName('TH');

    for (var i = 0; i < headers.length; i++)
    {
        var header = headers[i];
        if (header.fieldname == columnName)
        {
            return true;
        }
    }
    return false;
}

Now we need to check if our column exists, if not we alert the user that they need to select a different view to use this functionality:

if (!doesColumnExist('estimatedvalue'))
{
    alert('Cannot total Est. Revenue.  Please select a view that has the Est. Revenue column.');
}
else
{
    // calculate estimatedvalue
}

Then the rest of the code will go in the else statement and we will first get the selected records from the grid:

var selectedRecords = document.getElementById('crmGrid').InnerGrid.SelectedRecords;

Next we initialize a total revenue variable that we will display to the user and then loop through the selected records.

var totalRevenue = 0;
for (var i = 0; i < selectedRecords.length; i++)
{

}

In the for loop above we will first grab the third index of the current selected record, which is essentialy the TR element of the grid.

var record = selectedRecords[i][3];

Then we will grab the 5th child of the record, in which this case is the Est. Revenue column that we are totaling.  As this is a money field, we need to replace the dollar sign from the value of the cell in order to parse the value so we can get a sum:

var revenue = record.childNodes[5].innerText.replace('$', '');

Next is the tricky part.  Our value will have some hidden spaces in front and back of it.  Therefore we need to escape the value and then replace the special characters so we can finally parse the value to a float:

var escapedRevenue = escape(revenue).replace('%u202D', '').replace('%u202C', '');

Now that we have the unformatted value, we can parse it to a float and add it to our total:

totalRevenue += parseFloat(escapedRevenue);

Lastly, outside of our loop we can display the total to the user:

alert(totalRevenue);

The finished javascript should look like this:

function doesColumnExist(columnName)
{
    var gridBar = document.getElementById('gridBar');
    var headers = gridBar.getElementsByTagName('TH');
    for (var i = 0; i < headers.length; i++)
    {
        var header = headers[i];
        if (header.fieldname == columnName)
        {
            return true;
        }
    }
    return false;
}

if (!doesColumnExist('estimatedvalue'))
{
    alert('Cannot total Est. Revenue.  Please select a view that has the Est. Revenue column.');
}
else
{
    var selectedRecords = document.getElementById('crmGrid').InnerGrid.SelectedRecords;
    var totalRevenue = 0;
    for (var i = 0; i < selectedRecords.length; i++)
    {
        var record = selectedRecords[i][3];
        var revenue = record.childNodes[5].innerText.replace('$', '');
        var escapedRevenue = escape(revenue).replace('%u202D', '').replace('%u202C', '');
        totalRevenue += parseFloat(escapedRevenue);
    }
    alert(totalRevenue);
}

Now that the javascript is written, we can add it to the Opportunity's grid in the ISV Config:

<Entity name="opportunity">
 <Grid>
  <MenuBar>
   <Buttons>
    <Button JavaScript="function doesColumnExist(columnName) { var gridBar = document.getElementById('gridBar'); 
         var headers = gridBar.getElementsByTagName('TH'); for (var i = 0; i &lt; headers.length; i++) { 
         var header = headers[i]; if (header.fieldname == columnName) { return true; } } return false; } 
         if (!doesColumnExist('estimatedvalue')) { 
         alert('Cannot total Est. Revenue. Please select a view that has the Est. Revenue column.'); } 
         else { var selectedRecords = document.getElementById('crmGrid').InnerGrid.SelectedRecords; 
         var totalRevenue = 0; for (var i = 0; i &lt; selectedRecords.length; i++) { 
         var record = selectedRecords[i][3]; var revenue = record.childNodes[5].innerText.replace('$', ''); 
         var escapedRevenue = escape(revenue).replace('%u202D', '').replace('%u202C', ''); 
         totalRevenue += parseFloat(escapedRevenue); } alert('Total Selected Revenue: $' + totalRevenue); }">
     <Titles>
      <Title LCID="1033" Text="Total Revenue" />
     </Titles>
     <ToolTips>
      <ToolTip LCID="1033" Text="Total Revenue" />
     </ToolTips>
    </Button>
   </Buttons>
  </MenuBar>
 </Grid>
</Entity>


Note:  I encoded the javascript as it is being placed inside an XML attribute in the ISV Config.

It should look like the image below:

And when we click the button:

Wah-la!

Comments

  1. This button will present in all views. But how will it work if there will be no Est Revenue column, or it will be not on position 5.

    As for me it is better to use JavaScript to retreive data from CRM WebServices.

    ReplyDelete
  2. That is a good point. I think the default views for Opportunity have the column in the view and in the same spot which is probably why I didn't catch this. I will post an update for this to use the Web Service. Thanks.

    ReplyDelete
  3. I updated it to alert the user if the column isn't in the currently selected view.

    ReplyDelete
  4. Hi Blake, I tried this and got error at var headers = gridBar.getElementsByTagName('TH'); these lines , could u please help me in sorting this out?

    ReplyDelete

Post a Comment

Popular posts from this blog

Announcing the New Dynamics 365 Toolbot Chrome Extension!

Today I am excited to announce the new Dynamics 365 Toolbot! This new Chrome Extension will allow you to perform commands that will help you with your development or administrative tasks. The extension can be found here - https://chrome.google.com/webstore/detail/dynamics-365-toolbot/kljiiminicfmdlplhejocopfmgmipach.

Note: Currently, due to the APIs being used, it only works on v9+.

First, navigate to the link above using Chrome and install the extension. Then, head to your Dynamics 365 environment and open a record. From there, click the little blue robot icon in the toolbar of Chrome which will pop open the Toolbot.



Click the text box and a list of commands will display. You can select or type any of the commands and hit Submit to execute them. Some commands require you to replace the default token with the desired request.


For example, select or type "display id" and click Submit and the Toolbot will display the current record's ID.



The majority of the commands should …

CRM 2011 LINQ - All Columns vs. Selected Columns

When creating LINQ queries in CRM 2011, it is easy enough to return the whole column set of the entity record without even thinking about the impact.  
Below is an example of querying all contacts from Chicago and returning all columns for each contact record.


This can be a big performance impact depending on the amount of columns that exist on the Contact entity and how many contact records exist in the system.  Another issue is that it could cause errors down the line if some of the attribute types are changed in the CRM system since the data is being bound to a model class that could be out-of-sync.
From the MSDN article on constructing LINQ queries (http://msdn.microsoft.com/en-us/library/gg328328.aspx), we can see that the select clause creates a column set:
The select clause defines the form of the data returned. The clause creates a column set based on the query expression results. You can also define an instance of a new object to work with. The newly created object using the…

CRM 2011 Form XML

Retrieving and parsing the Form XML in CRM 2011 is pretty easy.  If you ever had to do this in CRM 4.0 you would know that the Form XML was stored in the OrganizationUI table.  In CRM 2011 it is now stored in the SystemForm table.  
So first things first, we need to build a query to retrieve the Form XML for an entity from the SystemForm table.

In my case, I just have the entity's name so I need to retrieve the entity metadata and find it's type code to use to filter the query.  I also filter the SystemForm Type by 2 which means it is the Main form (http://msdn.microsoft.com/en-us/library/gg509016.aspx).
Now that we have my entity's Form XML.  We can build a method to find all the attributes' schema names that exist on the form.  This method just parses the XML string into an XElement and then selects all the id's of the "control" nodes that exist in the Form XML.  This array of id's will be all the schema names of the attributes that exist on the for…