Dynamicallychange the background of a results page record but with columns

[Altair]

I was wondering if I could do this but with  specific columns instead. 

Any help would be appreciated. Thanks.

[Nuke354]

You can retain the code in the HowTo article but slight modifications. We use the concept of https://www.w3schools.com/cssref/sel_nth-child.asp to achieve this

Here's the code:
 

<script>
var isi = document.getElementById('visi[@field:ID]').parentNode.parentNode;

if('[@field:Sales#]' > 500){

isi.querySelector('td:nth-child(4)').style.backgroundColor = '#A1DE93';
}

else{
isi.querySelector('td:nth-child(4)').style.backgroundColor = 'red';
    
}

</script>

You could also check this deployed Datapage URL: https://c0dcd456.caspio.com/dp/ad9b7000d75f22b3a5474682b6db

[Nuke354]

You can also put this in your Footer if you are using calculated fields to manipulate the styles:

 

<script>

document.querySelectorAll(`[action*="[@cbAppKey]"] .cbResultSetCalculatedField`).forEach(function(elem) {
 if(elem.innerHTML == "Not Available") elem.style.color = "red";

})

</script>

 

[Roosta]

Hi @Nuke354

Is it possible to do this with calulcated columns/fields in pivot tables doesn't necessarily have to have a  value criteria just want certain columns to have a set background color and they are calculated fields?

For example:

Pass = Dark green

Pass (advisory) = Light green

Improvement = Yellow

Fail (supervision) = Light red

Fail (immediate) = Dark red

 

Kind regards, Roosta

 

[Nuke354]

@Roosta

Yes, this is definitely doable, you just need to determine the child row of a certain column to change the color. Since there are no criteria  you can omit the IF and ELSE statements. Like this:
 

isi.querySelector('td:nth-child(<column_number>)').style.backgroundColor = 'red';

To find the child no. just press F11 to Inspect Element on Google Chrome then copy paste the selector.

 

[Roosta]

Hi @Nuke354

Thank you for the reply its much appreciated but I don't quite understand how I can use that code alone for calculated fields without setting the "isi" variable  as per the code above;

var isi = document.getElementById('visi[@field:ID]').parentNode.parentNode;

I am very unfamiliar with this type of syntax/use so any help would be appreciated, I have tried using just the line of code you suggested in the above and omitting the  getElementById but it doesn't work.

I ran into a similar problem with a different pivot table where I wanted to change the colors of certain cells with a % in them and ended up doing it via the css when the page is deployed using on my website by inseting the following code in the footer;

 

<script>
$("table.cbResultSetTable tbody tr.cbResultSetOddRow, table.cbResultSetTable tbody tr.cbResultSetEvenRow").each(
    function (x) {
        $(this).children().each(
            function(){
                if ($(this).text().indexOf("%") != -1){
                    if ($(this).text() == "100 %"){
                        $(this).css("color", "white").css("background","green")
                    } else {
                        $(this).css("color", "white").css("background","red")
                    }
                }
            }
        )
    }
)
</script>

Which gives this result;

 

However the only problem is using this method is the actual background color is not altered so when using the Capios PDF function the CSS style changes are not printed would a similar soultion be possible using the methods above as well?

The printed output to PDF comes out like this;

Many thanks, Roosta

 

 

[Nuke354]

@Roosta

You can try this code if you want color a column color without any criteria. This method makes the PDF generator recognize the color change. You can check out the  DataPage again here: https://c0dcd456.caspio.com/dp/ad9b7000d75f22b3a5474682b6db
 

<script>
var isi = document.getElementById('visi[@field:ID]').parentNode.parentNode;

if('[@field:Sales#]' > 500){

isi.querySelector('td:nth-child(4)').style.backgroundColor = '#A1DE93';
}

else{
isi.querySelector('td:nth-child(4)').style.backgroundColor = 'red';
    
}
// Force change color
isi.querySelector('td:nth-child(5)').style.backgroundColor = 'blue';

</script>

 

[Roosta]

Hi @Nuke354

Unfortunately this method does not appear to work in pivot tables only tabular reports unless I am doing something wrong, I have tied inserting into the footer as you can't put html blocks in pivot tables to deploy code in this manner;

I did have some limited success using the following in the footer;

 

<script>

document.querySelectorAll(`[action*="[@cbAppKey]"] .cbResultSetCalculatedField`).forEach(function(elem) {
 if(elem.innerHTML == "100 %") elem.style.color = "white", elem.style.backgroundColor = 'green';

else if(elem.innerHTML== "50 %") elem.style.color = "white", elem.style.backgroundColor = 'red';
})

</script>

 

Which gives;

However it utileses innerHTML with spevified values and I cannot figure out how to apply to a single column where I could use just one color for a column or criteria for that column only such as 100 % else etc, etc.

Thanks for responding, Roosta

 

 

[Roosta]

Hi  @Nuke354

I found a partial solution which I modified slightly for the columns being the same color on the pivot table in an old post using this in the header;

<style>
#target table:nth-of-type(1) td:nth-of-type(3) {color: white;}
#target table:nth-of-type(1) td:nth-of-type(3) {background: green;}
#target table:nth-of-type(1) td:nth-of-type(5) {color: white;}
#target table:nth-of-type(1) td:nth-of-type(5) {background: limegreen;}
#target table:nth-of-type(1) td:nth-of-type(7) {color: white;}
#target table:nth-of-type(1) td:nth-of-type(7) {background: gold;}
#target table:nth-of-type(1) td:nth-of-type(9) {color: white;}
#target table:nth-of-type(1) td:nth-of-type(9) {background: red;}
#target table:nth-of-type(1) td:nth-of-type(11) {color: white;}
#target table:nth-of-type(1) td:nth-of-type(11) {background: darkred;}
</style>
<div id="target">

