Change column based on values from another calculated field

[Aleksandra]

Hello,

I've been testing different solutions offered on the forum for this, but no solution worked properly in my case. So, I want to change the format of the Budget Remaining Field in my report based on the value of the Budget Ratio field.  The field budget ratio is hidden from the tabular report. I applied the following script in the header to do so:

<style>
form[action*='[@cbAppKey]'] tr.cbResultSetDataRow td:nth-child(10),
form[action*='[@cbAppKey]'] tr.cbResultSetTableHeader th:nth-child(10)
{
display:none !important;
}
</style>

Budget Remaining is a calculated field and so is Budget Ratio. Budget Remaining is formatted as a currency field, while Budget Ratio is formatted as a numeric field with 2 decimals. I want to set up the following conditions:

If Budget Ratio <= 0.20 then the background color of Budget Remaining should be red.

If Budget Ratio > 0.20  and <= 0.40 then the background color of Budget Remaining should be orange. In any other cases it should be white.

I've tried to adjust codes from the following threads:

Changing cell color based on value of another cell - DataPages - Caspio Community Forums

How to add colors to particular cells in a Tabular Report list? - User JavaScript and CSS Discussions - Caspio Community Forums

Conditionally Format Values In Column Of Tabular Report Based On Values - User JavaScript and CSS Discussions - Caspio Community Forums

I also tried to solve this by customizing codes from the Caspio How to page:

Dynamically Changing the Background of a Results Page Record - Caspio Online Help

But nothing seems to work.

Has anyone on the forum faced something similar and what would be your advice on solving the issue?

Thank you all in advance,

Aleksandra

 

 

[cheonsa]

Hi @Aleksandra,

I found this forum post, and I think it is similar to your question:

I have changed a few things and it looks like this:

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

    function colorHandler() {

        const BudgetRatio = document.querySelectorAll('td:nth-child(4)'); // 4 is the order number of the Budget Ratio field
     
        BudgetRatio.forEach(element => {

        const currentElement = parseFloat(element.innerHTML);

         if (currentElement <= 0.20) {
                element.previousSibling.style.backgroundColor = 'red'; // field previous to the Budget Ratio field. This should be you Budget Remaining field
            }   
        else if (currentElement > 0.20  && currentElement <= 0.40) {
                element.previousSibling.style.backgroundColor = 'orange'; 
            }      
        });

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

[Aleksandra]

Thank you so much @cheonsa, this script you've left here worked perfectly and I figured out where I made a mistake with the code I was trying to implement.

[IamNatoyThatLovesYou]

  Hello Everyone, just sharing this other solution that may help.