How to add colors to particular cells in a Tabular Report list?

[MarkMayhrai]

Hello,

I have a tabular report list shown in picture below, and I would like to visually show our users colors depending on the statuses (text) in the Status Column. 

Notably, I wish to have like a red background with with bold text for "In Transit" and Green BK/ White TXT and Bold for "Delivery" for example.

Hopefully you guys to get me started so I could customize and add for the other various statuses

 

 

[CoopperBackpack]

Hello @MarkMayhrai,

For such cases I use the following code in the DataPage Footer:

<script >
    document.addEventListener('DataPageReady', function(event) {
        const statusField= document.querySelectorAll('td:nth-child(5)'); // '5' is field number for which styles have to be applied

        statusField.forEach(element => {
            if (element.innerHTML === 'In Transit') {
                element.style.cssText = 'background: #ff5a4b; font-weight: bold';
            } else if (element.innerHTML === 'Delivered') {
                element.style.cssText =  'background: #376f4d; font-weight: bold; color: #ffffff';
           }
        });

    });
</script>

Here is the result:

1) So, the idea is to select the required field, in my example its order number is 5.

2) Then depending on the value we can apply inline styles. Since you want to apply multiply styles, like background color, font-color, etc., we can use the cssText property.

Hope this helps.

[MarkMayhrai]

 Worked! 

 

Okay so just one more, how to apply the condition to number values?

[CoopperBackpack]

@MarkMayhrai,

It is easy to apply this approach to text values or numeric values.

But in your example it is a formatted number, and since the value has the $ sign it is a string. 

So, everything depends on the condition you want to apply. If this is just  'equal to',  then you may use the same code:

<script >
    document.addEventListener('DataPageReady', function(event) {

        const currencyFormatted = document.querySelectorAll('td:nth-child(2)');

        currencyFormatted.forEach(element => {
         if (element.innerHTML === '$0.00') {
                element.style.cssText = 'background: #52cc96; font-weight: bold';
            } 
         else if (element.innerHTML === '$20.00') {
                element.style.cssText = 'background: #008ee4; font-weight: bold';
            } 
        });

    });
</script>

The output:

 

However, if you need to apply 'greater than', 'less than', etc., then we need to remove the currency sign, also need to remove commas if they are used to group numbers and convert a string to a number.
The solution may require modifications if the applied formatting is a different one.

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

function colorCellsHandler(){

    const currencyFieldFormatted = document.querySelectorAll('td:nth-child(3)'); //3 is the position number of the field 

    currencyFieldFormatted.forEach(element => {
                   
        const currentElement = parseFloat((element.innerText).replace('$', '').replace(',','')); //remove the dollar sign and commas and convert the value to the floating point number

        if (currentElement < 1500) {                                           
            element.style.backgroundColor = '#80dac9';
        } 
        else if (currentElement >= 1500) {      
            element.style.backgroundColor = '#d8aef4';
        } 
    });
document.removeEventListener('DataPageReady', colorCellsHandler)
}
</script>

The output:

I will test other approaches and add them here if I have any.

Edited January 31 by CoopperBackpack
updated code for the formatted currency/number field

[MarkMayhrai]

You're the bomb!!!! 

Thanks, save me life and time!

[IamNatoyThatLovesYou]

Hello everyone! I also checked that Caspio has documentation related to this. https://howto.caspio.com/tech-tips-and-articles/advanced-customizations/how-to-dynamically-change-the-background-of-a-results-page/

[kpcollier]

Anyone know how to do this with Grid Edit enabled? I was able to conditionally style my cells on the normal view. But, once I go into Grid Edit view, the styling goes away. I can't seem to figure out how to get it there.

Mine is a bit different, as I am using a hidden Calculated Field as a condition. This is all inside of an HTML field, replacing Calc 5's actual field. Calc Field 6 is getting a percentage from 2 numbers on the report. If it is 10% or under, the HTML block should be highlighted orange. Also, if calc5 is a negative number, also highlight it orange. Otherwise keep it white. 

Any ideas?

