Unable to color cell based on cell value on report

SaranyaAlagar · September 11, 2018, 7:11am

frappe.query_reports[“Custom Stock Projected Qty”] = {
“filters”: [
{
“fieldname”:“company”,
“label”: __(“Company”),
“fieldtype”: “Link”,
“options”: “Company”
},
{
“fieldname”:“warehouse”,
“label”: __(“Warehouse”),
“fieldtype”: “Link”,
“options”: “Warehouse”
},
{
“fieldname”:“item_code”,
“label”: __(“Item”),
“fieldtype”: “Link”,
“options”: “Item”,

	},
	{
		"fieldname":"brand",
		"label": __("Brand"),
		"fieldtype": "Link",
		"options": "Brand"
	}
	],
			
	"formatter":function (row, cell, value, columnDef, dataContext, default_formatter) {
		value = default_formatter(row, cell, value, columnDef, dataContext);
	       	if (columnDef.id != "Actual Qty" && dataContext["Actual Qty"] < 20) {
		    value = "<span style='color:red!important;font-weight:bold'>" + value + "</span>";
	       	}
	       	if (columnDef.id != "Actual Qty" && dataContext["Actual Qty"] > 20) {
			value = "<span style='color:green!important;font-weight:bold'>" + value + "</span>";
	       	}
	       return value; 

		}

};

The above script is Stock Projected qty report .In this i am unable to color the cell …when i used this script it display empty only filter columns are visible . In this report need to color the value of Actual qty . can any one tell what was the error in my script…?? How to solve this issue??

shahid · September 11, 2018, 3:30pm

Why are you using not equals to operator in columndef.id?
columnDef.id != "Actual Qty"

I think it should be
columnDef.id == "Actual Qty"

SaranyaAlagar · September 12, 2018, 4:08am

@shahid
when columnDef.id == “Actual Qty” that case also not working …i have tried that one …

komsel2228 · September 12, 2018, 4:29am

columnDef.id == “ActualQty”

SaranyaAlagar · September 12, 2018, 4:49am

@komsel2228

columnDef.id == “ActualQty” this case also not working…

SaranyaAlagar · September 12, 2018, 5:30am

frappe.query_reports[“Custom Stock Projected Qty”] = {
“filters”: [
{
“fieldname”:“company”,
“label”: __(“Company”),
“fieldtype”: “Link”,
“options”: “Company”
},
{
“fieldname”:“warehouse”,
“label”: __(“Warehouse”),
“fieldtype”: “Link”,
“options”: “Warehouse”
},
{
“fieldname”:“item_code”,
“label”: __(“Item”),
“fieldtype”: “Link”,
“options”: “Item”,
“get_query”: function() {
return {
query: “erpnext.controllers.queries.item_query”
}
}
},
{
“fieldname”:“brand”,
“label”: __(“Brand”),
“fieldtype”: “Link”,
“options”: “Brand”
}
],

"formatter":function (row, cell, value, columnDef, dataContext, default_formatter) {
	value = default_formatter(row, cell, value, columnDef, dataContext);
       	if (columnDef.id == "ActualQty" && dataContext["Actual Qty"] < 20) {
	    value = "<span style='color:red!important;font-weight:bold'>" + value + "</span>";
       	}
       	if (columnDef.id == "ActualQty" && dataContext["Actual Qty"] > 20) {
		value = "<span style='color:green!important;font-weight:bold'>" + value + "</span>";
       	}
       return value; 

	}

}

I have changed the script …still not working …The report remains blank … Where to fix the issue??

komsel2228 · September 12, 2018, 5:54am

“formatter”:function (row, cell, value, columnDef, dataContext, default_formatter) {
value = default_formatter(row, cell, value, columnDef, dataContext);
if (columnDef.id == “Actual Qty”) {
if(dataContext[“Actual Qty”] < 20){
value = “” + value + “”;
}else{
value = “” + value + “”;
}
}

					return value;
			}

SaranyaAlagar · September 12, 2018, 6:26am

@komsel2228

“formatter”:function (row, cell, value, columnDef, dataContext, default_formatter) {
value = default_formatter(row, cell, value, columnDef, dataContext);
if (columnDef.id == “Actual Qty”) {
if(dataContext[“Actual Qty”] < 20){
value = “” + value + “”;
}else{
value = “” + value + “”;
}
}
return value;
}

The above code is not working… The report is not generated .

komsel2228 · September 12, 2018, 6:37am

st2
st1

i dont know, why not working. but, in my report its working.

SaranyaAlagar · September 12, 2018, 6:53am

@komsel2228
Thanks @komsel2228 …
yes ,it is working on your report. But i dont know where is bug in my code ?? If it possible means can u share sample code ?? …

komsel2228 · September 12, 2018, 7:05am

frappe.query_reports[“Stock Projected Qty”] = {
“filters”: [
{
“fieldname”:“company”,
“label”: __(“Company”),
“fieldtype”: “Link”,
“options”: “Company”
},
{
“fieldname”:“warehouse”,
“label”: __(“Warehouse”),
“fieldtype”: “Link”,
“options”: “Warehouse”
},
{
“fieldname”:“item_code”,
“label”: __(“Item”),
“fieldtype”: “Link”,
“options”: “Item”,
“get_query”: function() {
return {
query: “erpnext.controllers.queries.item_query”
}
}
},
{
“fieldname”:“brand”,
“label”: __(“Brand”),
“fieldtype”: “Link”,
“options”: “Brand”
}
],
“formatter”:function (row, cell, value, columnDef, dataContext, default_formatter) {
value = default_formatter(row, cell, value, columnDef, dataContext);
if (columnDef.id == “Actual Qty”) {
if(dataContext[“Actual Qty”] < 20){
value = “” + value + “”;
}else{
value = “” + value + “”;
}
}

					return value;
			}

}

SaranyaAlagar · September 12, 2018, 7:09am

@komsel2228
Thanks for sharing the code @komsel2228 … Is anything need to change in py file of report ??..

komsel2228 · September 12, 2018, 7:15am

i did not change py file.

SaranyaAlagar · September 12, 2018, 7:21am

@komsel2228
ok @komsel2228

value = “” + value + “”;

Here where you have assigned the color ?? … the value of Actual Qty less than 20 should be highlighted in red color…

komsel2228 · September 12, 2018, 7:32am

SaranyaAlagar · September 12, 2018, 8:10am

@komsel2228
Thank you @komsel2228 for sharing the code …