Result grid formatting on the client-side

1. Problem

Quite often it's required to modify the content and/or appearance of the client-side grid which shows the result of query execution.

Here some examples:

  • Set a format for the values in a particular column.

  • Change the content and/or style of data for certain values. For example, to show negative numbers in red, and positive - black.

  • add a column or row.

  • Turn the values in some column into links to other pages.

In this article, we will show you how to implement such tasks.

2. Tools

To set up needed grid interface, you can use few callback functions defined in the view options during the intialization:

<script>

  var options = {
      .    .    .    .    .
	  
      widgets: {
	      resultGrid: {
		      beforeTableRendering: function(dataTable) {
              },
			  
              formatColumnHeader: function(dataTable, colIndex, label) {
              },

              formatGridCell: function (dataTable, rowIndex, colIndex, value) {
              }
          },
      }
  };
  
  var view = new easyquery.ui.AdvancedSearchView();
  view.init(options);
  . . . . . .
</script>

The first parameter for all functions will be an object of EqDataTable class which is similar to DataTable from Google Visualization API library. EqDataTable has sub-set of DataTable's functyionality, so you can manage grid's content, using some methods of this "class". You can find more information in EasyQuery.JS API Reference.

More about each callback function:

  • beforeTableRendering This one you should use before rendering the grid. You can change meanings of titles and other data, add columns or rows, set up different formats for certain columns.

  • formatColumnHeader This tool is the best one for editing titles of columns. colIndex determines the index of the column, and then label determines actual meaning of the title. This function puts the new title of the column back.

  • formatGridCell This function is to be used before rendering every cell at the grid. rowIndex and colIndex determine the index of the row and column, and valuedetermines actual meaning of the cell. Function puts the new meaning back (even with the HTML settings)

Please note that the script section listed above (an assignment of easyQueryViewSetting variable must be placed after the inclusion of eq.community.min.js or eq.enterprise.min.js script).

3. Use-cases

3.1 Formatting values in result grid

To format values in result grid, you can use callback function formatGridCell. This function is called for each cell in result data grid. The list of parameters contains:

  • dataTable - a reference to EqDataTable object
  • rowIndex - row's index
  • colIndex - column's index
  • value - current value for this cell. You can change that value in any way you need (e.g. wrap it into some a tag to make a hyperlink).

Here is an example of the formatting columns with data type date and number using our DataDisplayFormatter class.

var options = {
    .   .   .   .
	widgets: {
	   resultGrid: {
			formatGridCell: function (dataTable, rowIndex, colIndex, value) {
				var formatter = new easyquery.core.DataDisplayFormatter({
					localeSettings: {
						decimalSeparator: ',',
						shortDateFormat: 'yyyy-MM-dd'
					}
				});

				var colType = dataTable.getColumnType(colIndex);
				if (colType == "date") { //using DataTable column type for choosing formatter
					var date = eval("new " + dataTable.getValue(rowIndex, colIndex));
					return formatter.dateTimeToStr(date, 10);
				}
				else if (colType == "number") {
					return formatter.numberToStr(dataTable.getValue(rowIndex, colIndex));
				}

				return value;
			}
		}
	}
};
  .    .    .     .     .     .

Sometimes it's necessary to render the values in some column of result grid as links to some "Details" page. You can do it using formatGridCell callback function.

In the following example, we convert all values in "Order ID" column into links to "Order Details" page for a particular order:

var options = {
    .   .   .   .
	widgets: {
	    resultGrid: {
			formatGridCell: function (dataTable, rowIndex, colIndex, value) {
				var colId = dataTable.getColumnId(colIndex);
				var props = dataTable.getColumnProperties(colIndex);
				if (colId == "OrderID" || (props && props.table == "Orders" && props.field == "OrderID")) {
					var uri = "/Order/Details/" + value;
					value = '<a href="' + uri + '" >' + value + '</a>';
				}
				return value;
			}
		}
	}
};

3.3. Setting a column with checkboxes

Sometimes it's necessary to add column with checkboxes into result grid - so users will be able to select several rows and pass them for some additional processing (for example to export only selected rows). We can use beforeTableRendering and formatGridCell callbacks to get the necessary result:

var options = {
    .   .   .   .
	widgets: {
	    resultGrid: {
			beforeTableRendering: function(dataTable) {
				dataTable.addColumn("string", " ");
			},

			formatGridCell: function (dataTable, rowIndex, colIndex, value) {
				if (colIndex == dataTable.getNumberOfColumns() - 1) {
					return '<input type="checkbox" name="selectedRows" value="' + rowIndex + '" />';
				}
				else {
				   return value;
				}
			}
		}
	}
};
.    .    .    .    .

3.4. Showing "No record" message for empty result set

It would be good to show some message (e.g. "No record found") when the query returns an empty result set. Like in the previous example we can use beforeTableRendering callback to perform this task.

The basic idea will be to define an additional panel hidden panel which shows necessary message right near the result panel and then show it when the result set is empty.

Here is the code:

<script>
  . . . . . . .
		
var options = {
    .   .   .   .
	widgets: {
	    resultGrid: {
			beforeTableRendering: function(dataTable) {
				var resultPanel = $("#ResultPanel");
				var emptyResultPanel = $("#EmptyResultPanel");
				if (dataTable.getNumberOfRows() == 0) {
					resultPanel.hide();
					emptyResultPanel.show();
				}
				else {
					emptyResultPanel.hide();
					resultPanel.show();
				}
			}
		}
	}
};
.    .    .    .
</script>

 . . . . . . .

<div id="EmptyResultPanel" style="display:none"><p>Record no found</p></div>
<div id="ResultPanel" class="result-panel-content">

 . . . . . . .