JSON using multiple conditions within IF statement

[slw_wills]

Hi

I'm new to using JSON and I'm struggling to format some SharePoint metadata which is dependent on 2 columns of data. 

I want to use a traffic light colour system to show expiry dates as red, amber, green but only if the document type is Service Agreement or Contract, all other document types will leave the date as black.

This JSON example below with the results for all document types which works.

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"color": "=if(@currentField <= @now, 'red',if(@currentField <= @now + 5184000000 && @currentField > @now,'orange','green'))"
}
}

But if I try to include an AND statement for the Document Type, something like below, it all goes wrong as all the dates go black.  I obviously don't know how to include an AND statement correctly and in the right place.

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"color": "=if([$Document_x0020_Type] == 'Service Agreement' || [$Document_x0020_Type] == 'Contract' && (@currentField <= @now, 'red', if[$Document_x0020_Type] == 'Service Agreement' || [$Document_x0020_Type] == 'Contract' && (@currentField <= @now + 5184000000 && @currentField > @now,'orange', if[$Document_x0020_Type] == 'Service Agreement' || [$Document_x0020_Type] == 'Contract' && (@currentField > @now + 5184000000, 'green'), 'black')"
  }
}

Any help greatly received.

Thanks in advance.

[#2 · Accepted solution]

Hi @slw_wills,

It seems (at least to me) that there are some syntax errors in your JSON code you are using. To fix it, you may need to correct the placement of parentheses and make sure the syntax is correct for the IF statements. Here's an updated version of the JSON you can try to adapt to your code:

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"color": "=if(@currentField <= @now && ([$Document_x0020_Type] == 'Service Agreement' || [$Document_x0020_Type] == 'Contract'), 'red', if(@currentField <= @now + 5184000000 && @currentField > @now && ([$Document_x0020_Type] == 'Service Agreement' || [$Document_x0020_Type] == 'Contract'), 'orange', if(@currentField > @now + 5184000000 && ([$Document_x0020_Type] == 'Service Agreement' || [$Document_x0020_Type] == 'Contract'), 'green', 'black')))"
}
}

Just a short explanation:

• The document type conditions are added using parentheses ( and ) to ensure the correct evaluation order.
• The && operator is used to combine the conditions for the document type and the expiry date.
• The || operator is used to check if the document type is either "Service Agreement" or "Contract" for each color condition.

Also just make sure to update it to use the actual column name in your SharePoint list (Document_x0020_Type).

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.

Kindest regards,

Leon Pavesic

[#3]

Thank you Leon, this works perfectly. I also appreciate your explanation. Much appreciated.