Jump to content

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.

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> 

 

Link to post
Share on other sites
  • 2 months later...

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> 

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>

 

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

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

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

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>

 

Link to post
Share on other sites
  • 4 weeks later...

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

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> 

 

Link to post
Share on other sites
  • 11 months later...
  • 1 year later...

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

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?

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

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.

Link to post
Share on other sites
  • 4 weeks later...

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