SOLVED

JSON If Statement

%3CLINGO-SUB%20id%3D%22lingo-sub-2866805%22%20slang%3D%22en-US%22%3EJSON%20If%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2866805%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%20have%20an%20If%20Statement%20that%20conditionally%20formats%20the%20background%20based%20on%20a%20date%20as%20follows%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%22attributes%22%3C%2FSPAN%3E%3CSPAN%3E%3A%20%7B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%22class%22%3C%2FSPAN%3E%3CSPAN%3E%3A%20%3C%2FSPAN%3E%3CSPAN%3E%22%3Dif(%40currentField%20%26lt%3B%3D%20%40now%2C'sp-field-severity--blocked'%2C%20if(%40currentField%20%26lt%3B%3D%20%40now%20%2B%207776000000%2C%20'sp-field-severity--warning'%2C%20if(%40currentfield%20%26gt%3B%3D%20%40now%20%2B%2078624000000%2C%20'sp-field-severity--good'%2C'sp-field-severity--good')))%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EI%20need%20to%20amend%20this%20to%20add%20an%20additional%20criteria%20based%20on%20a%20text%20value%20and%20tried%20adding%20%3CSPAN%3Eif(%40currentField%20%3D%3D%20'Failed'%2C%20'sp-field-severity--blocked'%20%3CFONT%20color%3D%22%23000000%22%3Ebut%20its%20not%20working%2C%20any%20help%20on%20this%20appreciated.%20%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2866805%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EJSON%20If%20Statement%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2868316%22%20slang%3D%22en-US%22%3ERe%3A%20JSON%20If%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2868316%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1191318%22%20target%3D%22_blank%22%3E%40newbeetle%3C%2FA%3E%26nbsp%3Byour%20logic%20is%20flawed%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%26nbsp%3B%3CSPAN%3E%3CEM%3Eif(%40currentField%20%26lt%3B%3D%20%40now%2C'sp-field-severity--blocked'%2C%20if(%40currentField%20%26lt%3B%3D%20%40now%20%2B%207776000000%3C%2FEM%3E%20-%20a%20date%20could%20be%20both%20of%20these%20so%20it%20wouldn't%20know%20how%20to%20apply%20the%20formatting%20consistently%20correctly.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E2)%20you%20can't%20have%20%22Failed%22%20in%20a%20date%2Ftime%20column.%20You%20would%20need%20to%20have%20the%20status%20in%20a%20different%20column%20and%20then%20use%20that%20in%20the%20JSON%20formula.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20will%20post%20a%20solution%20shortly%20using%20more%20simple%20JSON%20than%20the%20class%2Fattributes%20method.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EIntranet%2C%20SharePoint%20and%20Power%20Platform%20Manager%20(and%20classic%201967%20Morris%20Traveller%20driver)%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi I have an If Statement that conditionally formats the background based on a date as follows;

 

"attributes": {
"class": "=if(@currentField <= @now,'sp-field-severity--blocked', if(@currentField <= @now + 7776000000, 'sp-field-severity--warning', if(@currentfield >= @now + 78624000000, 'sp-field-severity--good','sp-field-severity--good')))"
},
 
I need to amend this to add an additional criteria based on a text value and tried adding if(@currentField == 'Failed', 'sp-field-severity--blocked' but its not working, any help on this appreciated.
4 Replies

@newbeetle your logic is flawed:

 

1) if(@currentField <= @now,'sp-field-severity--blocked', if(@currentField <= @now + 7776000000 - a date could be both of these so it wouldn't know how to apply the formatting consistently correctly.

 

2) you can't have "Failed" in a date/time column. You would need to have the status in a different column and then use that in the JSON formula.

 

I will post a solution shortly using more simple JSON than the class/attributes method.

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

Hi Rob,

 

I see what you mean, I'm very new to JSON but have applied your advice and have utilised an additional column and now have this working.

 

Many thanks :smile:

best response confirmed by newbeetle (Occasional Contributor)
Solution

@newbeetle the simpler JSON is:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if([$Status] == 'Failed', '#7d0000', ((if(@currentField <= @now, 'red', if((@currentField >= @now && @currentField <= @now + 7776000000),'orange','green')))",
    "color": "white"
  }
}

 

conditionalFormatDateColumn4.png

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

Thanks Rob, yes that is much easy.