Change background color after expired

[thompson]

Hello,

I have a Tabular Reports page that show different permits that have an expiration date. I want to make the row turn red if the expiration date has passed. I have tried to use the following but it does not work. Any help would be appreciated. 

<a id="visi[@field:TenantID]">

<script>
var isi = document.getElementById("visi[@field:TenantID]");
if('[@field:Expiration])'>'GetUTCDate()'){
isi.parentNode.parentNode.style.backgroundColor = 'red';
}
else{
isi.parentNode.parentNode.style.backgroundColor = '#NoColor';
}
</script></a>

The [@field:Expiration] is a formula field in the table.

[MayMusic]

Try this:

 <a id="visi[@field:TenantID]">

<script>
var isi = document.getElementById("visi[@field:TenantID]");
 var now = new Date();
var expiration = new Date("[@field:Date*]"); 
if(expiration > now){
isi.parentNode.parentNode.style.backgroundColor = 'red';
}
else{
isi.parentNode.parentNode.style.backgroundColor = '#NoColor';
}
</script></a> 

 

[malalibertecom]

Hi

I works great for me. Thanks. 

Could you help me with the same script but for multiple fileds with dates to check validity?

And how to hightlight the [@field:Vessel_Listing_Vessel] and the expired date ONLY instead of the entire row.

I have more than 15 date fileds fon each rows.

Thanks

Marc

HERE is my scropt:

<a id="visi[@field:Vessel_Listing_Vessel]"> <script>
var isi = document.getElementById("visi[@field:Vessel_Listing_Vessel]");
 var now = new Date();
var expiration = new Date("[@field:vessel_certificats_doc_exp*]"); 
if(expiration < now){
isi.parentNode.parentNode.style.backgroundColor = 'red';
}
else{
isi.parentNode.parentNode.style.backgroundColor = '#NoColor';
}
</script></a> 

[MayMusic]

Then in stead of including the actual field you can add HTML Block and show the value conditionally. For instance:

<div id="visi[@field:Vessel_Listing_Vessel]"> </div>  
  
  <script>
var isi = document.getElementById("visi[@field:Vessel_Listing_Vessel]");
 var now = new Date();
var expiration = new Date("[@field:vessel_certificats_doc_exp*]"); 
if(expiration < now){
isi.innerHTML = '[@field:vessel_certificats_doc_exp*]';
isi.style.backgroundColor='red';
}
else{
isi.innerHTML = '[@field:vessel_certificats_doc_exp*]';
}
</script>

 

[malalibertecom]

Hi MayMusic,

 

It works, thanks for your help. See attachment.

The result of the expired date created a cumulus at the end of the  report.
Can you help make the following changes:
- Making the the expired date field red instead of the current result.
- As you can see, this report is all about expired documents. What would be the script to check more than one field like  [@field:vessel_certificats_iapp_exp*]  (Once I got the idea, Ill make the rest of the fields)
- As you can see on the report page, the red field date format has changed to ENG US instead of ENG UK. Can this be avoided?

Thanks for your help.

Marc

 

 

 

[MayMusic]

since you are reading the value from table and in table we have US format you need to reformat the date to UK:

 

<div id="visi[@field:Vessel_Listing_Vessel]"> </div>  
  
  <script>
var isi = document.getElementById("visi[@field:Vessel_Listing_Vessel]");
var now = new Date();
var expiration = new Date("[@field:vessel_certificats_doc_exp*]"); 
    
var dArr = '[@field:vessel_certificats_doc_exp*]'.split("/");  
 var expirationdate = dArr[1]+ "/" +dArr[0]+ "/" +dArr[2];
    
if(expiration < now){
isi.innerHTML = expirationdate;
isi.style.backgroundColor='red';
}
else{
isi.innerHTML = expirationdate;
}
</script>

 

Can you elaborate on your first two questions with an example? I do not understand what exactly you are asking for

[malalibertecom]

Hi MayMusic,

See the attached resulting from the above script.

As you can see on the screen shot, the expired date of the DOC (first column) certificate not red but the script copied the date on the right with red background. Since I will have this page filled with dates to check for expiry, can you help suggest a script that would turn the DOC cell red instead of transposing the date on the right? Mmmm, not sure this is clear either...

Also, can you propose a script that would also check the date for [@field:vessel_certificats_iapp_exp*]

Thanks for your help.

Marc

 

[MayMusic]

Instead including the actual date field DOC you need to add HTML block and use that script. You can also give it a label. The only difference is that this column will no be editable.

 

To apply the same code for other field you need to add another HTML Block:

<div id="visi_2_[@field:Vessel_Listing_Vessel]"> </div>  
  
  <script>
var isi = document.getElementById("visi_2_[@field:Vessel_Listing_Vessel]");
var now = new Date();
var expiration = new Date("[@field:vessel_certificats_iapp_exp*] "); 
    
var dArr = '[@field:vessel_certificats_iapp_exp*]'.split("/");  
var expirationdate = dArr[1]+ "/" +dArr[0]+ "/" +dArr[2];
    
if(expiration < now){
isin.innerHTML = expirationdate;
isi.style.backgroundColor='red';
}
else{
isi.innerHTML = expirationdate;
}
</script>

 

[malalibertecom]

Good day Maymusic,

Here I am again.

-----------------------------------------

<div id="visi[@field:Vessel_Listing_Vessel]"></div>
<script>
var isi = document.getElementById("visi[@field:Vessel_Listing_Vessel]");
 var now = new Date();
