CODEWITHSUNDEEP
jqueryjqgrid
Michael Sobczak
Michael Sobczak

Reputation: 1085

jqgrid: display blank for null numeric column value

In a jqgrid I'm working on, I'm applying a formatter to several columns based on the data in the first row. So, if the first row contains a content value of "PERCENT", I apply a formtter to the actual value column that formats the value as number with decimal places.

The issue I'm having is that when the columns contain either a numeric value or null, the formatter formats the null values as "0.00".

I set up the following jsFiddle to show what is going on. What I need is for the var1Value, var2Value and var3Value columns to display a blank, and not "0.00", when the json value is null. Is this possible?

https://jsfiddle.net/msobczak/7prbs3tu/6/

The code follows:

var mainGrid = {
  "total": 1,
  "page": 1,
  "pageSize": 20,
  "records": 1,
  "rows": [{
    "id": 7259,
    "var1Name": "2015 Median Age",
    "var1Contents": "MEDIAN",
    "var1IsString": 0,
    "var1IsNumber": 1,
    "var1Value": "44",
    "var2Name": "% '15 HHs",
    "var2Contents": "PERCENT",
    "var2IsString": 0,
    "var2IsNumber": 1,
    "var2Value": "2.07",
    "var3Name": "Wine At Home",
    "var3Contents": "INDEX",
    "var3IsString": 0,
    "var3IsNumber": 1,
    "var3Value": "135"
  }, {
    "id": 7259,
    "var1Name": "2015 Median Age",
    "var1Contents": "MEDIAN",
    "var1IsString": 0,
    "var1IsNumber": 1,
    "var1Value": null,
    "var2Name": "% '15 HHs",
    "var2Contents": "PERCENT",
    "var2IsString": 0,
    "var2IsNumber": 1,
    "var2Value": null,
    "var3Name": "Wine At Home",
    "var3Contents": "INDEX",
    "var3IsString": 0,
    "var3IsNumber": 1,
    "var3Value": null
  }]
};

GridFunctions = {
  formatVariable: function(gridId, columnName, variableValue, variableContents, isNumber) {
    if (variableValue != undefined && variableContents != undefined) {
      switch (variableContents) {
        case "MEDIAN":
          $(gridId).jqGrid("setColProp", columnName, {
            formatter: 'number',
            defaultvalue: null
          });
          $(gridId).jqGrid("setColProp", columnName, {
            formatoptions: {
              thousandsSeparator: ',',
              decimalPlaces: 0
            }
          });
          break;

        case "COUNT":
          $(gridId).jqGrid("setColProp", columnName, {
            formatter: 'number'
          });
          $(gridId).jqGrid("setColProp", columnName, {
            formatoptions: {
              thousandsSeparator: ',',
              decimalPlaces: 0
            }
          });
          break;

        case "RATIO":
          $(gridId).jqGrid("setColProp", columnName, {
            formatter: 'number'
          });
          $(gridId).jqGrid("setColProp", columnName, {
            formatoptions: {
              thousandsSeparator: ',',
              decimalPlaces: 0
            }
          });
          break;

        case "PERCENT":
          $(gridId).jqGrid("setColProp", columnName, {
            formatter: 'number',
            defaultvalue: null
          });
          $(gridId).jqGrid("setColProp", columnName, {
            formatoptions: {
              thousandsSeparator: ',',
              decimalPlaces: 2
            }
          });
          break;

        case "INDEX":
          if (isNumber == 1) {
            $(gridId).jqGrid("setColProp", columnName, {
              formatter: 'number'
            });
            $(gridId).jqGrid("setColProp", columnName, {
              formatoptions: {
                thousandsSeparator: ',',
                decimalPlaces: 0
              }
            });
          }
          break;
      }
    }
  }
}

$(document).ready(function() {
  $("#jqGrid").jqGrid({
    datatype: function(postdata) {

      $('#' + 'load_' + 'jqGrid').show();

      var json = mainGrid;

      var thisGridId = "#jqGrid";

      var columnName = "var1Value";
      var varName = json.rows[0].var1Name;

      // Dynamically change column header for the variable 1 column
      GridFunctions.formatVariable(thisGridId, columnName, json.rows[0].var1Value, json.rows[0].var1Contents, json.rows[0].var1IsNumber);

      varName = json.rows[0].var2Name;
      columnName = "var2Value";

      // Dynamically change column header for the variable 2 column
      GridFunctions.formatVariable(thisGridId, columnName, json.rows[0].var2Value, json.rows[0].var2Contents, json.rows[0].var2IsNumber);

      varName = json.rows[0].var3Name;
      columnName = "var3Value";

      // Dynamically change column header for the variable 3 column
      GridFunctions.formatVariable(thisGridId, columnName, json.rows[0].var3Value, json.rows[0].var3Contents, json.rows[0].var3IsNumber);

      var thegrid = $('#jqGrid')[0];
      thegrid.addJSONData(json);

      $('#' + 'load_' + 'jqGrid').hide();
    },
    page: 1,
    colModel: [{
        label: 'ID',
        name: 'id',
        width: 50,
        hidden: false,
        key: true,
        editable: true,
        sortable: false,
        editrules: {
          edithidden: true
        }
      },
      // Variable 1
      {
        label: 'var1Value',
        name: 'var1Value',
        width: 90,
        sortable: true,
        hidden: false,
        align: 'right'
      },

      // Variable 2
      {
        label: 'var2Value',
        name: 'var2Value',
        width: 90,
        sortable: true,
        hidden: false,
        align: 'right'
      },

      // Variable 3
      {
        label: 'var3Value',
        name: 'var3Value',
        width: 90,
        sortable: true,
        hidden: false,
        align: 'right'
      }
    ],
    viewrecords: true,
    width: 800,
    shrinkToFit: false,
    height: '100%',
    rowNum: 20,
    pager: "#jqGridPager"
  });
});

Upvotes: 2

Views: 5348

Answers (1)

Oleg
Oleg

Reputation: 221997

First of all your code is very difficult to read. If I correctly understand your problem then you just use formatter: "number" and set default value.

You current code uses

$(gridId).jqGrid("setColProp", columnName, {
    formatter: 'number',
    defaultvalue: null
});
$(gridId).jqGrid("setColProp", columnName, {
    formatoptions: {
        thousandsSeparator: ',',
        decimalPlaces: 0
    }
});

which is the same as

$(gridId).jqGrid("setColProp", columnName, {
    formatter: 'number',
    defaultvalue: null,
    formatoptions: {
        thousandsSeparator: ',',
        decimalPlaces: 0
    }
});

The code contains some errors. The correct name of the property which you need is defaultValue instead of defaultvalue and the property need be set inside of formatoptions. If you need to display empty cell then you can use defaultValue: "" or better defaultValue: " ":

$(gridId).jqGrid("setColProp", columnName, {
    formatter: 'number',
    formatoptions: {
        thousandsSeparator: ',',
        decimalPlaces: 0,
        defaultValue: " "
    }
});

Moreover I don't recommend you to use datatype as function. You will disable a lot of helpful features of jqGrid without any advantage. It seems to me that your real code load the data from the server and you want to change column properties based on the data from the server response. You can use datatype: "json" in the case and to use beforeProcessing callback to "preprocess" the data before the data will be processed by jqGrid. See the answer for more details.

Upvotes: 3

Related Questions