How to get started with jQuery Grid Unbound Columns

Damyan Petev / Tuesday, October 16, 2012

Ignite UI jQuery Grid's Unbound Columns An all new functionality with the second release for this year comes to bring more flexibility to your grid and the way it handles data. The unbound column is one of the classic grid features throughout Infragistics experience with grids and now user demands are met by the all-new-yet-same Ignite UI Grid :)

The feature itself allows for columns that can be used to render separate arbitrary list of items or calculated values usually based on other columns’ values. The columns support and work with all grid features – proper sorting, filtering, etc. Also normal column enhancements like column templates, formatters and any other column property are available. It is very simple in concept and then once you get a closer look and take into account how this would interact with features it can get quite complicated, but that would be something to dive into later on.

Why Unbound columns?

What good are they for? For one, imagine a situation I end up in quite often – I like using Entity Framework but when I reach to the part where data needs to be send to the client all hell breaks loose. Why on earth has this not been addressed is beyond me but the Entity objects are damn near impossible for the default serializers and this has been around for so long. So what you do is either go for third party ones or use Data Transfer objects that are meant for such cases and that works good for me most of the time and it reduces traffic too. In those cases when one data model is not enough and out of you control, for when you need data from different sources and the Mashup Data Source is too much hassle or you don’t even have some parts available when the grid is created… this is when an unbound column can totally save the day.

Then again that is not all – besides integrating two pieces of data, the unbound columns can be used to display completely new data. You can use the formula property to calculate values based on other values in the row. That means you can now create Summaries on per row basis, rather than the out-of-the-box ones for columns!

Quick setup

At first glance the unbound columns are no harder to define that any other - as mentioned, all properties are available and features behave as expected. The single most important difference is the ‘unbound’ property that you can set to true in order for that particular column to be omitted when binding to the data source. Essentially you can get an empty column with no relation to the data model:

columns : [{
        unbound : true,
        key : 'empty',
        headerText : 'Empty',
        dataType : 'number'
    }
],

To put things in perspective, here’s the full grid definition.

$('#grid1').igGrid({
    width: '650px',
    autoGenerateColumns : false,
    autoGenerateLayouts : false,        
    columns : [{
            key : 'BusinessEntityID',
            dataType : 'number',
            headerText : 'B.E. Id'
        }, {
            key : 'SickLeaveHours',
            dataType : 'number',
            headerText : 'Sick Leave Hours'
        }, {
            key : 'VacationHours',
            dataType : 'number',
            headerText : 'Vacation Hours'
        }, {
            unbound : true,
            key : 'empty',
            headerText : 'Empty',
            dataType : 'number'
        }
    ],
    features : [{
            recordCountKey : 'TotalRecordsCount',
            pageIndexUrlKey : 'page',
            pageSizeUrlKey : 'pageSize',
            name : 'Paging',
            type : 'local'
        }, {
            sortUrlKey : 'sort',
            sortUrlKeyAscValue : 'asc',
            sortUrlKeyDescValue : 'desc',
            name : 'Sorting',
            type : 'local'
        }, {
            filterExprUrlKey : 'filter',
            filterLogicUrlKey : 'filterLogic',
            name : 'Filtering',
            type : 'local'
        }
    ],
    height : '600px',
    dataSource : "@Url.Action("Employees")",
    responseDataKey : 'd'
});

And the result of this is:

An empty Unbound column but still with appropriate filter conditions based on the defined data type.

As you can see you get a column of whatever type you have defined and the filter criteria match that too.

ASP.NET MVC

The helpers provided with the product could always be used instead to help you generate the same output with great ease. You actually have two options when using the server side assembly – chaining syntax with the HTML helper alone or combined with the grid model. Let’s see how that’s done. With just the helper in which case the model passed by the controller is the actual data:

@using Infragistics.Web.Mvc
@model IQueryable<GridUnboundColumn.Models.EmployeeDTO>

@(Html.Infragistics().Grid(Model).AutoGenerateColumns(false).AutoGenerateLayouts(true)
    .Columns(column =>
    {
        column.For(x => x.BusinessEntityID).HeaderText("B.E. Id").DataType("number");
        column.For(x => x.SickLeaveHours).HeaderText("Sick Leave Hours").DataType("number");
        column.For(x => x.VacationHours).HeaderText("Vacation Hours").DataType("number");
        column.Unbound("empty").HeaderText("Empty").DataType("number");
    })
    .Height("600px")
    .Features(feature =>
        {
            feature.Paging().Type(OpType.Local);
            feature.Sorting().Type(OpType.Local);
            feature.Filtering().Type(OpType.Local);
            feature.GroupBy().Type(OpType.Local);
        })
    .DataBind()
    .Render()
)

