Jump to content

Dynamicallychange the background of a results page record but with columns


Recommended Posts

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

Link to comment
Share on other sites

  • 3 months later...

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>

 

Link to comment
Share on other sites

  • 1 month later...

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:

image.thumb.png.7ecf2a3a66af0fb1030b0cdb6cba6266.png

Pass = Dark green

Pass (advisory) = Light green

Improvement = Yellow

Fail (supervision) = Light red

Fail (immediate) = Dark red

 

Kind regards, Roosta

 

Link to comment
Share on other sites

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

image.thumb.png.46d76116cf3b1362e813253120eb0f3e.png

 

Link to comment
Share on other sites

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;

 

image.thumb.png.a36d46a7da6b05ec8cff9038c64e68e5.png

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;

image.thumb.png.b4b247c63b93b027aac452a3e605709c.png

Many thanks, Roosta

 

 

Link to comment
Share on other sites

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

image.png.0706fe954844dc541fe41529348e4283.png

 

Link to comment
Share on other sites

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;

image.thumb.png.b345a2c9b39606fef04c60ce96a56aa4.png

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>

 

image.thumb.png.67a3d44b917affd11bdc7900b14c085d.png

Which gives;

image.thumb.png.2fe7100afa0fd3eedaa9174e969b9fa4.png

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

 

 

Link to comment
Share on other sites

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;

 

image.thumb.png.ca0d8cd5f0f20edba5954b5d6dfc123f.png

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

Cheers, Roosta

 

Link to comment
Share on other sites

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:

image.thumb.png.73af748c1e827eac1081cccda63f91ab.png

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

image.thumb.png.66e5e43069cfaa85004f189ec6a20614.png

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

 

 

 

 

Link to comment
Share on other sites

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;

image.thumb.png.17ad5edea56cf6560e5d85dfcf2db28c.png

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.

 

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