0

I have a requirement asking for some crazy If/Then statements for stoplights.

Fields I have:

  1. Need Date Column = Date/Time field

  2. Open Date Column = Date/Time field

  3. Modified Date = Date/Time Field

  4. Status = Choice Field

Requirements of stoplights:

  1. If past the "Need Date" = red

  2. 90 days past the "Open Date" = red (even though it could still be before the need date).

  3. If it is 15 days past the Modified Date it is yellow or if the current status is a "project" (project = long term project) it is also a yellow light.

  4. Default for everything else is green.

Is this possible? if yes, please provide any help.

Thanks.

  • In all your conditions are talking about today? i mean if today is past "Need Date"? and which SharePoint are you using? – harshal gite Nov 12 '18 at 8:10
  • I'm using 2016. And that is correct if today is past "need date". Need Date is 11/12/18 and [Today] is past it. Thanks!! – Chris L Nov 13 '18 at 6:53
0

We can use the calculated field to color fields.

However, Microsoft block the execution of custom markup in calculated fields in SharePoint Online from June 13, 2017 onwards. The June 2017 Public Update (PU) and later PUs will make blocking a configurable option for on-premises use in SharePoint Server 2016 and SharePoint Server 2013. Learn more about handling HTML markup in SharePoint calculated fields.

If you are using SharePoint 2010, you can use the calculated field.

  1. Create a field named “Today” to make the SharePoint to use [Today] (current date) as part of formula. After using Today in calculated field, delete the “Today” field to make SharePoint get the default [Today] current date. If you need to edit formula, you need create “Today” field again, then edit formula. Refer to using Today in calculated field.

  2. Create a calculated field named “Indicator” and return text. Enter the formula below:

    =IF(OR([Need Date]<[Today],[Open Date]+90<[Today]),"red",IF(OR([Modified]+15<[Today],[Status]="project"),"yellow","green"))

  3. Create another calculated field and enter the following formula in the calculated column and output the data with “Number” or “Currency” type:

    ="<div style='font-weight:bold; font-size:50px; color:"&[Indicator]&";'>•</div>"

Note: Change the field names to yours.

If you are using SharePoint 2013 or later, another method, use Client Side Rendering (CSR) to render list view.

  1. Create a field named “Today” to make the SharePoint to use [Today] (current date) as part of formula.

  2. Create a calculated field named “Indicator” and return text. Enter the formula below:

    =IF(OR([Need Date]<[Today],[Open Date]+90<[Today]),"red",IF(OR([Modified]+15<[Today],[Status]="project"),"yellow","green"))

  3. Delete the Today field created in step 1.

  4. Add a Script Editor web part on the page and then enter following script to the web part.

Scripts:

<script src="https://code.jquery.com/jquery-1.11.3.min.js" type="text/javascript"></script>
<script type="text/javascript">
(function () {
    var overrideNameField = {}; 
    overrideNameField.Templates = {};
    // This action will edit the "Indicator" field. You can change the "Status" to another column.
    overrideNameField.Templates.Fields = {
        "Indicator": { "View": overrideNameFieldTemplate }        
    };    

    SPClientTemplates.TemplateManager.RegisterTemplateOverrides(overrideNameField);

})();
function overrideNameFieldTemplate(ctx) { 
//get the column value.
var currentVal= ctx.CurrentItem["Indicator"];
// Render the column
return "<div style='font-weight:bold; font-size:50px; color:"+currentVal+";'>•</div>";
}
</script>

Note: Change the field names to internal names of your fields.

Information about Client Side Rendering

CSR code samples

  • The if then statements worked perfectly! However I'm getting one issue. I have the table set up to "Basice Table" in style view. Indicator Current Status Green <DIV style='font-weight:bold; font-size:50px;margin-top:-30px; color:green;'>•</DIV> Thanks again for all the help – Chris L Nov 14 '18 at 4:42
  • What is the issue? Do you mean it displays as string instead of a stoplight? Did you return the value with “Number” or “Currency” type? What is version of your SharePoint server? – JoannaW_MSFT Nov 14 '18 at 6:07
  • I forgot to change the value to number! Thank you so much. – Chris L Nov 14 '18 at 7:23
  • If you think my suggestion helps you, please accept it as the best answer. Thanks. – JoannaW_MSFT Nov 14 '18 at 7:25
0

We can use calculated fields but the problem here is calculated fields get updated ONLY when item is updated. It does not update everyday on date change. You will have to do this using js or workflow. Or create a calculated column to show the indicators and 1 more column "Today" and update that field everyday using a workflow. Find the steps below.

  1. Create a column "Today" as date and time.
  2. Create another column (calculated column) "Indicator" and use the below formula:

    =IF(OR([NeedDate]<[Today],[OpenDate]+90<[Today]),"<img src='_layouts/images/KPIDefault-2.GIF' border='0'/>",IF(OR([Modified]+15<[Today],[Status]="project"),"<img src='_layouts/images/KPIDefault-1.GIF' border='0'/>","<img src='_layouts/images/KPIDefault-0.GIF' border='0'/>"))
    
  3. Create a workflow which will update the "Today" field everyday using pause action. NOTE: if you are using default "Modified" date column, then this value will change everyday when workflow updates the item.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.