Sharepoint List,
I have a 'Status' column (Completed, In Progress, Requested, Created) and a Date Received column. I would like to 'Date Received' column to turn the text bold, red, 30 days (2592000000) after received, if the 'Status' column is not 'Completed'.
I've spent a few hours looking at different JSON code, but can't seem to nail this one down. Thanks in advance.
Store this reference in a ready location until you do this with ease: https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/formatting-syntax-reference
You want to style (!) one column based on another column. In the formatting JSON, style is an object with many properties that you specify. You should know that these '=if()' functions don't work with the on-prem SP versions. You will probably have to use the operator()/operand() functions to get the values.
You set the values of those properties based on conditions, so you will likely be using "=if(condition, true-condition, false-condition)" syntax, and often with a lot of nesting of these =if() tests. The properties of the style object will be CSS-like property names and property values.
Based on your description, you want only the Date Received column to have BOLD RED text (CSS properties "font-weight" and "color" being necessary) based on the current date (a NOW() function) being 30 days past the value in the Date Received (get field value of this column) AND the status column not having "Completed" as the value of that 4-choice column.
Start with your object, and insert the style object with relevant properties "font-weight" and "color". The values will be "bold" and "red" (CSS property values for those properties) based on the 'if()' setup.
The final version I came up with is below:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"font-weight": "=if([$Status] != 'Completed' && (Number(@now) - Number(@currentField))/(24*60*60*1000) >= 30, 'bold', 'normal')",
"color": "=if([$Status] != 'Completed' && (Number(@now) - Number(@currentField))/(24*60*60*1000) >= 30, 'red', 'black')"
}
}
For me, I have not reached "whiz" level on column and row formatting (row formatting has its own properties and different schema).
So there is a LOT of trial and error. Strip away properties and add slowly. Build formula that produce property values slow.
The JSON above is tested by me. In the image below, I applied the formatting to Date Received column. The Status column is a four-choice column. I also put in a calculated column that computes NOW() minus Date Received value, giving the result in number of days (including fractional days).
In the image below, I changed the Status of the 3rd item from 'Completed' to 'Requested'. The Date Received was already more than 30 days (54+ days) and now the other condition is met (Status not equal to 'Completed'), so the date value is bold and red.
Some notes: the '=if(condition[s], true-condition, false-condition)' is Excel-like in arrangement of parameters, but it stop there. In Excel you might write the formula
= if (AND([Status] <> "Completed", NOW() - [Date Received] >= 30), 'red', 'black')
for the color value setting. But in fact, you use boolean operators in C/C#/JavaScript/TypeScript-like languages:
= if ([$Status] != 'Completed' && (Number(@now) - Number(@currentField))/(24*60*60*1000) >= 30, 'red', 'black')
I am not up on all the functions available for date manipulations (date math). The Number() converts dates to milliseconds, and the rest is math.
