Change cell color based on future dates

[khale]

Hi!

I found this code that I am trying to get to work for my case.  I have several fields that need to change color so the array is great.  However, I need it to change red if the date is overdue, yellow if its due this month and green if its due next month.  

 

Any ideas on how to accomplish this?

 

TIA!
 

 

 

<script>
if (document.DataPageReadyHandler == undefined) {

const columnsArr = [6,7,8,9,10,11,12,13]

const paintCell = (cell, color) => {
cell.style = color
}

const colorTableCells = () => {
document.querySelectorAll('.cbResultSetDataRow').forEach(row=>{
for (let i=0;i<columnsArr.length;i++) {
let cell = row.querySelector(`td:nth-child(${columnsArr[i]})`)
if (cell==null) return
switch (cell.innerHTML.trim()) {
  case '1': {paintCell(cell, 'background: #df4d55;color: #ffffff')
  break
  }
  case '2': {paintCell(cell, 'background: #fcc200;color: #ffffff')
  break
  }  
  case '3': {paintCell(cell, 'background: #96ded1;color: #000000')
  break
  }  
  case '4': {paintCell(cell, 'background: #96ded1;color: #000000')
  break
  }
    case 'NA': {paintCell(cell, 'background: #e6e6e6;color: #000000')
  break
  }
}

}

})
}

const DataPageReadyHandler = (e) => {
 if (e.detail.appKey != '[@cbAppKey]') { return }
colorTableCells ()
}

document.addEventListener('DataPageReady', DataPageReadyHandler)
document.DataPageReadyHandler = 'Enabled'
}

</script>

[cheonsa]

On 4/4/2024 at 9:16 AM, khale said:

Hi!

I found this code that I am trying to get to work for my case.  I have several fields that need to change color so the array is great.  However, I need it to change red if the date is overdue, yellow if its due this month and green if its due next month.  

 

Any ideas on how to accomplish this?

 

TIA!
 

 

 

<script>
if (document.DataPageReadyHandler == undefined) {

const columnsArr = [6,7,8,9,10,11,12,13]

const paintCell = (cell, color) => {
cell.style = color
}

const colorTableCells = () => {
document.querySelectorAll('.cbResultSetDataRow').forEach(row=>{
for (let i=0;i<columnsArr.length;i++) {
let cell = row.querySelector(`td:nth-child(${columnsArr[i]})`)
if (cell==null) return
switch (cell.innerHTML.trim()) {
  case '1': {paintCell(cell, 'background: #df4d55;color: #ffffff')
  break
  }
  case '2': {paintCell(cell, 'background: #fcc200;color: #ffffff')
  break
  }  
  case '3': {paintCell(cell, 'background: #96ded1;color: #000000')
  break
  }  
  case '4': {paintCell(cell, 'background: #96ded1;color: #000000')
  break
  }
    case 'NA': {paintCell(cell, 'background: #e6e6e6;color: #000000')
  break
  }
}

}

})
}

const DataPageReadyHandler = (e) => {
 if (e.detail.appKey != '[@cbAppKey]') { return }
colorTableCells ()
}

document.addEventListener('DataPageReady', DataPageReadyHandler)
document.DataPageReadyHandler = 'Enabled'
}

</script>

Hi @khale,

This forum post might help you with your desired result:

 

[cheonsa]

Another approach is to use a Calculated Field to determine if the record is already overdue, due this month, or due next month. Then the use the result of this Calculated field in the condition on the script.

CASE WHEN [@field:Date] < Getdate() THEN 'overdue'
WHEN (DatePart(month,[@field:Date]) > DatePart(month, Getdate())) AND (DatePart(year,[@field:Date]) = DatePart(year, Getdate())) THEN 'next month'
WHEN (DatePart(month,[@field:Date]) = DatePart(month, Getdate())) AND (DatePart(year,[@field:Date]) = DatePart(year, Getdate())) THEN 'this month'
END

Then, you can apply the script provided here:

https://howto.caspio.com/tech-tips-and-articles/how-to-dynamically-change-the-background-of-a-results-page/