Forum Discussion

AnatolyD's avatar
AnatolyD
Copper Contributor
Oct 13, 2020

Conditional Formatting SharePoint List

Hello team,

 

I need your support with conditional JSON formatting of SharePoint list.

 

I would like to format column INVEST_DUE_DATE based on current date (date cell) and based on value in column Phase (string).

 

 

Right now, I implemented just formatting based on date, using following code:

 

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if(@currentField < @now , '#f7adad', if(@currentField <= @now + 864000000, '#f2d596', if(@currentField >= @now + 864000000 , '#a0dec0', '')))"
}
}

 

I want to apply additional condition for it - use color formatting only if value of cell Phase = Investigation.

 

So , it should be something like this

(@currentField < @now) and @[$Phase]='Investigation' , '#f7adad',  and etc....

But it does not works....

 

Could you please advise me how to modify initial JSON code?

 

Thank you in advance! 

 

 

Conditional Formatting
date and text
json
JSON formatting

5 Replies

  • ganeshsanap's avatar
    ganeshsanap
    MVP
    Oct 13, 2020

    AnatolyD, You need to use the && (AND) operator for this. use your conditions in below format: 

     

     

    =if((@currentField < @now) && [$Phase]='Investigation', '#f7adad','#f2d596')

     

     

    Where Phase is the internal name of your "Phase" column with $ (needed in JSON formatting to add reference to column). Follow this article to get the internal name of your SharePoint column: How to find the Internal name of columns in SharePoint Online? 

     

    References: Use column formatting to customize SharePoint 

    Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

    For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

    • AnatolyD's avatar
      AnatolyD
      Copper Contributor
      Oct 13, 2020

      ganeshsanap 

      Hello! thanks.

       

      I tried this one

      {
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if((@currentField < @now) && [PHASE]='Investigation', '#f7adad', if((@currentField <= @now + 864000000) && [PHASE]='Investigation', '#f2d596', if((@currentField >= @now + 864000000) && [PHASE]='Investigation' , '#a0dec0', '')))"
}
}

       

      But something is wrong....

       

      Column values disappear, like with a wrong condition.

       

       

      You proposed an option for 2 colors, but I m using 3 colors.

      Sorry, I m not familiar with this syntax....

       

      Column name is "PHASE"

       

       

      • ganeshsanap's avatar
        ganeshsanap
        MVP
        Oct 13, 2020

        AnatolyD , Use below condition:

         

         

        =if((@currentField < @now) && [$PHASE]='Investigation', '#f7adad', if((@currentField <= @now + 864000000) && [$PHASE]='Investigation', '#f2d596', if((@currentField >= @now + 864000000) && [$PHASE]='Investigation' , '#a0dec0', '')))

         

         

        If you want to apply the background color only if PHASE="Investigation" then you can use below condition as well:

         

         

        =if([$PHASE]='Investigation', if(@currentField < @now, '#f7adad', if(@currentField <= (@now + 864000000), '#f2d596', if(@currentField >= (@now + 864000000), '#a0dec0', ''))), '')

         

        Please click Mark as Best Response if my post helped you solve your issue. This will help others find the correct solution easily. It also closes the item. If the content was useful in other ways, please consider giving it Like.

         

Resources