change the cell color of a tabular report with chart

[khale]

Hi!

I am trying to get the cell of a calculated field to turn colors based on the value.  I found this code but I am not sure how to make it work:

 

<style>
input[name="InsertRecordFIELDNAME"][value="Active"] {
  color: green;
}
</style>

Basically, I want the value of a calculated filed to change colors based on the value.  Specifically, <1 = Red, 1-31 Yellow, 32-62 Orange, and if the value in another cell is not blank, change the color to grey.  Its a tabular report with a chart and html is disabled.

 

Any help or direction will be appreciated!

 

[CoopperBackpack]

Hello @khale,

The basic approach is to add the following script in the Footer section.

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

function colorHandler() {

  const calcField= document.querySelectorAll('td:nth-child(3)'); // 3 is the field position on the results page
    
    calcField.forEach(element => {
         if (parseFloat(element.innerText) <=1) {
                element.style.backgroundColor = '#ff5a4b';
            } 
         else if (parseFloat(element.innerText) > 1 && parseFloat(element.innerText)<=31) {
                element.style.backgroundColor = '#f2f18b';
            } 
         else if (parseFloat(element.innerText) > 31 && parseFloat(element.innerText)<=62 ) {
                element.style.backgroundColor = '#ff9966';
            } 
        });

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

The result:

In my example the Calculated field is the 3rd field on the results page, so I reference it accordingly in the script.

1) Do you need to apply the font color or the background color?

2) Have you applied formatting for the values in the Calculated field? For example, there is the currency sign, etc.?

3) Please clarify this part of the condition "If the value in another cell is not blank, change the color to grey". Do you mean that, for example,  the value in the Calc field = 5 but the decision to color the field is based on another field? If that field is blank the script colors the Calc value in yellow, but if it is not blank - in gray. 

[khale]

Good Morning!

Thank you so much for responding!  To answer your questions, 

In my example the Calculated field is the 3rd field on the results page, so I reference it accordingly in the script.  Its 5th in mine so I will update accordingly.

1) Do you need to apply the font color or the background color?  Background color only

2) Have you applied formatting for the values in the Calculated field? For example, there is the currency sign, etc.?  No formatting as its just numbers without decimals.

3) Please clarify this part of the condition "If the value in another cell is not blank, change the color to grey". Do you mean that, for example, the value in the Calc field = 5 but the decision to color the field is based on another field? If that field is blank the script colors the Calc value in yellow, but if it is not blank - in gray.    So basically, the calculated field calculates how many days past a due date or until how many more days until its due.  There is also a field that's not calculated that is if it was already done date.  If that date is populated, then the calculated field should turn gray, indicating that it has been done.

 

Thanks so much!

 

 

[khale]

And the code worked beautifully!  Just need the fields to turn grey if there is a date in the done field!

[CoopperBackpack]

Hello @khale,

Thank you for sharing the details. 

So, there is one more field, in my example its name is "Done".

In this example, the 1st and 4th values in the DateDiff field should be gray because the 'Done' field is not blank.

 

If this is correct, please let me know the done date field position on the results page. In my example, it is the 6th field.

[khale]

Yes   The Done column is in position 8

[khale]

Hi Coopper Backpack!

Thanks again for your help on this.  I am trying to get the "gray" portion of this code to work but I am unsuccessful.  In the current state of the code, its not changing to gray if the field is not blank. 

Thanks again for your help! 

[CoopperBackpack]

Hello @khale,

If the screenshot showcases the expected result, please test the code I attached below.

 

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

