2

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"
  });
});
Michael Sobczak
  • 1,045
  • 1
  • 24
  • 45

1 Answers1

3

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.

Community
  • 1
  • 1
Oleg
  • 220,925
  • 34
  • 403
  • 798
  • Hi Oleg! First off, thank you for helping me with the defaultValue. The solution works perfectly! Second, I went with the datatype as function because I'm getting data via an asynchronous service call from within Salesforce. Long story short, this type of call does not use a url, but instead requires the page to make an asynchronous call to a service using a Javascript function with parameters. Is there an example of using a datatype of "json" where an asynchronous call is made via a JavaScript function? – Michael Sobczak Feb 04 '16 at 23:35
  • @MichaelSobczak: It seems be the problem, which independent from your current question. An important point is: whether you really have large set of data and you need **server side** paging. jqGrid works very effective for relatively large set of data. Try [the demo](http://www.ok-soft-gmbh.com/jqGrid/OK/performane-13-4000-20-free-jqgrid.htm) with 13 columns and 4000 rows with teh page size 20 and [this one](http://www.ok-soft-gmbh.com/jqGrid/OK/performane-13-40000-20-free-jqgrid.htm) with 40000 rows. If you would load the data at once and then use `datatype: "local"`, all will be simple. – Oleg Feb 04 '16 at 23:43
  • @MichaelSobczak: I don't use Salesforce myself and don't know it at all, but I found [the article](http://www.oyecode.com/2014/02/how-to-salesforce-rest-api-from-browser.html) which looks for me that one needs just set some headers in Ajax request and then one can use standard jQuery.ajax and so the standard `datatype: "json"` of jqGrid. The use the same code in jqGrid you need just use `loadBeforeSend` callback instead of `beforeSend`. – Oleg Feb 04 '16 at 23:51
  • In the article you found, the programmer is essentially making a REST query to Salesforce itself. I'm querying a REST service external to Salesforce, using a Salesforce REST service as a proxy. The external REST service is using paging. Where I work, we have very large data sets that are generated by PL/SQL code, and we don't want the user to wait for a long time for the external service to generate the 40000 rows and send it back to the page. It would be impractical for what we need to do. So, we do paging at the server side. – Michael Sobczak Feb 04 '16 at 23:57
  • Updated jsFiddle, in case anyone would like to see an updated working version: https://jsfiddle.net/msobczak/7prbs3tu/7/ – Michael Sobczak Feb 04 '16 at 23:59
  • I hit the upvote but forgot the accept part. Sorry! I'm OK with what I have for now. I really do appreciate your help! – Michael Sobczak Feb 05 '16 at 12:09
  • @MichaelSobczak: You are welcome! Accepting and voting are very important to allow other people to find the posts on stackoverflow. Even Google use custom searching engine for stackoverflow and take in consideration whether the answer is accepted and which vote count it has. It the information will be place on the 20-th page of searching results it will be never found by other user. Just because of that I remind don't forget accepting and voting (not only my answers of cause) of every information which you find helpful on stackoverflow. – Oleg Feb 05 '16 at 12:32