Jump to content
thompson

Change background color after expired

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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> 

 

Share this post


Link to post
Share on other sites

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> 

Share this post


Link to post
Share on other sites

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>

 

Share this post


Link to post
Share on other sites

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

 

 

 

Screen Shot 01-25-18 at 11.02 AM.JPG

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

 

Screen Shot 01-25-18 at 03.45 PM.JPG

Share this post


Link to post
Share on other sites

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>

 

Share this post


Link to post
Share on other sites

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.

 

Screen Shot 02-23-18 at 12.01 PM.JPG

Share this post


Link to post
Share on other sites

 

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> 

 

Share this post


Link to post
Share on other sites

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!

5HPUbM.jpg

Share this post


Link to post
Share on other sites

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.

 

 

 

snip_ReportDetails.png

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!  :)

Share this post


Link to post
Share on other sites
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:

 

Share this post


Link to post
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...