question

needhelpwithJSON-6781 avatar image
0 Votes"
needhelpwithJSON-6781 asked needhelpwithJSON-6781 commented

JSON Code help (Sharepoint)

59571-expiry-date.png
I have been using this JSON helped with and it has been working great the only issue being the background colour for when the field is empty.
can someone assist? How do I change it that (when the date expires it goes red) (90 days before expiry yellow), green when it's not in 90 days before expiry) and when the field is empty it stays the same as the background.
Any help would be appreciated.

Thank you.

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json";,
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if(@currentField <=@now ,'#f53337',if(@currentField <=(@now+7776000000) ,'#96882b','#458b50'))"
}
}


office-sharepoint-onlineoffice-sharepoint-server-development
expiry-date.png (6.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EmilyDu-MSFT avatar image
0 Votes"
EmilyDu-MSFT answered

@needhelpwithJSON-6781

You could use below code.

 {
   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
   "elmType": "div",
   "txtContent": "@currentField",
   "style": {
   "background-color": "=if((@currentField, if(@currentField <@now, '#f53337', if(@currentField <=@now+7776000000, '#96882b', '#458b50')),'')"
   }
 }

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnupamShri avatar image
0 Votes"
AnupamShri answered needhelpwithJSON-6781 commented

I guess you want something like this:

59587-image.png

Why don't you use the Full JSON syntax as explained here column-formatting. It handles "null" values easily.

Some quick tweaking will give you something like this... you can play with the JSON yourself to make it as per your requirement.

 {
   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
   "elmType": "div",
   "style": {
     "box-sizing": "border-box",
     "padding": "0 2px"
   },
   "attributes": {
     "class": {
       "operator": ":",
       "operands": [
         {
           "operator": "==",
           "operands": [
             {
               "operator": "Date()",
               "operands": [
                 {
                   "operator": "toDateString()",
                   "operands": [
                     {
                       "operator": "Date()",
                       "operands": [
                         "[$DueDate]"
                       ]
                     }
                   ]
                 }
               ]
             },
             {
               "operator": "Date()",
               "operands": [
                 {
                   "operator": "toDateString()",
                   "operands": [
                     {
                       "operator": "Date()",
                       "operands": [
                         null
                       ]
                     }
                   ]
                 }
               ]
             }
           ]
         },
         "sp-css-backgroundColor-neutralBackground",
    
    
   {
           "operator": ":",
           "operands": [
             {
               "operator": "<=",
               "operands": [
                 {
                   "operator": "Date()",
                   "operands": [
                     {
                       "operator": "toDateString()",
                       "operands": [
                         {
                           "operator": "Date()",
                           "operands": [
                             "[$DueDate]"
                           ]
                         }
                       ]
                     }
                   ]
                 },
                 {
                   "operator": "Date()",
                   "operands": [
                     {
                       "operator": "toDateString()",
                       "operands": [
                         {
                           "operator": "Date()",
                           "operands": [
                             "@now"
                           ]
                         }
                       ]
                     }
                   ]
                 }
               ]
             },
             "sp-css-backgroundColor-errorBackground",
         {
           "operator": ":",
           "operands": [
             {
               "operator": "<=",
               "operands": [
                 {
                   "operator": "Date()",
                   "operands": [
                     {
                       "operator": "toDateString()",
                       "operands": [
                         {
                           "operator": "Date()",
                           "operands": [
                             "[$DueDate]"
                           ]
                         }
                       ]
                     }
                   ]
                 },
                 {
                   "operator": "Date()",
                   "operands": [
                     {
                       "operator": "toDateString()",
                       "operands": [
                         {
                           "operator": "+",
                           "operands": [
                             "@now",
    7776000000
                           ]
                         }
                       ]
                     }
                   ]
                 }
               ]
             },
             "sp-css-backgroundColor-warningBackground50",
             "sp-css-backgroundColor-successBackground50"
           ]
         }
       ]
     }
    ]
  }
   },
   "children": [
     {
       "elmType": "span",
       "style": {
         "line-height": "16px",
         "height": "14px"
       },
       "attributes": {
         "iconName": {
           "operator": ":",
           "operands": [
             {
               "operator": "==",
               "operands": [
                 {
                   "operator": "Date()",
                   "operands": [
                     {
                       "operator": "toDateString()",
                       "operands": [
                         {
                           "operator": "Date()",
                           "operands": [
                             "[$DueDate]"
                           ]
                         }
                       ]
                     }
                   ]
                 },
                 {
                   "operator": "Date()",
                   "operands": [
                     {
                       "operator": "toDateString()",
                       "operands": [
                         {
                           "operator": "Date()",
                           "operands": [
                             null
                           ]
                         }
                       ]
                     }
                   ]
                 }
               ]
             },
             "",
             {
               "operator": ":",
               "operands": [
                 {
                   "operator": "<",
                   "operands": [
                     {
                       "operator": "Date()",
                       "operands": [
                         {
                           "operator": "toDateString()",
                           "operands": [
                             {
                               "operator": "Date()",
                               "operands": [
                                 "[$DueDate]"
                               ]
                             }
                           ]
                         }
                       ]
                     },
                     {
                       "operator": "Date()",
                       "operands": [
                         {
                           "operator": "toDateString()",
                           "operands": [
                             {
                               "operator": "Date()",
                               "operands": [
                                 "@now"
                               ]
                             }
                           ]
                         }
                       ]
                     }
                   ]
                 },
                 "",
                 ""
               ]
             }
           ]
         }
       }
     },
     {
       "elmType": "span",
       "style": {
         "overflow": "hidden",
         "text-overflow": "ellipsis",
         "padding": "0 3px"
       },
       "txtContent": "[$DueDate.displayValue]",
       "attributes": {
         "class": {
           "operator": ":",
           "operands": [
             {
               "operator": "==",
               "operands": [
                 {
                   "operator": "Date()",
                   "operands": [
                     {
                       "operator": "toDateString()",
                       "operands": [
                         {
                           "operator": "Date()",
                           "operands": [
                             "[$DueDate]"
                           ]
                         }
                       ]
                     }
                   ]
                 },
                 {
                   "operator": "Date()",
                   "operands": [
                     {
                       "operator": "toDateString()",
                       "operands": [
                         {
                           "operator": "Date()",
                           "operands": [
                             null
                           ]
                         }
                       ]
                     }
                   ]
                 }
               ]
             },
             "",
             {
               "operator": ":",
               "operands": [
                 {
                   "operator": "<",
                   "operands": [
                     {
                       "operator": "Date()",
                       "operands": [
                         {
                           "operator": "toDateString()",
                           "operands": [
                             {
                               "operator": "Date()",
                               "operands": [
                                 "[$DueDate]"
                               ]
                             }
                           ]
                         }
                       ]
                     },
                     {
                       "operator": "Date()",
                       "operands": [
                         {
                           "operator": "toDateString()",
                           "operands": [
                             {
                               "operator": "Date()",
                               "operands": [
                                 "@now"
                               ]
                             }
                           ]
                         }
                       ]
                     }
                   ]
                 },
                 "",
                 ""
               ]
             }
           ]
         }
       }
     }
   ]
 }




image.png (6.6 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your help.
I think my skills with JSON not quite up to scratch enough to be able to change the areas needed in time for the site launch.

0 Votes 0 ·