Conditionally Change Background and Text Color

[pmcfarlain]

I'd like to add some HTML to my Datapage to conditionally change the color of a report's background and text color based on the value of a calculated field. This field finds the number of days left until a project is due. I'd like to change the text color of this field to red and the background to yellow if it is past due (if the days left are negative). 

I know there are a couple of other pre-existing threads on this topic but for some reason I am having no luck. I pasted these in the source of my header but they had no effect. Let me know if you see anything wrong with my formulas:

//for the background
<a id="visi[@calcfield:1#]">

<script>
var isi = document.getElementById("visi[@calcfield:1#]");
if(isi < 0){
isi.parentNode.parentNode.style.backgroundColor = 'red';
}
else{
isi.parentNode.parentNode.style.backgroundColor = '#NoColor';
}
</script></a> 

//for the text
<div id="mydiv[@calcfield:1#]"> </div>

<SCRIPT LANGUAGE="JavaScript">

if ("[@calcfield:1#]" < "0"){ document.getElementById("mydiv[@calcfield:1#]").innerHTML ="<span style='color:red;'> [@calcfield:1#] </span>"; }

</SCRIPT>

 

[TellMeWhy]

22 minutes ago, pmcfarlain said:

I'd like to add some HTML to my Datapage to conditionally change the color of a report's background and text color based on the value of a calculated field. This field finds the number of days left until a project is due. I'd like to change the text color of this field to red and the background to yellow if it is past due (if the days left are negative). 

I know there are a couple of other pre-existing threads on this topic but for some reason I am having no luck. I pasted these in the source of my header but they had no effect. Let me know if you see anything wrong with my formulas:

//for the background
<a id="visi[@calcfield:1#]">

<script>
var isi = document.getElementById("visi[@calcfield:1#]");
if(isi < 0){
isi.parentNode.parentNode.style.backgroundColor = 'red';
}
else{
isi.parentNode.parentNode.style.backgroundColor = '#NoColor';
}
</script></a> 

//for the text
<div id="mydiv[@calcfield:1#]"> </div>

<SCRIPT LANGUAGE="JavaScript">

if ("[@calcfield:1#]" < "0"){ document.getElementById("mydiv[@calcfield:1#]").innerHTML ="<span style='color:red;'> [@calcfield:1#] </span>"; }

</SCRIPT>

 

You only need 1 HTML Block

 

<div id="visi[@field:Report_ID]"> [@calcfield:1]</div>