and a closing </div> in the footer which gives me;

 

Still working on somethin to replace the CSS manipulation with a style change if anyone has any ideas?

Cheers, Roosta

 

[Roosta]

hi @Nuke354

Well I thought I's solved it, not pretty but thought I had,,,,

 

<script>

document.querySelectorAll(`[action*="[@cbAppKey]"] td:nth-child(5)`).forEach(function(elem) {
 if(elem.innerHTML == "100 %")  elem.style.color = "white", elem.style.backgroundColor = 'green'
})

document.querySelectorAll(`[action*="[@cbAppKey]"] td:nth-child(5)`).forEach(function(elem) {
 if(elem.innerHTML !== "100 %") elem.style.color = "white", elem.style.backgroundColor = 'red';
})

document.querySelectorAll(`[action*="[@cbAppKey]"] td:nth-child(8)`).forEach(function(elem) {
 if(elem.innerHTML == "100 %") elem.style.color = "white", elem.style.backgroundColor = 'green';
})

document.querySelectorAll(`[action*="[@cbAppKey]"] td:nth-child(8)`).forEach(function(elem) {
 if(elem.innerHTML !== "100 %") elem.style.color = "white", elem.style.backgroundColor = 'red';
})

document.querySelectorAll(`[action*="[@cbAppKey]"] td:nth-child(11)`).forEach(function(elem) {
 if(elem.innerHTML == "100 %") elem.style.color = "white", elem.style.backgroundColor = 'green';
})

document.querySelectorAll(`[action*="[@cbAppKey]"] td:nth-child(11)`).forEach(function(elem) {
 if(elem.innerHTML !== "100 %") elem.style.color = "white", elem.style.backgroundColor = 'red';
})

document.querySelectorAll(`[action*="[@cbAppKey]"] td:nth-child(14)`).forEach(function(elem) {
 if(elem.innerHTML == "100 %") elem.style.color = "white", elem.style.backgroundColor = 'green';
})

document.querySelectorAll(`[action*="[@cbAppKey]"] td:nth-child(14)`).forEach(function(elem) {
 if(elem.innerHTML !== "100 %") elem.style.color = "white", elem.style.backgroundColor = 'red';
})
</script>

Which gave me this in a preview of the pivot table:

Great I thought should print on the PDF now but when printed got this;

I have absolutely no idea why it doesn't print in the PDF but shows in the preview (which when using JS to manipulate it via CSS it doesn't) it would appear that pivot tables just don't behave in the same way as tabular results tables!

Unfortunately as you can't inset HTML blocks in pivot tables the methods used in tabular reports for checnging colors using a unique row ID and a varible can't be used, also in the header/footer you can't select data fields only authentication fields so  if [@field:xxxxxx]  etc isn't an option either hence the hack above!

Oh well back to the drawing board any suggestions gratefully recieved would be much esier if Caspio had conditional formatting as a native option for the data fields...

Cheers, Roosta

 

 

 

 

[Roosta]

Hi all,

Well came up with a solution (off sorts!) for the Pivot table not deisplaying colors in the PDF genrator using the code below;

<script>

document.querySelectorAll(`table.cbResultSetTable tbody tr.cbResultSetOddRow td:nth-child(4), table.cbResultSetTable tbody tr.cbResultSetEvenRow td:nth-child(4)`).forEach(function(elem) {
 if(elem.innerHTML !== "0 %") elem.style.color = "white", elem.style.backgroundColor = 'green';
})

document.querySelectorAll(`table.cbResultSetTable tbody tr.cbResultSetOddRow td:nth-child(6), table.cbResultSetTable tbody tr.cbResultSetEvenRow td:nth-child(6)`).forEach(function(elem) {
 if(elem.innerHTML !== "0 %") elem.style.color = "white", elem.style.backgroundColor = 'limegreen';
})

document.querySelectorAll(`table.cbResultSetTable tbody tr.cbResultSetOddRow td:nth-child(8), table.cbResultSetTable tbody tr.cbResultSetEvenRow td:nth-child(8)`).forEach(function(elem) {
 if(elem.innerHTML !== "0 %") elem.style.color = "white", elem.style.backgroundColor = 'gold';
})

document.querySelectorAll(`table.cbResultSetTable tbody tr.cbResultSetOddRow td:nth-child(10), table.cbResultSetTable tbody tr.cbResultSetEvenRow td:nth-child(10)`).forEach(function(elem) {
 if(elem.innerHTML !== "0 %") elem.style.color = "white", elem.style.backgroundColor = 'red';
})

document.querySelectorAll(`table.cbResultSetTable tbody tr.cbResultSetOddRow td:nth-child(12), table.cbResultSetTable tbody tr.cbResultSetEvenRow td:nth-child(12)`).forEach(function(elem) {
 if(elem.innerHTML !== "0 %") elem.style.color = "white", elem.style.backgroundColor = 'darkred';
})
</script>

Which gives this in the PDF output;

Not terribly efficient I know put I think the problem was using;

document.querySelectorAll(`[action*="[@cbAppKey]"]

Which didn't specifically target the columns/rows that I needed to alter the Style of , hopefully this will be of some use to anyone trying to do something similar in a pivot table!

Cheers, Roosta.