function colorHandler() {

  const calcFieldCells = document.querySelectorAll('form[action*="[@cbAppKey]"] tr.cbResultSetDataRow td:nth-child(5)');
  const doneFieldCells = document.querySelectorAll('form[action*="[@cbAppKey]"] tr.cbResultSetDataRow td:nth-child(8)');

    calcFieldCells.forEach((cell, index) => {

         if(doneFieldCells[index].innerText.trim() !='') {
             cell.style.backgroundColor = '#bfbfbf';
           }
         else  if (parseInt(cell.innerText) <= 1) {
             cell.style.backgroundColor = '#ff5a4b';
            } 
         else if (parseInt(cell.innerText) > 1 && parseFloat(cell.innerText) <= 31) {
             cell.style.backgroundColor = '#f2f18b';
            } 
         else if (parseInt(cell.innerText) > 31 && parseFloat(cell.innerText) <= 62) {
             cell.style.backgroundColor = '#ff9966';
            } 
    });

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

 

[khale]

Yes!  This worked beautifully!  I really appreciate your help!  I have several columns that this code needs to apply to.  I just copy it over and change the referencing fields, correct?   Again, thank you so much!

 

[khale]

Like this for example..

 

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

function colorHandler() {

  const calcFieldCells = document.querySelectorAll('form[action*="[@cbAppKey]"] tr.cbResultSetDataRow td:nth-child(7)');
  const doneFieldCells = document.querySelectorAll('form[action*="[@cbAppKey]"] tr.cbResultSetDataRow td:nth-child(6)');

  const calcFieldCells = document.querySelectorAll('form[action*="[@cbAppKey]"] tr.cbResultSetDataRow td:nth-child(10)');
  const doneFieldCells = document.querySelectorAll('form[action*="[@cbAppKey]"] tr.cbResultSetDataRow td:nth-child(9)');

    calcFieldCells.forEach((cell, index) => {

         if(doneFieldCells[index].innerText.trim() !='') {
             cell.style.backgroundColor = '#bfbfbf';
           }
         else  if (parseInt(cell.innerText) <= 1) {
             cell.style.backgroundColor = '#ff5a4b';
            } 
         else if (parseInt(cell.innerText) > 1 && parseFloat(cell.innerText) <= 31) {
             cell.style.backgroundColor = '#f2f18b';
            } 
         else if (parseInt(cell.innerText) > 31 && parseFloat(cell.innerText) <= 62) {
             cell.style.backgroundColor = '#ff9966';
            } 
    });

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

[khale]

I figured it out.  Just added the code to multiple HTML blocks and all works fine!  Not sure if that's the right way or not (please tell me if not) but its working!

 

Thanks again!  You're great!

[CoopperBackpack]

Hello @khale, you are welcome

It is better to add the code to the Footer. So, the initial code works for fields 5 and 8.

Let`s say you need to apply the same logic for fields 2 and 3, then you can add another piece of code that references to those fields:

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

function colorHandler() {

  const calcFieldCells = document.querySelectorAll('form[action*="[@cbAppKey]"] tr.cbResultSetDataRow td:nth-child(5)');
  const doneFieldCells = document.querySelectorAll('form[action*="[@cbAppKey]"] tr.cbResultSetDataRow td:nth-child(8)');

    calcFieldCells.forEach((cell, index) => {

         if(doneFieldCells[index].innerText.trim() !='') {
             cell.style.backgroundColor = '#bfbfbf';
           }
         else  if (parseInt(cell.innerText) <= 1) {
             cell.style.backgroundColor = '#ff5a4b';
            } 
         else if (parseInt(cell.innerText) > 1 && parseFloat(cell.innerText) <= 31) {
             cell.style.backgroundColor = '#f2f18b';
            } 
         else if (parseInt(cell.innerText) > 31 && parseFloat(cell.innerText) <= 62) {
             cell.style.backgroundColor = '#ff9966';
            } 
    });

 const secondFieldCells = document.querySelectorAll('form[action*="[@cbAppKey]"] tr.cbResultSetDataRow td:nth-child(2)');
 const thirdFieldCells = document.querySelectorAll('form[action*="[@cbAppKey]"] tr.cbResultSetDataRow td:nth-child(3)');

 secondFieldCells.forEach((cell, index) => {

    if(thirdFieldCells[index].innerText.trim() !='') {
        cell.style.backgroundColor = '#bfbfbf';
      }
    else  if (parseInt(cell.innerText) <= 1) {
        cell.style.backgroundColor = '#ff5a4b';
       } 
    else if (parseInt(cell.innerText) > 1 && parseFloat(cell.innerText) <= 31) {
        cell.style.backgroundColor = '#f2f18b';
       } 
    else if (parseInt(cell.innerText) > 31 && parseFloat(cell.innerText) <= 62) {
        cell.style.backgroundColor = '#ff9966';
       } 
});


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

If you have more fields, the code can be optimized.