A common request I hear from our clients is for inline editing of data within grid views in CRM. Many of them would love to see this functionality in Dynamics CRM 5.0. Occasionally, though, one of our clients has a strong enough need for this capability (e.g., for rapid data entry, Excel-like functionality, etc.) and asks us to provide it.
On a recent project, I worked with a company to replace an Excel-based data collection process (where Excel files are distributed, collected, and consolidated) with a web-based application. The goal was to provide the same general capabilities provided by Excel (ease of data entry, grid-based interface, calculated fields, drop-down picklists, data validation, etc.) but provide it over the web and connect the application with the company's Dynamics CRM 4.0 system. And, of course, the page couldn't post back to the server after entering data in a cell so the use of Ajax was mandatory.
Our client had already invested in ASP.NET components from Infragistics for other projects so I downloaded the components and took a close look at the capabilities, particularly the Ajax, grid, and inline editing features. Fortunately, I became convinced that the components would meet the needs of the project very well so I proceeded with the application's design, estimates, and prototypes.
(Infragistics offers a 30-day trial of their components, so you can give them a test drive before forking over the thousand bucks.)
Downloading and installing the ASP.NET controls was simple. Being new to Infragistics components, though, I was initially tripped-up by their use of three names for their two grid products. Here's the deal: the "UltraWebGrid" and the "WebGrid" are the same product. This isn't at all obvious when first looking at the documentation. The "DataGrid" is the latest grid component, but we didn't use it due to its lack of support for hierarchical data (e.g., ADO.NET DataSet with multiple related DataTables).
To get started with the WebGrid, simple drag it onto the web form from the NetAdvantage 8.3 Web toolbox section in Visual Studio (we used Visual Studio 2005).
The easy way to get data to appear in the grid is to bind the grid to a data source. For example, you can use Visual Studio to create a DataSet that uses the ADO.NET SqlDataAdapter to query one or more CRM Filtered Views. The WebGrid provides a style editor that you can use to set inline editing rules, column widths, fonts, alternating row coloring, etc.
Connecting the WebGrid to a SQL query is fine for display purposes, but you can't (or shouldn't) write data back to CRM directly to the CRM database tables. So you won't get the "quick-and-dirty" benefit of binding the WebGrid to CRM because the grid won't be able to dynamically update the database when inline changes are made.
Although I had to write a lot of code to get data into the grid and write updates back to CRM, it was surprising easy to get all the parts working. The WebGrid provides a rich object model that allows you to configure and access all parts of the grid object, from the height, width, and overall style of the grid down to what should happen after someone types data into a cell.
Here's a screenshot of the application. I had to keep it small to protect the data (I'll try to upload a bigger data-wiped version later) but I mainly wanted to show the overall appearance of the grid and provide an example of the inline popup WebCombo control.
Here's a quick rundown of the architecture for this application:
On a recent project, I worked with a company to replace an Excel-based data collection process (where Excel files are distributed, collected, and consolidated) with a web-based application. The goal was to provide the same general capabilities provided by Excel (ease of data entry, grid-based interface, calculated fields, drop-down picklists, data validation, etc.) but provide it over the web and connect the application with the company's Dynamics CRM 4.0 system. And, of course, the page couldn't post back to the server after entering data in a cell so the use of Ajax was mandatory.
Our client had already invested in ASP.NET components from Infragistics for other projects so I downloaded the components and took a close look at the capabilities, particularly the Ajax, grid, and inline editing features. Fortunately, I became convinced that the components would meet the needs of the project very well so I proceeded with the application's design, estimates, and prototypes.
(Infragistics offers a 30-day trial of their components, so you can give them a test drive before forking over the thousand bucks.)
Downloading and installing the ASP.NET controls was simple. Being new to Infragistics components, though, I was initially tripped-up by their use of three names for their two grid products. Here's the deal: the "UltraWebGrid" and the "WebGrid" are the same product. This isn't at all obvious when first looking at the documentation. The "DataGrid" is the latest grid component, but we didn't use it due to its lack of support for hierarchical data (e.g., ADO.NET DataSet with multiple related DataTables).
To get started with the WebGrid, simple drag it onto the web form from the NetAdvantage 8.3 Web toolbox section in Visual Studio (we used Visual Studio 2005).
The easy way to get data to appear in the grid is to bind the grid to a data source. For example, you can use Visual Studio to create a DataSet that uses the ADO.NET SqlDataAdapter to query one or more CRM Filtered Views. The WebGrid provides a style editor that you can use to set inline editing rules, column widths, fonts, alternating row coloring, etc.
Connecting the WebGrid to a SQL query is fine for display purposes, but you can't (or shouldn't) write data back to CRM directly to the CRM database tables. So you won't get the "quick-and-dirty" benefit of binding the WebGrid to CRM because the grid won't be able to dynamically update the database when inline changes are made.
Although I had to write a lot of code to get data into the grid and write updates back to CRM, it was surprising easy to get all the parts working. The WebGrid provides a rich object model that allows you to configure and access all parts of the grid object, from the height, width, and overall style of the grid down to what should happen after someone types data into a cell.
Here's a screenshot of the application. I had to keep it small to protect the data (I'll try to upload a bigger data-wiped version later) but I mainly wanted to show the overall appearance of the grid and provide an example of the inline popup WebCombo control.
Here's a quick rundown of the architecture for this application:
- Upon application startup, I gather the default filter criteria from the filter options (e.g., date range, owner, etc.). The Infragistics date picker control came in handy for date filtering.
- I then pass the filter conditions to a method that dynamically builds the SQL WHERE clause for the main grid data. The data is stored in related DataTable objects. The WebGrid supports hierarchical datasets, so once you've populated the DataSet you can bind it to the grid and it takes care of rending parent/child rows.
- Once I have the data, I call a custom method to configure the grid columns. This involves setting the type of control for each column. Essentially, you can tell the grid whether a cell should provide a text field (with optional masking/formatting), a dropdown box, a WebCombo control, date picker, image, calculated value, etc.
- With the data in the DataSet and all columns configured, I then bind the data to the WebGrid. That gets the data on the screen, but that's only the beginning. The next part of the project involved writing a lot of JavaScript to handle the various actions that can happen in the grid. For example, when the user clicks into a cell, I sometimes want an Infragistics WebCombo to appear to allow the user to select from a popup grid.
- Saving data from the WebGrid back to CRM involves 1) Creating a Save button (turn-off the auto-postback functionality), 2) When Save is clicked, construct a DynamicEntity (it helps to have some wrapper/helper code for this part to build the SOAP) and set the name of the target attribute and its value, and 3) Use XmlHttpRequest to call the CRM Web Service with either a Create, Update, or Delete command in the SOAP XML.
One of the biggest challenges I faced on the project was using Ajax to dynamically filter the contents that appear in the Infragistics WebCombo objects used in the grid. For example, when the user selects an option on a parent row, I needed to filter the available options in the WebCombo for a cell that resides on a child row. The way I did this was 1) Create a JavaScript event handler for when the user clicks into a cell that needs a filtered combobox, 2) Formulate a CRM SDK query and use Ajax to get back a collection of CRM entitiese, 3) Clear the contents of the WebCombo that's linked to the clicked cell, and 4) Use the Infragistics WebCombo API to dynamically create option rows.
Infragistics provides an abundance of online samples, knowledge base articles, and sample applications to help you ramp-up on their controls.
All in all, the Infragistics ASP.NET controls provided us with a great collection of tools to provide an Excel-like interface for our client. They can now update CRM data from a web grid, which provides them with the rapid data-entry interface that they needed.
