Jump to content
  • 0

Conditionally Change Background and Text Color


pmcfarlain
 Share

Question

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>

 

Link to comment
Share on other sites

18 answers to this question

Recommended Posts

  • 0
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.

Link to comment
Share on other sites

  • 1
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:

1739550721_ScreenShot2021-07-20at1_35_06PM.png.21bfccb2ef1cf8b6ba2ac93d82f893bc.png

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:

475709898_ScreenShot2021-07-20at1_39_36PM.png.067204ac221906cd08c13c1e2135c090.png

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";

 

Link to comment
Share on other sites

  • 0
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):

600008608_ScreenShot2021-07-12at1_05_22PM.png.41e41e75a0f68ea9f86efcee5d81d792.png

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

Link to comment
Share on other sites

  • 0
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):

600008608_ScreenShot2021-07-12at1_05_22PM.png.41e41e75a0f68ea9f86efcee5d81d792.png

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

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0
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

Link to comment
Share on other sites

  • 0

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?

Link to comment
Share on other sites

  • 0
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!

Link to comment
Share on other sites

  • 0
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*]'){

 

Link to comment
Share on other sites

  • 0
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?

1828458130_ScreenShot2021-07-20at1_24_56PM.png.0061a570b59f8f37217b1951a8a05582.png

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

Link to comment
Share on other sites

  • 0
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

Link to comment
Share on other sites

  • 0

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:

1739550721_ScreenShot2021-07-20at1_35_06PM.png.21bfccb2ef1cf8b6ba2ac93d82f893bc.png

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:

475709898_ScreenShot2021-07-20at1_39_36PM.png.067204ac221906cd08c13c1e2135c090.png

Link to comment
Share on other sites

  • 0
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')

 

Link to comment
Share on other sites

  • 0
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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...