none
Calculated Field Formula RRS feed

  • Question

  • I have created a calculated field and use html for color coding - I want the formula to check the field and if blank leave it blank otherwise add the color - can someone look at my formula and help

    This is my attempt to do this    

    ="DIV style='font-weight:bold; font-size:24px; color:"&IF(Scope1="","",CHOOSE(RIGHT(LEFT(Scope1,1),2),"green","yellow","red")&";'>♦</DIV>"

    my original formula:   =”<DIV style=’font-weight:bold; font-size:24px; color:”&CHOOSE(RIGHT(LEFT(Scope1,1),2),”green”,”yellow”,”red”)&”;’>♦</DIV>”

    Thursday, September 10, 2009 4:46 PM

Answers

  • Hi,

    For script which has to place in CEWP, have a look at the thread

    When the Scope="" you are trying to show white color, but Sharepoint List View rows background color is alternate (in Default view style) like White and Grey, so you have to apply 'Newsletter' style in view settings page which shows white background for all rows in List View or else you have to create a blank transparent image as I already mentioned in my previous reply
    • Marked as answer by Chengyi Wu Friday, September 18, 2009 3:15 AM
    Friday, September 11, 2009 5:20 AM

All replies

  • Try this

    ="<DIV><IMG src='/_layouts/images/KPIDefault-"&IF(Scope1="","blank.GIF",IF(Scope1<>"","1.GIF"))&"'></DIV>"

    Create a blank image, name it as KPIDefault-blank.GIF place in /_layouts/images/ location
    Thursday, September 10, 2009 5:11 PM
  • I am using a hidden CEWP to pull the html from this calculation - so the colors Green, yellow, red are idicated with ♦

    When I use my formula above the field shows the DIV and the color name no color -
    Thursday, September 10, 2009 5:17 PM
  • Also if nothing I see #Value in the field on the list - I really want to see nothing.....
    Thursday, September 10, 2009 5:18 PM
  • Am I assuming that if the value is '#' you want to show blank....if so, try the below one.

    ="<DIV><IMG src='/_layouts/images/KPIDefault-"&IF(Scope1="","blank.GIF",IF(Scope1<>"","1.GIF",IF(Scope1="#","blank.GIF")))&"'></DIV>"
    Thursday, September 10, 2009 5:24 PM
  • can you show the script you have in CEWP to show the colour? you may need to need to use innerHTML to see the color and not the text.

    Thursday, September 10, 2009 5:29 PM
  • I ended up using "White" and it will not show:

    ="<DIV style='font-weight:bold; font-size:24px; color:"&IF([P Scope]="1","green",IF([P Scope]="2","yellow",IF([P Scope]="3","Red",IF([P Scope]="","white"))))&";'>♦</DIV>"

    Thursday, September 10, 2009 6:17 PM
  • Hi,

    For script which has to place in CEWP, have a look at the thread

    When the Scope="" you are trying to show white color, but Sharepoint List View rows background color is alternate (in Default view style) like White and Grey, so you have to apply 'Newsletter' style in view settings page which shows white background for all rows in List View or else you have to create a blank transparent image as I already mentioned in my previous reply
    • Marked as answer by Chengyi Wu Friday, September 18, 2009 3:15 AM
    Friday, September 11, 2009 5:20 AM