var expiration = new Date("[@field:vessel_certificats_doc_exp]");
if(expiration < now){
isi.innerHTML = 'RENEW';
isi.style.backgroundColor='red';
}
else{
isi.innerHTML = 'OK';
isi.style.backgroundColor='green';
}
</script>

------------------------------------------------------

Would you help find a way to make the flowing changes to this script:

- Yellow for dates within the next 30 days, with ASK
- No color (blank) for empty fields
 

I am also running into a issue where the script CRASHES when I use it for a second HTML block/new date field.
So I need to make this script check over 15 fields.

 

Thanks for your help.

 

[MayMusic]

 

Try this:

<div id="visi[@field:Vessel_Listing_Vessel]"></div>
<script>
var isi = document.getElementById("visi[@field:Vessel_Listing_Vessel]");
 var now = new Date();
var expiration = new Date("[@field:vessel_certificats_doc_exp]");
  var future = new Date();
future.setDate(future.getDate() + 30);
  
if(expiration > now && expiration < future){
isi.innerHTML = 'RENEW';
isi.style.backgroundColor='red';
}
  else if (expiration = ''){

isi2.style.backgroundColor="";
}

else{
isi.innerHTML = 'OK';
isi.style.backgroundColor='green';
}
</script> 

 

[kpcollier]

@malalibertecom Are you still active on here? I am trying to build a page that is very similar. It consists of our employees, the trainings, and expiration dates of each. How did you create your page? Did you use a pivot table? Could you help me out?

[kpcollier]

Hey @MayMusic, 

Could you help me out? I have a pivot table similar to the ones shown above. My problem is, due to normalization, the labels on top are records from a Training_Table and not fields. However, I just want ANY date in this report that is within 30 days to turn red. 

I appreciate it!

[MayMusic]

Since in pivot you cannot use JS I do no think we can customize the color of the data

[kpcollier]

Thanks, May.

[vanderLeest]

Hi,

I would like the Invoiced value to be red or have a red background if its is higher than the Budget value.

This Image is a snip of a Report Details page, where the fields shown as all Display Only.

Both fields MS_InvAmount and MS_Budget are Currency fields.

I tried versions of the code above but without success.

 

 

 

[Vitalikssssss]

Hi @vanderLeest

You may consider using Formula field for this case.

Here is something similar explained: 

Regards,

vitalikssssss

[vanderLeest]

Thanks ,

Sadly 

CASE
WHEN [@field:MS_InvAmount] > [@field:MS_Budget] 
THEN '<span style="color:#ffff00;">'+[@field:MS_InvAmount]+'</span>'
ELSE [@field:MS_InvAmount]
END

is an invalid formula.

Will the formula field be displayed as short currency, i.e. $12,567?

[vanderLeest]

'<span style="color:#ffff00;">'+[@field:MS_InvAmount]+'</span>'  causes the problem

[Vitalikssssss]

Hi @vanderLeest,

Please make sure that you use the expression in Formula field, since Calculated field/Calculated value do not support HTML output.

Also, you need to tweak your expression because concatenation with "+" available only for string type.
Also both logic block should return same datatype. 

CASE
WHEN [@field:MS_InvAmount] > [@field:MS_Budget] 
THEN '<span style="color:#ffff00;">'+CAST([@field:MS_InvAmount] AS nvarchar)+'</span>'
ELSE CAST([@field:MS_InvAmount] as nvarchar)
END

Hope this helps.

Regards,

vitalikssssss

[vanderLeest]

Thanks this works

I used:

CASE

WHEN LEN([@field:MS_InvAmount]) > 0

AND Len([@field:MS_Budget]) > 0

AND Round([@field:MS_InvAmount],0) > Round([@field:MS_Budget],0)

THEN '<span style="color:red;">'+cast([@field:MS_InvAmount] as nvarchar)+'</span>'

ELSE CAST([@field:MS_InvAmount] as nvarchar)

END

But sadly, instead of $300,000 in red, I get 300000.00 in both the Result Page as well as in the Details Page of my Tabular Report DataPage.

[Leon13]

Great thread y'all!  Hoping this one is easy   
I would like to change the font in my Tabular report based on two keywords:

"Completed" Green Font

"Incomplete"  Red Font

 

Thank you in advance! 

[cheonsa]

2 hours ago, Leon13 said:

Great thread y'all!  Hoping this one is easy   
I would like to change the font in my Tabular report based on two keywords:

"Completed" Green Font

"Incomplete"  Red Font

 

Thank you in advance! 

Hi @Leon13, 

You may try to solution provided here:

 

[Leon13]

Thank you @LittleMsGinger !  Will certainly try and status accordingly.  Appreciate your time!

[pmcfarlain]

None of these solutions are having any effect on my table. Am I doing something wrong?

<a id="visi[@calcfield:1#]">

<script>
var isi = document.getElementById("visi[@calcfield:1#]");
if(isi < 0){
isi.parentNode.parentNode.style.backgroundColor = 'red';
}
else{
isi.parentNode.parentNode.style.backgroundColor = '#NoColor';
}
</script></a> 

This should be able to set the background color to red if the calculated field "Days" (which gets the days left until the project is due) is negative. Ideally, I'd like to set the background yellow and the text red. The text thread wasn't working for me either. Am I using the wrong IDs? or does it not work with calculated fields?

[Kurumi]

This new post might help as well: