How to set the color of one cell depending on the value of another cell?

[Dror SaddanMember]

In my application we display a list of invoices. An invoice can have one of three statuses: 0=Open , 1=Closed,  2=Canceled.

The JSON data is similar to this: [{“InvoiceNumber”: 1003, “Status”: 1}, {“InvoiceNumber”: 1005, “Status”: 3}]

I need to set the invoice number to green for “Closed” invoices (status=1) and to red for “Open” invoices (status=0), and so on … ?

My problem is this: The cellsrenderer function available in the columns property of the grid receives only the value of the cell at hand (e.g. the invoice number), and I need to somehow fetch the value of the status cell.

The best hack I could conceive is this:

Put the Status column first, use cellsrendered to save its value, and then use the value later in the invoice number cellsrenderer.

     var statusId = 0;
            var statusSaver = function (row, columnfield, value) {
                statusId = value;
            }
            var cellsrenderer = function (row, columnfield, value, defaulthtml, columnproperties) {
                //debugger;
                if (statusId  == 0) {
                    return '<span style="margin: 4px; float: ' + columnproperties.cellsalign + '; color: #ff0000;">' + value + '</span>';
                }
                else if (statusId  == 1) {
                    return '<span style="margin: 4px; float: ' + columnproperties.cellsalign + '; color: #00ff00;">' + value + '</span>';
                }
                else if (statusId  == 2) {
                    return '<span style="margin: 4px; float: ' + columnproperties.cellsalign + '; color: #9B30FF; ">' + value + '</span>';
                }
            }
columns: [
                    { text: 'Status', datafield: 'StatusID', width: 30, cellsalign: 'right', cellsrenderer: statusSaver }
                  , { text: 'Total 1', datafield: 'Total1', cellsformat: 'f2', width: 150, cellsalign: 'right' }
                  , { text: 'Total No Vat', datafield: 'TotalNoVat', cellsformat: 'f2', width: 150, cellsalign: 'right' }
                  , { text: 'Business Name', datafield: 'BusinessName', width: 100, minwidth: 90, maxwidth: 150, cellsalign: 'right' }
                  , { text: 'Date', datafield: 'InvoiceDate', cellsformat: 'd/M/yyyy', width: 150, cellsalign: 'right' }
                  , { text: 'Number', datafield: 'InvoiceNumber', width: 50, cellsalign: 'right', cellsrenderer: cellsrenderer }</pre>
Is there a better solution?

Dror

[Peter StoevKeymaster]

Hi Dror Saddan,

You can use the ‘getrowdata’ method to get the data record depending on the passed as parameter record index:

For example:

$("#jqxgrid").jqxGrid('getrowdata', 0)

You can call the ‘getrowdata’ in the ‘cellsrenderer’ callback function and check the value of the ‘Status’. Depending on this value, return the appropriate HTML string.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[Dror SaddanMember]

Thank you. It works great:

            var invoiceNumRenderer = function (row, columnfield, value, defaulthtml, columnproperties) {
  
                var theStatus = $("#jqxgrid").jqxGrid('getrowdata', row).StatusID;
                if (theStatus == 0) {
                    return '<span style="margin: 4px;float: ' + columnproperties.cellsalign + ';color: #ff0000;font-weight: bold">' + value + '</span>';
                }
                else if (theStatus == 2) {
                    return '<span style="margin: 4px;float: ' + columnproperties.cellsalign + ';color: #006638;font-weight: bold">' + value + '</span>';
                }
                else if (theStatus == 3) {
                    return '<span style="margin: 4px;float: ' + columnproperties.cellsalign + ';color: #9B30FF;font-weight: bold">' + value + '</span>';
                }
            };

Dror

[mail2harishaMember]

Hi All,

I have similar requirement.

Requirement:
I am using grid to display many invoices stored in two different databases one in local and another external system. Each row contains “n” number of columns. While displaying if there is any mismatch in any column of a row, i have to highlight in different colors and i have to show the mismatched value in another system in tool tip when hovered on that colored column.

Mismatch can be on any column.

I am retrieving all the data from local database in JSON format:

[{"id":"1","ref":"20120424/31","buy":"buy3","nominal":"nominal3","instrument":"instrument3","rate":"6","start_date":"04/27/2012","status":"MISMATCH"},{"id":"7","ref":"20120424/35","buy":"buy","nominal":"actual","instrument":"i6","rate":"7","start_date":"4/24/2012","status":"NO_MISMATCH"}]

Below are my questions:
1. What would be the most efficient way of getting mismatch data from server?
Example: If “rate” field of first invoice is having mismatch.
Local database = 6
External = 6.5

Then should I have to form a single JSON object and send to grid like:

[{"id":"1","ref":"20120424/31","buy":"buy3","nominal":"nominal3","instrument":"instrument3","rate":"6","start_date":"04/27/2012","status":"MISMATCH","rate_MISMATCH":"6.5"},{"id":"7","ref":"20120424/35","buy":"buy","nominal":"actual","instrument":"i6","rate":"6","start_date":"4/24/2012"}]
and iterate each rowdata to find is there any field with key "property_name"_MISMATCH.

2. What would be the most efficient way of displaying mismatch data?
Current thought solution:

var colorrenderer = function (row, columnfield, value, defaulthtml, columnproperties) {
	                var theStatus = $("#jqxgrid").jqxGrid('getrowdata', row).status;
	                if (theStatus == 'MISMATCH') {
                             var prop = columnfield + "_MISMATCH";
                             if(row[prop] != null) {
	                              return '<table bgcolor="#F5C9C9" width="100%"><tr><td><span style="margin: 4px;float: ' + columnproperties.cellsalign + '">' + value + '</span></td></tr></table>';
                                       // Show tooltip with that value
	                     }
                        }
	                else {
	                    return '<span style="margin: 4px;float: ' + columnproperties.cellsalign + '">' + value + '</span>';
	                }
	                
	            };
	            
				$("#jqxgrid").jqxGrid({
					width : 1200,
					source : dataAdapter,
					editable : true,
					theme : theme,
					selectionmode : 'singlecell',
					sortable: true,
	                altrows: true,
					columns : [ {
						text : 'eBlotter Ref',
						datafield : 'eblotter_ref',
						width : 200, cellsrenderer: colorrenderer 
					}, {
						text : 'B/S/U/A',
						datafield : 'buy',
						width : 200, cellsrenderer: colorrenderer
					}, {
						text : 'Nominal',
						datafield : 'nominal',
						width : 300, cellsrenderer: colorrenderer
					},

[ryan paulParticipant]

what is the use of columnfield?
is there any way to add the value of another column?
for exmple if i have 
column1 column2
a            1            
b            2
c            3
instead, i would like to have the output for the grid to be
column1 column2
a            a1
b            b2
c            c3

[Author]

Posts