Format column for dynamically generated column collection

[Foster]

I am prototyping a solution that uses any of a set of data sources for a grid.

So far, I'm pleased with how kendo easily renders the columns dynamically, without defining them in the instantiation. I've found, also, how to hide columns I do not want rendered. What I've not yet found is how to format dates. I see many examples of applying templates for dates, but none for applying/binding templates to columns after initial kendoGrid instantiation. Is there a way within Kendo to do this, or will I need to write something custom against the DOM?

Thanks,
Foster

[Alexander Popov]

Hi Foster,

I am glad to hear that you are having a positive experience with Kendo UI.
Dates could be formatted using the kendo.toString method, as explained here, however changing a column's template after the Grid's initialization is currently not supported.

Regards,
Alexander Popov
Telerik

[Foster]

OK, so for absolute clarification, rendering date types on dynamically/automatically generated columns is not supported at this time.

So, a nice feature to have would be a default globalization configuration or setting and detection of date (and numbers and currency) columns during automatic generation.

My solution for now will be to convert the dates to strings within the stored procedures. An alternative solution would be to iterate over the columns after rendering and inspect for date types. Any other, more efficient ideas?

Thanks.

[Alexander Popov]

Hello again Foster,

Rendering dates is fully supported, however changing the formatting options of the Grid's columns (regardless of the type) is not. Numbers and dates are already detected and the appropriate editor is used, as seen in this example. Currency on the other hand, is also a number, so one should be explicit what kind of widget should be used as an editor. Converting the dates to strings will prevent you from using the DatePicker as editor, if the Grid has editing capabilities at all. I am not sure what your setup is, but generally speaking, when a Grid is initialized using dynamic data the best approach would be to generate the initialization options first (perhaps event on the server), based on that data. Then, once the page has loaded one could use these options to initialize a Grid, that will use proper editors, templates, IDs and events.

Regards,
Alexander Popov
Telerik

[Foster]

OK, finally got back to this today. I have clarification for you, and a solution that works for me. I'd love to hear of a simpler solution as this seems like it would be a common scenario and the solution I have is both dated and complex.

The root cause is the deserialization of dates from JSON to JavaScript objects. I've traditionally been a SOAP guy, so this is new to me. I was using the native (IE) window.parse() method to deserialize the JSON. The resulting dates (as formatted by the .NET JavaScriptSerializer object) were interpreted as strings by the .parse() method and rendered in Kendo as "/Date(1386079666990)/".

After some self education on this subject, and a few attempts to solve it myself, I came across this excellent solution: http://erraticdev.blogspot.com/2010/12/converting-dates-in-json-strings-using.html

Calling  this extended jQuery method ($.parseJSON(jsonString, true);) works beautifully. What stuns me is that this article was written over 3-years ago and seems to be the only good way of doing this. What am I missing?

[Foster]

Minor correction: not native windows.parse, but JSON.parse.

[Foster]

So, after having additional issues filtering the dates (filterable.operators.date was never applying to the Date object in my data array) I've discovered another way to define the strings in the JSON as dates: use a schema. I suspect, at this point, also that I'll need to specify columns in order to specify short-date display.

All this leads me away from letting Kendo render the columns automatically, and instead to iterating over the data to produce my own auto-generated schema and columns. If I can do it, then I'd think you guys could include the same sort of thing in your library. Again, I have to wonder if I'm missing an easier way. Please jump in if you have a suggestion.

[Foster]

Here is a draft (working but not complete nor fully tested) function for anyone else playing this game. Note that the return value has both columns and schema defined from a javascript data object passed in:

function columnDefinition(data) {
    var returnValue = new Object();
    returnValue.columns = new Array();
    returnValue.schema = {
        model: {
            fields: {
            }
        }
    }
    var maxRowsExamined = 10;
    var dateISO = /\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(?:[.,]\d+)?Z/i;
    var dateNet = /\/Date\((\d+)(?:-\d+)?\)\//i;    var colPos = 0;
    for (var col in data[0]) {
        returnValue.schema.model.fields[col] = new Object();
        if (data[0].hasOwnProperty(col)) {
            var testRow = 0;
            while (testRow < maxRowsExamined) {
                var val = data[testRow][col];
                if (val == null) {
                    if (testRow == maxRowsExamined - 1) {
                        //no values found, assume string
                        returnValue.schema.model.fields[col].type = 'string';
                        returnValue.columns[colPos] = {
                            field: col
                        };
                    }
                }
                else if (col == 'ID') {
                    //depends on data having and id named ID
                    returnValue.schema.model.fields[col].type = 'number';
                    returnValue.columns[colPos] = {
                        field: col,
                        template: "<input type='checkbox' value='#: ID #'></input>"
                    };
                    break;
                }
                else if (dateNet.test(val) || dateISO.test(val)) {
                    returnValue.schema.model.fields[col].type = 'date';
                    returnValue.columns[colPos] = {
                        field: col,
                        format: "{0: MM-dd-yyyy}"
                    };
                    break;
                }
                else if (Object.prototype.toString.call(val) === '[object Number]') {
                    returnValue.schema.model.fields[col].type = 'number';
                    //naming conventions used to determine formatting
                    if (col.indexOf('amount') > -1) {
                        returnValue.columns[colPos] = {
                            field: col,
                            format: "{0:c}"
                        };
                    }
                    else {
                        returnValue.columns[colPos] = {
                            field: col
                        };
                    }                    break;
                }
                //not sure how to tell via SQL. Maybe the serializer turns SQL bit to true/false?
                else if (Object.prototype.toString.call(val) === '[object Boolean]') {
                    returnValue.schema.model.fields[col].type = 'boolean';
                    returnValue.columns[colPos] = {
                        field: col
                    };
                    break;
                }
                else {
                    returnValue.schema.model.fields[col].type = 'string';
                    returnValue.columns[colPos] = {
                        field: col
                    };
                    break;
                }
                testRow++;
            }
        }
        colPos++;
    }
    return returnValue;
}

[Alexander Popov]

Hello Foster,

In case you know the type of each field you could generate both the DataSource schema and the columns options on the server. For example:

//pseudocode
foreach (field in Fields)
{
    schema.model.fields[field.FieldName] = {type: field.DataType};
}

This would allow you to take advantage of the built-in features instead of re-writing them yourself. 

Regards,
Alexander Popov
Telerik

[Foster]

If I'm generating the schema and column options (formatting, etc.) aren't I re-inventing the features in any case? If I understand where you are headed with this approach, I would serve up the schema and columns objects via JSON service calls, right?

In any case, an advantage to doing this server-side is that I do not have to sniff the data to guess the types, but can rely instead on the db types. That would certainly be cleaner and more reliable. Depending on how the rest of my requirements map to Kendo, I may take this approach.

[Alexander Popov]

That is correct - once the schema and columns are prepared using the data types information (conveniently available on the server-side) you could pass them to the client-side where the Grid would be initialized with these options. Whether it would be using Ajax calls or you would pre-render the JavaScript initialization code on the server depends on your setup , requirements and/or personal preferences. 

Regards,
Alexander Popov
Telerik