Color coding by date

[bookish]

Hi! I have this script that is working but I really meant the second condition to be a date ([Field:Enddate] which is a date/time) but I could not get it to work. I used https://howto.caspio.com/tech-tips-and-articles/customize-background-and-font-colors-in-report-datapage/ as reference for the color coding

 

<span id="[@field:Schedules_Enddate*]changeColor3" style="color:white; background:red"></span>

<script>

if("[@field:Schedules_ACTIVE^]" ==  "Yes" && [@field:Schedules_ACTIVITY_ID#] < 62 )

{

document.getElementById("[@field:Schedules_Enddate*]changeColor3").innerHTML='Code C';

}

</script>

I wanted the second condition to be if [Field:Enddate] is in the past or before today. Could you help me with the JS? Thanks!
 

[Ilyrian]

Hi @bookish

There is another approach to achieving your result.
You can try to create a CalculatedField and add the formula there. For example:


Then based on the result on the calculatedField you can change the colors using the javascript:
 

<script>
document.addEventListener('DataPageReady', colorHandler);

function colorHandler() {

  let lines = document.querySelectorAll("td:nth-child(5)");  // 5 is the colummn order number

  lines.forEach(line => {
    if(line.innerText.startsWith('YES')){
    line.parentElement.style.backgroundColor = '#d9c1c9';  // #d9c1c9 is the color code
    }
  })

document.removeEventListener('DataPageReady', colorHandler);
}
</script>

Afterward, if you do not wish to show the calculatedfield, you could use the approach in this article to hide the column.

Hope this helps

[bookish]

I couldn't get past the Verify Formula. 

[Ilyrian]

12 minutes ago, bookish said:

I couldn't get past the Verify Formula. 

Please share the formula, and tell us what are you trying.

[bookish]

5 minutes ago, Ilyrian said:

Please share the formula, and tell us what are you trying.

I copied exactly your calculated field except for the date field where I picked my own, but the formula is invalid. Are you missing a quote after WHEN? But even if I put [@field:Schedules_ACTIVE^] between quotes, it is still invalid formula - incorrect syntax

CASE

WHEN [@field:Schedules_ACTIVE^]" ==  "Yes"  AND [@field:Schedules_Enddate] < SysDateTime() THEN 'YES'
ELSE 'NO'

END

[Ilyrian]

Hi @bookish

If the "Schedules_ACTIVE" field is Yes/No Field then use this:

CASE

WHEN [@field:Schedules_ACTIVE] ==  1  AND [@field:Schedules_Enddate] < SysDateTime() THEN 'YES'
ELSE 'NO'

END

1-stands for YES (or selected)
0-stands for No (not selected)

[bookish]

10 minutes ago, Ilyrian said:

Hi @bookish

If the "Schedules_ACTIVE" field is Yes/No Field then use this:

CASE

WHEN [@field:Schedules_ACTIVE] ==  1  AND [@field:Schedules_Enddate] < SysDateTime() THEN 'YES'
ELSE 'NO'

END

1-stands for YES (or selected)
0-stands for No (not selected)

It is still invalid, incorrect syntax. Thanks so much for the effort.

[Ilyrian]

10 minutes ago, bookish said:

It is still invalid, incorrect syntax. Thanks so much for the effort.

That is strange. Here is a screenshot from my end:


 

CASE 

WHEN  [@field:YesNo1] = 0 AND [@field:Date1] < SysDateTime() THEN 'It is not selected' 
ELSE 'Selected'

END

 

[CoopperBackpack]

Hi @bookish, 

According to your reply, it looks like there is an incorrect comparison in the formula:

WHEN [@field:Schedules_ACTIVE] ==  1 

This double-equal sign is used in JavaScript for compassion. But this is the SQl formula so just an equal sign must be used. 

Perhaps, this is the cause of the issue. 

[bookish]

10 hours ago, CoopperBackpack said:

Hi @bookish, 

According to your reply, it looks like there is an incorrect comparison in the formula:

WHEN [@field:Schedules_ACTIVE] ==  1 

This double-equal sign is used in JavaScript for compassion. But this is the SQl formula so just an equal sign must be used. 

Perhaps, this is the cause of the issue. 

Thank you so much! That was simple and brilliant, esp for a non-programmer like me  But does it mean that this script (the second condition in particular) won't work in this case? I truly appreciate the solution by @Ilyrian but I am still hoping there is a simpler solution. Could this script be tweaked? Once or twice it worked but it didn't after, when I previewed again. There is something that is not reliable in this script.
 

<span id="[@field:Schedules_Enddate*]changeColor3" style="color:white; background:red"></span>
<script>

if("[@field:Schedules_ACTIVE^]" ==  "Yes" && [@field:Schedules_Enddate] < SysDateTime() )
{
document.getElementById("[@field:Schedules_Enddate*]changeColor3").innerHTML='Code C';
}
</script>

[bookish]

12 hours ago, Ilyrian said:

Hi @bookish

There is another approach to achieving your result.
You can try to create a CalculatedField and add the formula there. For example:


Then based on the result on the calculatedField you can change the colors using the javascript:
 

<script>
document.addEventListener('DataPageReady', colorHandler);

function colorHandler() {

  let lines = document.querySelectorAll("td:nth-child(5)");  // 5 is the colummn order number

  lines.forEach(line => {
    if(line.innerText.startsWith('YES')){
    line.parentElement.style.backgroundColor = '#d9c1c9';  // #d9c1c9 is the color code
    }
  })

document.removeEventListener('DataPageReady', colorHandler);
}
</script>

Afterward, if you do not wish to show the calculatedfield, you could use the approach in this article to hide the column.

Hope this helps

After the change in the calculated field and the proposed change of @CoopperBackpack on the single =, everything you said here worked including hiding the column. Thanks so much! I am just hoping for the tweak of my script if possible .

[bookish]

So this is what I did now, and it's working

1. Calculated field: not hidden at all

CASE WHEN  [@field:Schedules_ACTIVE] = 1 AND [@field:Schedules_Enddate] < SysDateTime() THEN 'Code C' ELSE NULL END

2. Footer to change record background
 

<script>
document.addEventListener('DataPageReady', colorHandler);

function colorHandler() {

  let lines = document.querySelectorAll("td:nth-child(5)");  // 5 is the column order number

  lines.forEach(line => {
    if(line.innerText.startsWith('Code C')){
    line.parentElement.style.backgroundColor = '#FF5C45';  
    }
  })

document.removeEventListener('DataPageReady', colorHandler);
}
</script>

Happy for now  Thanks @Ilyrian and @CoopperBackpack