Jump to content

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


Recommended Posts

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

 

 

Capture.PNG

Link to comment
Share on other sites

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:

dwiJfAK.png

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.

Link to comment
Share on other sites

@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:

U0nWU7b.png

 

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:

kF6eOWk.png

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

Edited by CoopperBackpack
updated code for the formatted currency/number field
Link to comment
Share on other sites

  • 4 months later...

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>

 

Link to comment
Share on other sites

  • 3 months later...
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

mark.jpg

Link to comment
Share on other sites

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:

2T0zFU2.png

Link to comment
Share on other sites

  • 2 months later...
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

Link to comment
Share on other sites

  • 1 month later...

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:

image.png

I hope this helps!

Link to comment
Share on other sites

  • 2 weeks later...

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:
 

image.png.88483f5db3617bf4e5c7fe4f5aa5825a.png

 

But this is what I keep getting:

image.png.311bcc6bb17d3ab51b9688202fd03dec.png

 

 

 

 

 

Link to comment
Share on other sites

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:

2NOAL91.png

Link to comment
Share on other sites

  • 10 months later...

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).


n5h3UWp.png

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

<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>
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
Reply to this topic...

×   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...
×
×
  • Create New...