Or using the grind model where the latter is being passed to the view:

using Infragistics.Web.Mvc

public ActionResult Demo()
{
    ViewBag.Message = "Below the grid ....";
    AdventureWorksEntities context = new AdventureWorksEntities();
    var employees = context.Employees.ToList().ConvertAll(e => e.ToDTO());
 
    GridModel grid = new GridModel();
    grid.AutoGenerateColumns = false;
    grid.Columns = new List<GridColumn>();
    grid.Columns.Add(new UnboundColumn()
    {
        Key = "empty",
        HeaderText = "Empty",
        DataType = "number"
    });
    grid.Columns.Add(new GridColumn()
    {
        Key = "BusinessEntityID",
        HeaderText = "B.E. Id",
        DataType = "number"
    });
    grid.Columns.Add(new GridColumn()
    {
        Key = "SickLeaveHours",
        HeaderText = "Sick Leave Hours",
        DataType = "number"
    });
 
    grid.Columns.Add(new GridColumn()
    {
        Key = "VacationHours",
        HeaderText = "Vacation Hours",
        DataType = "number"
    });
    grid.DataSource = employees.AsQueryable();
    return View(grid);
}

And that is merely consumed in the view:

@using Infragistics.Web.Mvc
@Html.Infragistics().Grid(Model)

Note: While the result will generally be the same, the above grid model is missing feature definitions just for this snippet. Also all snippets so far assume you are aware scripts need to be loaded in advance either manually or using the Infragistics resource loader widget. For full code refer to the demos themselves or general help with proper setup can be found in the Deployment Guide in our online help.

“So now how do I get data in there?”

That’s the logical question that follows. You have a column completely unrelated to your data model but it’s empty and that’s not why it’s there. Rest assured, you are quire in luck when it comes to options to supply values for those type of columns.

Unbound Values

So now comes the part I mentioned with model serialization and DTOs. The simple task of displaying AdventureWorks () Employees that has no names also requires the Person table. For the sake of the example imagine you don’t have access to the model OR those are in separate databases even. So the grid is bound to the ‘Employees’ and since I only want the names from ‘People’ I provide them in a separate list.

ASP.NET MVC

public ActionResult Mvc()
{
    ViewBag.Message = "Below the grid ....";
    AdventureWorksEntities context = new AdventureWorksEntities();
    var employees = context.Employees.ToList().ConvertAll(e => e.ToDTO());
    ViewBag.people = context.People.Select(p => p.FirstName + " " + p.LastName).ToList();
    return View(employees.AsQueryable());
}

Keep in mind the list can be absolutely anything, really from values from remote APIs to calculated or generated values. Next in the view we have same grid definition as above with the addition of using the ‘UnboundValues’ option:

column.Unbound("name").UnboundValues((ViewBag.people as List<string>).Cast<object>().ToList()).HeaderText("Name").DataType("string");

