Color column in report based on calculated field not working as expected

[techguy]

hello,

I have a calc field in a tab report and I am colorizing the expiration date based on a formula. The formula I use is:

Datediff(day,[@field:ExpireDate],Dateadd(hour,-8,GetUTCDate()))

The field has the correct number of days from or past the expiration date. The report shows the days properly. I am using the script below to change the color based on the number of days before and after the expiration date. Everything works EXCEPT the calculated field with days  -1000 or more (-1200, -1673, etc) will always turn RED. Even though that indicates the date is not for a few years away. the magic number is -999 for it to work and when the date is farther out it always turns RED.

Any suggestions???

</div>
<script>
var isi = document.getElementById('visi[@field:TrainingID]').parentNode.parentNode;
if([@calcfield:1#] < -59){
isi.querySelector('td:nth-child(8)').style.backgroundColor = 'green';
isi.querySelector('td:nth-child(8)').style.color = 'white';
}
else if([@calcfield:1#] > -60 && [@calcfield:1#] < -1){
isi.querySelector('td:nth-child(8)').style.backgroundColor = 'yellow';
isi.querySelector('td:nth-child(8)').style.color = 'black';
}
else{
isi.querySelector('td:nth-child(8)').style.backgroundColor = 'red';
isi.querySelector('td:nth-child(8)').style.color = 'white';
}
</script>

[CoopperBackpack]

Hello @techguy,

It doesn`t work as expected due to the parameter format. 

You add a calc field to the script as a number: [@calcfield:1#] 

You may want to check this article https://howto.caspio.com/parameters/displaying-parameters/
I mean # sign:

As a result, the value from the calc field is formatted according to the format set under Localization applied on the DataPage.
To check, you may add an HTML block and insert [@calcfield:1#] there.
For example:

It looks like the value on your DataPage is formatted as - 1,000 or -1.000. The main point is that a comma or dot is added if the value is greater than 999. 

So, the comparison is not correct in the script when it compares -1.000 or more. 

To fix this, please remove the # sign after the calc field. 
 

<script>
var isi = document.getElementById('visi[@field:TrainingID]').parentNode.parentNode;
if([@calcfield:1] < -59){
isi.querySelector('td:nth-child(8)').style.backgroundColor = 'green';
isi.querySelector('td:nth-child(8)').style.color = 'white';
}
else if([@calcfield:1] > -60 && [@calcfield:1] < -1){
isi.querySelector('td:nth-child(8)').style.backgroundColor = 'yellow';
isi.querySelector('td:nth-child(8)').style.color = 'black';
}
else{
isi.querySelector('td:nth-child(8)').style.backgroundColor = 'red';
isi.querySelector('td:nth-child(8)').style.color = 'white';
}
</script>

[techguy]

Thank you! It works perfectly now.