<script>
var isi = document.getElementById("visi[@field:Report_ID]");
if([@calcfield:1#] > 47){

isi.parentNode.parentNode.style.backgroundColor = 'red'; //background color

isi.style.color="yellow"; //font color
}
else{
isi.parentNode.parentNode.style.backgroundColor = '#NoColor';
}
</script>

You need to use UNIQUE Field for the id, my unique field is Report_ID

You can also insert the calc field in the div already, and then just change the font color base on the conditions.

[Kurumi]

Hi @pmcfarlain - You may refer to this forum post to hide columns in Tabular Report:

 

[TellMeWhy]

3 hours ago, pmcfarlain said:

Oh I don't need it to apply across the entire row. Just a couple of select columns. Here's the result I got from that code:

I want it to apply to the PO Ship Date and the Expected Ship Date columns (both of which I have as HTML blocks with the code) but for some reason it only highlights the first HTML block red (I've tried with PO Ship in front and with Expected Ship in front it only applies to whichever is first).  Here's the code I have in each HTML block (with Expected Ship instead of PO Ship).

<div id="visi[@field:tbl_ggl_job_lines_Line_Number]">[@field:PO_Ship_Date*]</div>
<script>
var isi = document.getElementById("visi[@field:tbl_ggl_job_lines_Line_Number]");
if('[@field:PO_Ship_Date*]' < '[@cbTimestamp*]'){
var elems = isi.parentNode.parentNode.querySelectorAll("td");

isi.style.color="red"; //font color

}
</script>

But this is the result I get from that code:

Oh, then it's much simpler, just replace 1 to what number of column that is, add another line if you want to set font color for another one, then set the column number

isi.parentNode.parentNode.querySelector("td:nth-child(1)").style.color="red";

 

[pmcfarlain]

37 minutes ago, TellMeWhy said:

You only need 1 HTML Block

 

<div id="visi[@field:Report_ID]"> [@calcfield:1]</div>

<script>
var isi = document.getElementById("visi[@field:Report_ID]");
if([@calcfield:1#] > 47){

isi.parentNode.parentNode.style.backgroundColor = 'red'; //background color

isi.style.color="yellow"; //font color
}
else{
isi.parentNode.parentNode.style.backgroundColor = '#NoColor';
}
</script>

You need to use UNIQUE Field for the id, my unique field is Report_ID

You can also insert the calc field in the div already, and then just change the font color base on the conditions.

Thanks for your response! I pasted this code into an HTML block and swapped "Report_ID" for my unique ID but it's still not showing any color. Here's what's in my HTML block:

<div id="visi[@field:job_Job]">[@calcfield:1#]</div>
<script>
var isi = document.getElementById("visi[@field:job_Job]");
if([@calcfield:1#] < 0){

isi.parentNode.parentNode.style.backgroundColor = 'red'; //background color

isi.style.color="yellow"; //font color
}
else{
isi.parentNode.parentNode.style.backgroundColor = '#NoColor';
}
</script>

And here's the result (HTML on the left, original on the right):

Here's the link to my test page: https://c1dcs476.caspio.com/dp/66ca9000fb6b84ef7b9a462ba0a2

[TellMeWhy]

6 minutes ago, pmcfarlain said:

Thanks for your response! I pasted this code into an HTML block and swapped "Report_ID" for my unique ID but it's still not showing any color. Here's what's in my HTML block:

<div id="visi[@field:job_Job]">[@calcfield:1#]</div>
<script>
var isi = document.getElementById("visi[@field:job_Job]");
if([@calcfield:1#] < 0){

isi.parentNode.parentNode.style.backgroundColor = 'red'; //background color

isi.style.color="yellow"; //font color
}
else{
isi.parentNode.parentNode.style.backgroundColor = '#NoColor';
}
</script>

And here's the result (HTML on the left, original on the right):

Here's the link to my test page: https://c1dcs476.caspio.com/dp/66ca9000fb6b84ef7b9a462ba0a2

Hmm, can you screenshot your HTML Block code? Screenshot your calculated field as well

[pmcfarlain]

Sure. Here's the two blocks:

The calculated field is just taking the difference in days between today and the due date.

[pmcfarlain]

Realized that because of the way my view is set up the Job Number is not a unique field so I changed it to the Line Number which is a GUID. Still no luck. 

<div id="visi[@field:tbl_ggl_job_lines_Line_Number#]">[@calcfield:1#]</div>
<script>
var isi = document.getElementById("visi[@field:tbl_ggl_job_lines_Line_Number#]");
if([@calcfield:1#] < 0){

isi.parentNode.parentNode.style.backgroundColor = 'red'; //background color

isi.style.color="yellow"; //font color
}
else{
isi.parentNode.parentNode.style.backgroundColor = '#NoColor';
}
</script>

Let me know if you see any issues with the updated code.

[TellMeWhy]

3 minutes ago, pmcfarlain said:

Realized that because of the way my view is set up the Job Number is not a unique field so I changed it to the Line Number which is a GUID. Still no luck. 

<div id="visi[@field:tbl_ggl_job_lines_Line_Number#]">[@calcfield:1#]</div>
<script>
var isi = document.getElementById("visi[@field:tbl_ggl_job_lines_Line_Number#]");
if([@calcfield:1#] < 0){

isi.parentNode.parentNode.style.backgroundColor = 'red'; //background color

isi.style.color="yellow"; //font color
}
else{
isi.parentNode.parentNode.style.backgroundColor = '#NoColor';
}
</script>

Let me know if you see any issues with the updated code.

I've tested this and it should work, it thought it was the datediff formula, but it's still working on mine. I've refreshed your page and it seems to be working now, try changing the color for the ELSE part

[pmcfarlain]

Just realized it was the # after the calcfield causing the issue. Removing it fixed this. 

<div id="visi[@field:tbl_ggl_job_lines_Line_Number]">[@calcfield:1]</div>
<script>
var isi = document.getElementById("visi[@field:tbl_ggl_job_lines_Line_Number]");
if([@calcfield:1] < 0){

isi.parentNode.parentNode.style.backgroundColor = 'yellow'; //background color

isi.style.color="red"; //font color
}
else{
isi.parentNode.parentNode.style.backgroundColor = '#NoColor';
}
</script>

So now that this field is displaying the calculated value, I have 2 "Days" fields. In the tabular report I don't see a way to hide the calculated value. Is there a way I can calculate it in the HTML block?

[pmcfarlain]

On 7/12/2021 at 1:49 PM, TellMeWhy said:

I've tested this and it should work, it thought it was the datediff formula, but it's still working on mine. I've refreshed your page and it seems to be working now, try changing the color for the ELSE part

Hi again, I have a related question. I'm looking for a similar outcome, if the due date has passed then highlight but instead of having a number of days, I now only have a date. Here is the code I'm currently trying to use:

<div id="visi[@field:lines_Line_Number]">[@field:dates_Customer_Expected_Date*]</div>
<script>
var isi = document.getElementById("visi[@field:lines_Line_Number]");
if([@field:dates_Customer_Expected_Date*] < [@cbTimestamp*]){

isi.parentNode.parentNode.style.backgroundColor = 'yellow'; //background color

isi.style.color="red"; //font color
}
else{
isi.parentNode.parentNode.style.backgroundColor = '#NoColor';
}
</script>

But it only highlights dates in 06/2021 or 08/2021. Is there a different way to compare dates?

Thanks!

[Kurumi]

Hi @pmcfarlain - you may check these forum posts:

 

[TellMeWhy]

22 hours ago, pmcfarlain said:

Hi again, I have a related question. I'm looking for a similar outcome, if the due date has passed then highlight but instead of having a number of days, I now only have a date. Here is the code I'm currently trying to use:

<div id="visi[@field:lines_Line_Number]">[@field:dates_Customer_Expected_Date*]</div>
<script>
var isi = document.getElementById("visi[@field:lines_Line_Number]");
if([@field:dates_Customer_Expected_Date*] < [@cbTimestamp*]){

isi.parentNode.parentNode.style.backgroundColor = 'yellow'; //background color

isi.style.color="red"; //font color
}
else{
isi.parentNode.parentNode.style.backgroundColor = '#NoColor';
}
</script>

But it only highlights dates in 06/2021 or 08/2021. Is there a different way to compare dates?

Thanks!

I think you're just missing quotation?
 

This worked perfectly on mine even on different dates

if('[@field:Date*]' < '[@cbTimestamp*]'){

 

[pmcfarlain]

2 hours ago, TellMeWhy said:

I think you're just missing quotation?
 

This worked perfectly on mine even on different dates

if('[@field:Date*]' < '[@cbTimestamp*]'){

 

Yes, thanks! The quotation seems to fix the issue. But it is only working on one column at a time. I need to be able to conditionally make the font in multiple different columns red. How can I do this?

(both columns should be red but only the first one turns red)

[TellMeWhy]

13 minutes ago, pmcfarlain said:

Yes, thanks! The quotation seems to fix the issue. But it is only working on one column at a time. I need to be able to conditionally make the font in multiple different columns red. How can I do this?

Ah, I see, it seems that changing the row's font color does not work, you woul have to change each cell individually, try adding this, just below the backgroundcolor code, still inside the if (or else if you're putting it there)
 

var elems = isi.parentNode.parentNode.querySelectorAll("td");

for (var i=0;i < elems.length;i++){
elems[i].style.color="red";

}

What this does is select all the td in that specific row, then apply red font color to each one

[pmcfarlain]

Oh I don't need it to apply across the entire row. Just a couple of select columns. Here's the result I got from that code:

I want it to apply to the PO Ship Date and the Expected Ship Date columns (both of which I have as HTML blocks with the code) but for some reason it only highlights the first HTML block red (I've tried with PO Ship in front and with Expected Ship in front it only applies to whichever is first).  Here's the code I have in each HTML block (with Expected Ship instead of PO Ship).

<div id="visi[@field:tbl_ggl_job_lines_Line_Number]">[@field:PO_Ship_Date*]</div>
<script>
var isi = document.getElementById("visi[@field:tbl_ggl_job_lines_Line_Number]");
if('[@field:PO_Ship_Date*]' < '[@cbTimestamp*]'){
var elems = isi.parentNode.parentNode.querySelectorAll("td");

isi.style.color="red"; //font color

}
</script>

But this is the result I get from that code:

[pmcfarlain]

15 hours ago, TellMeWhy said:

Oh, then it's much simpler, just replace 1 to what number of column that is, add another line if you want to set font color for another one, then set the column number

isi.parentNode.parentNode.querySelector("td:nth-child(1)").style.color="red";

 

Perfect, thanks! Another question, is there a way I can add an AND to the conditional? I'd like to check if a specific field is not marked 'C'. Here's what I tried:

if('[@field:dates_Customer_Expected_Date*]' < '[@cbTimestamp*]' && '[@field:Engineering_Traveler_Released]' != 'C')

 

[TellMeWhy]

12 minutes ago, pmcfarlain said:

Perfect, thanks! Another question, is there a way I can add an AND to the conditional? I'd like to check if a specific field is not marked 'C'. Here's what I tried:

if('[@field:dates_Customer_Expected_Date*]' < '[@cbTimestamp*]' && '[@field:Engineering_Traveler_Released]' != 'C')

 

Does that not work? That's how you add AND operator, seems correct.

[pmcfarlain]

22 hours ago, TellMeWhy said:

Does that not work? That's how you add AND operator, seems correct.

EDIT: had a different formatting issue! it works fine.

Nope. When I add that, nothing gets highlighted.

[Kurumi]

This new post might help as well:

 

[Kurumi]

Hi - Just an update, for dynamically changing the font color of field values based on a condition - you can use this solution/CSS: https://howto.caspio.com/tech-tips-and-articles/dynamically-changing-colors-of-values/

[IamNatoyThatLovesYou]

Hello Everyone, just sharing this article as well that may be related to the topic. https://howto.caspio.com/tech-tips-and-articles/color-coding-in-datapages/