Colour Rows Based on Greater than or Less than Ranges

Tips submitted by ASP.NET Maker users
Post Reply
xgis
User
Posts: 68

Colour Rows Based on Greater than or Less than Ranges

Post by xgis »

In some cases you may need to colour fields based on a range of selections.
The example below uses colours to differentiate levels of risk using decimal / float values.
For discrete values (ie integers) you could use "=" and change Convert.ToDouble to Convert.ToInt32
The control example in this case is a drop down with stored values ranging from 1-5.
The colour range is amber to red as any decision indicates risk, whereas no selection (null) indicates no risk.

The code; (replace "EnvERRSoilContVisAssID" with your field name)

if (EnvERRSoilContVisAssID.CurrentValue is System.DBNull) {
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #FF8C69"; }
else if (Convert.ToDouble(EnvERRSoilContVisAssID.CurrentValue) <= 1.5 ){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #FFD966"; }
else if (Convert.ToDouble(EnvERRSoilContVisAssID.CurrentValue) <= 2.5 ){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #F9C475"; }
else if (Convert.ToDouble(EnvERRSoilContVisAssID.CurrentValue) <= 3.5 ){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #F4B084"; }
else if (Convert.ToDouble(EnvERRSoilContVisAssID.CurrentValue) <= 4.5 ){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #F68C77"; }
else if (Convert.ToDouble(EnvERRSoilContVisAssID.CurrentValue) > 4.5 ){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #F8696B"; }


xgis
User
Posts: 68

Post by xgis »

Coding for Discrete values (Integers)

Two changes to the first sample are given below;

  1. Usually I use an Amber colour to represent a field where a decision needs to be made, In this example Amber is already used.
    I have simply changed NULL values to SlateGray to make them standout in this example. eg #C6E2FF
  2. Ignore the line "For discrete values (ie integers) you could use "=" and change Convert.ToDouble to Convert.ToInt32"
    in the previous post. The following code for integers is based on the example in ANM Custom Scripts.
    The following code works for integers;

The code; (replace "EnvERRSoilContVisAssID" with your field name)

//1. EnvERRSoilContVisAssID
if (EnvERRSoilContVisAssID.CurrentValue is System.DBNull) {
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #C6E2FF"; }
else if (ew_SameInt(EnvERRSoilContVisAssID.CurrentValue, 1)){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #FFD966"; }
else if (ew_SameInt(EnvERRSoilContVisAssID.CurrentValue, 2)){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #F9C475"; }
else if (ew_SameInt(EnvERRSoilContVisAssID.CurrentValue, 3)){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #F4B084"; }
else if (ew_SameInt(EnvERRSoilContVisAssID.CurrentValue, 4)){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #F68C77"; }
else if (ew_SameInt(EnvERRSoilContVisAssID.CurrentValue, 5)){
EnvERRSoilContVisAssID.CellCssStyle = "background-color: #F8696B"; }


xgis
User
Posts: 68

Post by xgis »

This is an alternate implementation of the code that is designed for data within one year of an event occurring.
C# often has issues with the way the code is presented when operators are used e.g. (> or <).
The use of the int variable "Days", also reduces verbose and repetitive coding.

The code itself changes the 'CellCssStyle ' to display more intense colours (e.g. Darker RED) as the event draws nearer.
The codes below are used on an MS SQL View (aka query) but are suitable for tables also.
This method is designed to be applicable to multiple views or tables with minimal name changes.
To use, simply replace "DaysTillWedding" with your field name representing your integer field type.

The codes used in the 'Row_Rendered' event;

int Days = Convert.ToInt32(DaysTillWedding.ViewValue); //Render Decision Variable as an Integer

if (DaysTillWedding.CurrentValue is System.DBNull) { 
DaysTillWedding.CellCssStyle = "background-color: #FFE680"; }  //Pastel Heat Map Colours Below
else if ((int)Days < 3)  {
DaysTillWedding.CellCssStyle = "background-color: #C96A67"; } //DkRed (3 Days)
else if ((int)Days < 7) {       
DaysTillWedding.CellCssStyle = "background-color: #CE7876"; } //Red (1 Week)
else if ((int)Days < 14)  {
DaysTillWedding.CellCssStyle = "background-color: #D99795"; } //LightRed (2 Weeks)
else if ((int)Days < 21)  {
DaysTillWedding.CellCssStyle = "background-color: #EAC6C5"; } //LighterRed (3 Weeks)
else if ((int)Days < 30)  {
DaysTillWedding.CellCssStyle = "background-color: #75923C"; } //DkGreen (1 Months)
else if ((int)Days < 60)  {
DaysTillWedding.CellCssStyle = "background-color: #75923C"; } //Green (2 Months)
else if ((int)Days < 180)  {
DaysTillWedding.CellCssStyle = "background-color: #C1CEA7"; } //LightGreen (6 Months)
else if ((int)Days < 365)  {
DaysTillWedding.CellCssStyle = "background-color: #ECF0E4"; } //LighYellowGreen (12 Months)

xgis
User
Posts: 68

Post by xgis »

The sample below is an ANM 12/2017 Row Rendered sample that uses hex gradients.
The method used subtracts dates then converts it to integers.

//DATE DIFFERENCE CELL BACKGROUND GRADIENT COLOURS (MOST RECENT EDITS)
//Blue Colour Scheme Gradient hex 172459 14 steps perbang.dk/rgbgradient

DateTime DateLastEdited = Convert.ToDateTime(SiteEditedDateLast.ViewValue);
DateTime DateToday = Convert.ToDateTime(DateTime.Today);
var DateDifferenceDays = Convert.ToInt32((DateToday - DateLastEdited).TotalDays);

if (DateDifferenceDays <1){
	SiteEditedDateLast.CellCssStyle = "background-color: #172459; color: #FFFFFF";}	//contrasts cell text with white on dark blue background
	else if ((DateDifferenceDays >= 1) & (DateDifferenceDays <= 3)){
	SiteEditedDateLast.CellCssStyle = "background-color: #172459; color: #E5E5E5";}	
	else if ((DateDifferenceDays > 3) & (DateDifferenceDays <= 5)){
	SiteEditedDateLast.CellCssStyle = "background-color: #283465";}	
	else if ((DateDifferenceDays > 5) & (DateDifferenceDays <= 7)){
	SiteEditedDateLast.CellCssStyle = "background-color: #3a4572";}	
	else if ((DateDifferenceDays > 7) & (DateDifferenceDays <= 14)){
	SiteEditedDateLast.CellCssStyle = "background-color:#4c567f";}	
	else if ((DateDifferenceDays > 14) & (DateDifferenceDays <= 30)){
	SiteEditedDateLast.CellCssStyle = "background-color: #5e678c";}	
	else if ((DateDifferenceDays > 30) & (DateDifferenceDays <= 60)){
	SiteEditedDateLast.CellCssStyle = "background-color: #707898";}	
	else if ((DateDifferenceDays > 60) & (DateDifferenceDays <= 90)){
	SiteEditedDateLast.CellCssStyle = "background-color: #8289a5";}	
	else if ((DateDifferenceDays > 90) & (DateDifferenceDays <= 180)){
	SiteEditedDateLast.CellCssStyle = "background-color: #9399b2";}	
	else if ((DateDifferenceDays > 180) & (DateDifferenceDays <= 365)){
	SiteEditedDateLast.CellCssStyle = "background-color: #a5aabf";}	
	else if ((DateDifferenceDays > 365) & (DateDifferenceDays <= 730)){
	SiteEditedDateLast.CellCssStyle = "background-color: #b7bbcb";}	
	else if ((DateDifferenceDays > 730) & (DateDifferenceDays <= 1825)){
	SiteEditedDateLast.CellCssStyle = "background-color: #c9ccd8";}	
	else if ((DateDifferenceDays > 1825) & (DateDifferenceDays <= 3650)){
	SiteEditedDateLast.CellCssStyle = "background-color: #dbdde5";}

xgis
User
Posts: 68

Post by xgis »

// How to Deal with the Date fields that have NULL values
//START InspectionDate (Some of the date fields have NULL Values)

if (InspectionDate.CurrentValue is System.DBNull) {
InspectionDate.CellCssStyle = "background-color: #FFE680"; }

//If DATE NOT NULL then do this
else if (InspectionDate.CurrentValue != null){

DateTime DateInspectionDate = Convert.ToDateTime(InspectionDate.CurrentValue);
DateTime DateToday2 = DateTime.Today;
var DateDifferenceDays2 = Convert.ToInt32((DateToday2 - DateInspectionDate).TotalDays);

	if (DateDifferenceDays2 <1){
	InspectionDate.CellCssStyle = "background-color: #172459; color: #FFDF00";}
	else if ((DateDifferenceDays2 >= 1) & (DateDifferenceDays2 <= 3)){
	InspectionDate.CellCssStyle = "background-color: #172459; color: #FFFFFF";}	
	else if ((DateDifferenceDays2 > 3) & (DateDifferenceDays2 <= 5)){
	InspectionDate.CellCssStyle = "background-color: #283465; color: #FFFFFF";}		
	else if ((DateDifferenceDays2 > 5) & (DateDifferenceDays2 <= 7)){
	InspectionDate.CellCssStyle = "background-color: #3a4572; color: #FFFFFF";}		
	else if ((DateDifferenceDays2 > 7) & (DateDifferenceDays2 <= 14)){
	InspectionDate.CellCssStyle = "background-color:#4c567f; color: #FFFFFF";}		
	else if ((DateDifferenceDays2 > 14) & (DateDifferenceDays2 <= 30)){
	InspectionDate.CellCssStyle = "background-color: #5e678c; color: #FFFFFF";}
	else if ((DateDifferenceDays2 > 30) & (DateDifferenceDays2 <= 60)){
	InspectionDate.CellCssStyle = "background-color: #707898";}	
	else if ((DateDifferenceDays2 > 60) & (DateDifferenceDays2 <= 90)){
	InspectionDate.CellCssStyle = "background-color: #8289a5";}	
	else if ((DateDifferenceDays2 > 90) & (DateDifferenceDays2 <= 180)){
	InspectionDate.CellCssStyle = "background-color: #9399b2";}	
	else if ((DateDifferenceDays2 > 180) & (DateDifferenceDays2 <= 365)){
	InspectionDate.CellCssStyle = "background-color: #a5aabf";}	
	else if ((DateDifferenceDays2 > 365) & (DateDifferenceDays2 <= 730)){
	InspectionDate.CellCssStyle = "background-color: #b7bbcb";}	
	else if ((DateDifferenceDays2 > 730) & (DateDifferenceDays2 <= 1825)){
	InspectionDate.CellCssStyle = "background-color: #c9ccd8";}	
	else if ((DateDifferenceDays2 > 1825) & (DateDifferenceDays2 <= 3650)){
	InspectionDate.CellCssStyle = "background-color: #dbdde5";}
	else if ((DateDifferenceDays2 > 3650) & (DateDifferenceDays2 <= 36500)){
	InspectionDate.CellCssStyle = "background-color: #dee0e7";}
	else if ((DateDifferenceDays2 > 36500) & (DateDifferenceDays2 <= 3650000)){
	InspectionDate.CellCssStyle = "background-color: #CD2626";}
           //the last check here is for dates that may have been added that are clearly wrong e.g year 1754
	}

//FINISH InspectionDate


xgis
User
Posts: 68

Post by xgis »

The success of the last 2 code samples above is dependent on the datetime format, for the relevant date field in ANM.

The Date/Time named format in the case of DateTime that includes Time is set to "DateTime" Format
Alternatively it may be left as "Default", but NULL values may cause issues.

In the case the field is only displaying short date eg "31/12/2017" then you can use format "dmY"

Troubleshooting
In the event you receive a "String was not recognised as a valid DateTime" changing the ANM date format should resolve the issue.
This code can be touchy and was only tested against MSSQL.

Duplicating the code to other tables requires you to pay close attention to the renamed variables.
If at all possible implement a naming convention that allows you to do a single word copy paste in notepad or Visual Studio Code.
eg InspectionEditedDateLast vs WorksEditedDateLast


xgis
User
Posts: 68

Post by xgis »

// Better version of DateTime Colour Ranges with NULL Check to prevent errors encountered from earlier post of same type of code

		if (InspectionEditedDateLast.CurrentValue != System.DBNull.Value)

		{
			DateTime DateLastEdited = Convert.ToDateTime(InspectionEditedDateLast.CurrentValue);
			DateTime DateToday = Convert.ToDateTime(DateTime.Today);
			var DateDifferenceDays = Convert.ToInt32((DateToday - DateLastEdited).TotalDays);

			if (DateDifferenceDays < 1)
			{
				InspectionEditedDateLast.CellCssStyle = "background-color: #172459; color: #FFFFFF";
			}
			//contrasts cell text with white on dark blue background
			else if ((DateDifferenceDays >= 1) & (DateDifferenceDays <= 3))
			{
				InspectionEditedDateLast.CellCssStyle = "background-color: #172459; color: #E5E5E5";
			}
			else if ((DateDifferenceDays > 3) & (DateDifferenceDays <= 5))
			{
				InspectionEditedDateLast.CellCssStyle = "background-color: #283465";
			}
			else if ((DateDifferenceDays > 5) & (DateDifferenceDays <= 7))
			{
				InspectionEditedDateLast.CellCssStyle = "background-color: #3a4572";
			}
			else if ((DateDifferenceDays > 7) & (DateDifferenceDays <= 14))
			{
				InspectionEditedDateLast.CellCssStyle = "background-color:#4c567f";
			}
			else if ((DateDifferenceDays > 14) & (DateDifferenceDays <= 30))
			{
				InspectionEditedDateLast.CellCssStyle = "background-color: #5e678c";
			}
			else if ((DateDifferenceDays > 30) & (DateDifferenceDays <= 60))
			{
				InspectionEditedDateLast.CellCssStyle = "background-color: #707898";
			}
			else if ((DateDifferenceDays > 60) & (DateDifferenceDays <= 90))
			{
				InspectionEditedDateLast.CellCssStyle = "background-color: #8289a5";
			}
			else if ((DateDifferenceDays > 90) & (DateDifferenceDays <= 180))
			{
				InspectionEditedDateLast.CellCssStyle = "background-color: #9399b2";
			}
			else if ((DateDifferenceDays > 180) & (DateDifferenceDays <= 365))
			{
				InspectionEditedDateLast.CellCssStyle = "background-color: #a5aabf";
			}
			else if ((DateDifferenceDays > 365) & (DateDifferenceDays <= 730))
			{
				InspectionEditedDateLast.CellCssStyle = "background-color: #b7bbcb";
			}
			else if ((DateDifferenceDays > 730) & (DateDifferenceDays <= 1825))
			{
				InspectionEditedDateLast.CellCssStyle = "background-color: #c9ccd8";
			}
			else if ((DateDifferenceDays > 1825) & (DateDifferenceDays <= 3650))
			{
				InspectionEditedDateLast.CellCssStyle = "background-color: #dbdde5";
			}


			else { }

		}
		else
		{
		}

Post Reply