And using the model (you can see the :

grid.Columns.Add(new UnboundColumn()
{
    Key = "name",
    HeaderText = "Name",
    UnboundValues = context.People.OrderBy(p => p.BusinessEntityID)
        .Select(p => p.FirstName + " " + p.LastName)
        .ToList().Cast<object>().ToList(),
    DataType = "string"
});

Of course, those server side properties are proxy to the client API where you can use it like so:

var peopleNames = @Html.Raw(Json.Encode(ViewBag.people)) ;

{
                unbound : true,
                key : 'name',
                unboundValues : peopleNames,
                headerText : 'Name',
                dataType : 'string'
            },

Ignite UI jQuery Grid with Unbound column of names from a separate source.

Note: When using the ‘UnboundValues’ property the list of provided values is assigned as is – meaning is the same order, which means that those *must* be ordered the same way as the data source if you want meaningful results.

Set Unbound Values

The very same result can be achieved using the ‘SetUnboundValues’ grid method. With it you can provide values for any columns by specifying its key:

grid.Columns.Add(new UnboundColumn()
{
    Key = "name",
    HeaderText = "Name",
    DataType = "string"
});
grid.SetUnboundValues("name", peopleNames);

The method is also available with chaining and it has two overloads – one accepting as second parameter either a value list like so far or a dictionary with primary key and value pairs. The latter naturally requires your grid to have a primary key defined and if that is the case it is the recommended method to use as it’s the end result of the other overload anyway (yes, by all means, read that as performance gain). This can also spare you the hassle of having to order your sources the same way because using the Dictionary overload you can essentially assign value for each primary key. So for the above ‘peopleNames’ you can now use something like:

var peopleNames = context.People.ToDictionary(x => (object)x.BusinessEntityID, x => (object)(x.FirstName + " " + x.LastName));

NOTE: While the unbound column itself may not support some remote feature (see below), other columns normally would and their results affect the entire data view and for correct results you absolutely need a proper mapping between unbound values and the grid’s primary key, therefore the above Dictionary overload of the ‘SetUnboundValues’ method is always the best option to be used.

As always things in client-side script are much less constrained and also somewhat different in functionality as it’s executed after the control is initialized. You can do that either in grid events or user triggered actions. For a start the easiest way is to go for a button click:

<button onclick="$('#Grid1').igGrid('setUnboundValues', [ 'John Doe(1)' , 'John Doe(2)' , 'Jane Doe(1)', 'John Doe(3)'], 'name')"> Set Unbound values </button>

Note: The client version of the method as you can see takes values first and then the key and can be called after the grid is initialized regardless of the unbound column state – be it still empty or already initialized with values, so this can be combined with the previous resulting in a prefilled on the server column and just the first few values replaced by the method:

The client side method to set unbound column values in the Ignite Ui grid.

Formula

The probably most exciting part. It’s not really even a formula per se. It’s a JavaScript function that is called with the rest of the row data and reference to the grid so it can calculate and return a value for the unbound column. However, since it’s just a client function you can return pretty much anything and obtain it in any way. That is powerful. Here’s the most basic usage I mentioned making some kind of row summary if you will. Inside the grid column definitions:

{
                unbound : true,
                key : 'total',
                headerText : 'Total',
                dataType : 'number',
                formula : calcTotal
            }

function calcTotal(row, grid) {
    return row.SickLeaveHours + row.VacationHours;
};

Note that the row is pretty much the current data record even with the ‘name’ unbound value we already have. Also the formula property can be set with string, which I’m sure you will see when using the identical MVC property:

grid.Columns.Add(new UnboundColumn()
{
    Key = "total",
    HeaderText = "Total",
    DataType = "number",
    Formula = "calcTotal"
});

The result of this is the heading screenshot of course.

Gotcha-s

As I explained it’s a deceptively simple feature and once you look into it it’s quite something. That warrants a few limitations. While it does work just fine on the client with every feature, the server side compatibility is lacking – in fact, remote Filtering, Sorting and GroupBy are not supported for unbound columns. You could potentially perform updating but as you can imagine the rows (grid records) no longer match the server model the grid was bound to so custom deserialization would be required.

There’s also the matter of how the additional data is to be handled and that is dictated by the ‘mergeUnboundColumns’ property. There are some performance implications to this that we’ll leave for some other time (read: the second blog, find directs links to skip to that below).

Resources and demos

How to configure jQuery Grid Unbound Column and make the most of it
- Using unbound columns with advanced column options such as Templating or formatting.

You can instantly try the the feature with this JSFiddle demonstrating different ways to set unbound values.
A shared demo for this and the previous blog – and ASP.NET MVC project with various grid configurations described so far. Keep in mind most views use the MVC wrapper and require at least a trial version of Ignite UI on your machine – click the banner below to get yourself one.
There are two demos available in our Samples: Unbound Column in the Grid and Unbound Column in the Hierarchical Grid.
You can find general and control specific help in our Online Documentation
You can find the client-side options and methods in the Ignite UI jQuery Grid API reference.

As always, you can follow us on Twitter @DamyanPetev and @Infragistics and stay in touch on Facebook, Google+ and LinkedIn!

TL;DR | Summary

We’ve looked into the benefits and different applications of the all new Unbound Columns feature in the Ignite UI jQuery Grid. We’ve went through adding such a column to the grid and the different ways to provide it with data in both client-side scripts and using the ASP.NET MVC server-side helper/wrapper. We noted some cases to in mind and in a following blog we’ll go into greater detail about the ‘mergeUnboundColumns’ property and also methods to get values from unbound columns.