Merge several columns of JSON data and display as single column in jqGrid

Question

I want to merge some of the data (3 address columns to 1 column) I am retrieving via JSONP from Fusion Tables into jqGrid.

Does anyone know if this is possible/how to go about it? Unfortunately Fusion Tables SQL API does not currently support CONCAT via SELECT commands.

Oleg provided code for basically colspan-ing 2 columns if one had long data, but I actually want to take the data from several columns and present it as just one column in jqGrid

Thanks in advance

edit, added a snippet of code:

datatype: "jsonp", // "json" or "jsonp"
colNames: ["id","lat","long","Name","Address","","","Postcode"],
colModel:[
    {name:'id',index:'id',key:true,sorttype:'int',hidden:true,sortable:true},
    {name:'latitude',index:'latitude',hidden:true},
    {name:'longitude',index:'longitude',hidden:true},
    {name:'name',index:'name',width:170,sortable:false,sorttype:'text'},
    {name:'address_line_1',index:'address_line_1',width:400,formatter:function (cellvalue, options, rowObject) {
        addPart1 = rowObject[4];
        addPart2 = rowObject[5];
        addPart3 = rowObject[6];
        fullAddress = addPart1 + addPart2 + addPart3;
        return fullAddress;},sortable:false,sorttype:'text'},
    {name:'address_line_2',index:'address_line_2',sortable:false,sorttype:'text',hidden:true},
    {name:'address_line_3',index:'address_line_3',sortable:false,sorttype:'text',hidden:true},
    {name:'postcode',label:'postcode',width:80,sortable:false,sorttype:'text'}      
],
jsonReader: {
    cell: "", // the same as cell: function (obj) { return obj; }
    root: "table.rows",
    page: function (obj) { return 1; },
    total: function (obj) { return 1; },
    records: function (obj) { return obj.table.rows.length; }
},

Here's a generic public data example from a .gov table (my table is basically the same setup). I'll tidy up the question later so people can easily see the question/answer :)

<script type="text/javascript"> 
var queryText = "SELECT * FROM 185189";
jQuery(document).ready(function() {
    jQuery("#rTable").jqGrid({
        url: 'http://www.google.com/fusiontables/api/query?sql=' +
              encodeURI(queryText) + '&jsonCallback=?',
        postData: "",
        datatype: "jsonp",
        colNames: ["col1","col2","col3","col4"],
        colModel:[
            {name:'FACID',index:'FACID',key:true,sorttype:'int',sortable:true},
            {name:'FACNAME',index:'FACNAME'},
            {name:'FAC_ADDRESS1',index:'FAC_ADDRESS1',sortable:false,sorttype:'text'},
            {name:'FAC_ADDRESS2',index:'FAC_ADDRESS2',sortable:false,sorttype:'text'}
        ],
        jsonReader: {
            cell: "",
            root: "table.rows",
            page: function (obj) { return 1; },
            total: function (obj) { return 1; },
            records: function (obj) { return obj.table.rows.length; }
        },
        rowNum:10,
        rowList:[10,20,30],
        pager: '#pager2',
        sortname: 'name',
        sortorder: "asc",
        viewrecords: true,
        loadonce: true,
        height: "100%",
        multiselect: true,
        caption: ""
    }); // END CREATE GRID
    jQuery("#rTable").jqGrid('navGrid','#pager2',{edit:false,add:false,del:false}); // paging options
});
</script>

Oleg · Accepted Answer · 2011-10-28T11:44:16.067

You can use custom formatter to construct column contain based on any input data for the row. The rowObject parameter represent the row of data returned from the server. The string returned by the custom formatter is the text or HTML text with will be displayed in the cell.

If you would have implementation problem you should post the URL with the fusion table which you use.

UPDATED: You can solve the problem of composed columns in different ways. The first one work with old version of jqGrid and is just rewriting of the formatter function to the following:

formatter: function (cellvalue, options, rowObject) {
    var rowObject = arguments[2];
    if ($.isArray(rowObject)) {
        return rowObject[4] + rowObject[5] + rowObject[6];
    } else {
        return rowObject.address_line_1 +
            rowObject.address_line_2 +
            rowObject.address_line_3;
    }
}

The small disadvantage of the way is that you will have unneeded hidden columns address_line_2 and address_line_3 which you will not really use.

More elegant solution will be to use new beforeProcessing callback function (event) (see my original suggestion to the feature here). The function will be called only in case of loading the data from the server. It allows you to modify the data returned from the server before the data will be processed by jqGrid. In the case you can event use default jsonReader:

colNames: ["lat", "long", "Name", "Address", "Postcode"],
colModel:[
    {name: 'latitude', hidden: true},
    {name: 'longitude', hidden: true},
    {name: 'name', width: 170},
    {name: 'address_line', width: 400},
    {name: 'postcode', width: 80}
],
cmTemplate: { sortable: false },
beforeProcessing: function (data) {
    var rows = data.table.rows, length = rows.length, i = 0, row;
    data.page = 1;
    data.total = 1;
    data.records = length;
    data.rows = [];
    for (; i < length; i += 1) {
        row = rows[i];
        data.rows.push({
            id: row[0],
            cell: [row[1], row[2], row[3], row[4] + row[5] + row[6], row[7]]
        });
    }
    delete data.table;
}

I don't has your original JSON data and don't tested the code above, but the code shows how you can construct new data based on the original data returned from the server.

thanks for the reply. When I tried using a formatter, it worked on the first page of results, but paging through results, it sets all cells in that column to "undefined". — JPMox, Oct 28 '11 at 07:06
@JPMox: If you would post the URL which you use (like I asked your before) or post the JSON data which you receive from the server I would modify my answer with detailed solution of your problem. — Oleg, Oct 28 '11 at 07:30
it's coming from a private Fusion Table so I can't show you that data. Let me think of an alternative. thanks for your help though. — JPMox, Oct 28 '11 at 07:44
@JPMox: Is it so difficult to place test data in the public Fusion Table? To get JSON data returns from the server you can use [Fiddler](http://www.fiddler2.com/fiddler2/) or [Firebug](http://getfirebug.com/). In any way I will post two ways to solve your problem in the next hours. — Oleg, Oct 28 '11 at 07:48
@@JPMox: I updated my answer with two ways to solve your problem. — Oleg, Oct 28 '11 at 11:03
Thank you very much for your updated answer - my wording was bad in my comment - I meant to say I will find you a public fusion table I can use as an example. The first solution works fine, but so far I haven't had success with your 2nd answer - I get that data must come from a server (it is, Fusion Tables). Not sure what you mean about jsonReader though. Oh, and I noticed google returns the JSON data as type CSV, so I guess that's why it doesn't show up in Firebug http://groups.google.com/group/fusion-tables-users-group/browse_thread/thread/b909820434b5c191 — JPMox, Oct 31 '11 at 06:32

Merge several columns of JSON data and display as single column in jqGrid

1 Answers1

Linked