[@calcfield:5#]
<a id="visi[@field:Job_Table_Job_Number]">

<script>
var isi=document.getElementById("visi[@field:Job_Table_Job_Number]");

if([@calcfield:6] <= 10) {
  isi.parentNode.style.backgroundColor="orange";

} else if([@calcfield:5] < 0){
  isi.parentNode.style.backgroundColor="orange";

} else {
  isi.parentNode.style.backgroundColor="#fff"; 
}
</script>

 

[sanza]

On 11/25/2021 at 4:41 PM, MarkMayhrai said:

You're the bomb!!!! 

Thanks, save me life and time!

Hi MarkNinja 

I followed this thread and realised that I need to have some features of the same on my tabular report. Basically, I need to color code the following variables  on my columns ( Neutral to Orange; Positive to Green and Negative to Red. How do I go about that?  Any code that I could use? I have attached a screenshot that could assist. Looking forward to your responses.. Thamls

[CoopperBackpack]

Hello @sanza,

As I can see on the screenshot, this is a Tabular Report. 

Please add this code to the DataPage Footer in the "Configure Results Page Fields" page. Disable the HTML editor on the Advanced tab before pasting the code.  

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

    function colorHandler() {

        const statusField = document.querySelectorAll('td:nth-child(2)'); // 2 is the order number of the requred field, change this value if needed

        statusField.forEach(element => {
         if (element.innerHTML === 'Neutral') {
                element.style.backgroundColor = '#e4a889';
            } 
         else if (element.innerHTML === 'Positive') {
                element.style.backgroundColor = '#a2dfa7';
            } 
         else if (element.innerHTML === 'Negative') {
                element.style.backgroundColor = '#f2867c';
            } 
        });

  document.removeEventListener('DataPageReady', colorHandler);

};
</script>

 

Please change the order number of your field in the "statusField " variable if needed. In my example this is the second field. Also, please change the color codes if needed.

The output:

[lamarh]

This works great if there is data in the cell. I need to also highlight cells that are empty, but when I change it to 

(element.innerHTML === '')

The cell color doesn't change. Any help would be appreciated!

[futurist]

17 hours ago, lamarh said:

This works great if there is data in the cell. I need to also highlight cells that are empty, but when I change it to 

(element.innerHTML === '')

The cell color doesn't change. Any help would be appreciated!

Hi @lamarh,

 

Instead of three equal signs (===), have you tried using either just one or two? You may read more about the difference between =, ==, and === here: https://www.guru99.com/difference-equality-strict-operator-javascript.html

[Kurumi]

Hi! Just an update - if you would like to highlight search words in the Results Page, you can try these solutions:

1. From this link: https://www.delftstack.com/howto/javascript/highlight-text-using-javascript/

To apply in the DataPage, insert the code below in the Footer of Configure Results Page Fields

<script>

document.addEventListener('DataPageReady', function (event) {

let searched = document.querySelector("input[name='Value1_1']").value.trim();
    if (searched !== "") {
        let text = document.querySelector("table[id*='cbTable']").innerHTML;
        let re = new RegExp(searched,"g"); // search for all instances
        let newText = text.replace(re, `<mark>${searched}</mark>`);
    document.querySelector("table[id*='cbTable']").innerHTML = newText;
    }

});

</script>

You can change the Value1_1 based on the form element of your search field.

2. Using mark.js - https://markjs.io/ This is text highlighter written in JavaScript.

To apply in the DataPage, insert the code below in the Footer of Configure Results Page Fields

<script src="https://cdnjs.cloudflare.com/ajax/libs/mark.js/8.11.1/mark.min.js"></script>

<script>

document.addEventListener('DataPageReady', function (event) {

let searched = document.querySelector("input[name='Value1_1']").value;

    if (searched !== "") {
       var instance = new Mark(document.querySelector("table[id*='cbTable']").tBodies[0]);
       instance.mark(searched);
    }

});
</script>

Result:

I hope this helps!

[NJVideoGuy]

Is there a way to highlight the contents of one (or more) fields based on the value of another field?

Using modifications to the solutions provided above, I am able to change the background of a field if the contents in that field equal a specific value, but how can I do this based on another field's value?

I've tried modifying it like this but it doesn't work, it changes the background on every cell in columns 2 and 3, not just the ones that have the 'DealerList' variable set to 'ABC':

 

if('[@field:DealerList]' == 'ABC') 

{
 document.addEventListener('DataPageReady', function(event) 
{
         const statusField= document.querySelectorAll('td:nth-child(2),td:nth-child(3)'); // '2, 3' are field numbers for which styles have to be applied

statusField.forEach(element => 
{
   {element.style.cssText = 'background: #ff4600; font-weight: bold';}
 });

 });

}

 

This is what I would like it to look like:
 

 

But this is what I keep getting:

 

 

 

 

 

[CoopperBackpack]

Hello @NJVideoGuy,

Please test this script in the Footer section:

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

    function colorHandler() {

        const dealerField = document.querySelectorAll('td:nth-child(4)'); // 4 is the order number of the DealerList field, change this value if needed
     
        dealerField.forEach(element => {
         if (element.innerHTML === 'ABC') {
                element.previousSibling.style.backgroundColor = '#e4a889'; // field previous to the DealerList field (the 3rd in this example)
                element.previousSibling.previousSibling.style.backgroundColor = '#e4a889'; // field previous previous to the DealerList field (the 2nd in this example)
            }       
        });

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

 

Output:

[CoopperBackpack]

I wanted to share a version of the code to apply the styles to the cells based on the date.
For example, highlight the cells where the date is in the past (so, the date is less than today).

Add this script to the Footer section of the Results page. Disable the HTML editor on the Advanced tab before pasting the code.

!IMPORTANT NOTE: Localization matters. This code works for the US date format.

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

function colorHandler() {

  const dateField= document.querySelectorAll('td:nth-child(2)'); // 2 is the field position on the results page
    
  const date = new Date();
  
  const todayDay = `${date.getDate()}`.padStart(2, 0);
  const todayMonth = `${date.getMonth() + 1}`.padStart(2, 0);
  const todayYear = date.getFullYear();
  
  const today = Date.parse(`${todayMonth}/${todayDay}/${todayYear}`);
  
  dateField.forEach(element => {
    if (Date.parse(element.innerText) < today) {
      element.style.backgroundColor = '#afb0ca';
      } 
  });

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