0

I'm beginner/intermediate when it comes to SharePoint JSON, and I've been trying all sorts of conditional logic to make an empty column txtContent show 'Yes' or 'No'.

There are 7 columns (all date columns) that need to be validated in one sharepoint list - if ANY of them are overdue (equal to or less than @now), then new column should say 'No'. If all of them are greater than @now (or null) then 'Yes'.

I got this far (but of course it doesn't work). JSONLint says it's ok, but there's obviously something weird I'm doing that is keeping this column blank.

Can you see what I've missed or done incorrectly??? (probably loads)

    {
    "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json",

    "elmType": "div",
    "txtContent": "='(' + if([$datecolumn1] <= @now, 'No', if([$datecolumn2] <= @now, 'No', if([$datecolumn3] <= @now, 'No', if([datecolumn4] <= @now, 'No',if([$datecolumn5] <= @now, 'No', if([$datecolumn6] <= @now, 'No', if([$datecolumn7] <= @now, 'No', 'Yes'))))))) + ')'",
    "style": {
        "background-color": "=if(txtContent == 'Yes', '#107c10', if(txtContent == 'No', '#e81123', ''))"
    }

}
4
  • check datecolumn4 missing $?
    – Amos
    Jun 16 '20 at 8:45
  • Try using your condition something like: =IF([$DateColumn1] > @now && [$DateColumn2] > @now && [$DateColumn3] > @now && [$DateColumn4] > @now && [$DateColumn5] > @now && [$DateColumn6] > @now && [$DateColumn7] > @now, "Yes", "No"). It should work for you. Let me know if it works or if you found any issue in this. Jun 16 '20 at 10:21
  • What does && do? I've tried && and || but they give back different answers. I'm not sure if it's being weird because some date fields are blank.
    – ichinap
    Jun 17 '20 at 3:04
  • Does this condition worked for you? We use && for ANDing the two conditions and || for ORing two conditions. Check Operators supported in JSON formatting. Jun 17 '20 at 9:19
0

You can use || (OR operand) to compare multiple columns.

Use below JSON to set the new column value to 'No' if any of the 7 Date columns are overdue else set 'Yes'.

Apply same condition to set the background-color as well.

{
    "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json", 
    "elmType": "div",
    "txtContent": "=if([$datecolumn1] <= @now || [$datecolumn2] <= @now || [$datecolumn3] <= @now || [datecolumn4] <= @now || [$datecolumn5] <= @now || [$datecolumn6] <= @now || [$datecolumn7] <= @now,'No','Yes')",
    "style": {
           "background-color": "=if([$datecolumn1] <= @now || [$datecolumn2] <= @now || [$datecolumn3] <= @now || [datecolumn4] <= @now || [$datecolumn5] <= @now || [$datecolumn6] <= @now || [$datecolumn7] <= @now,'#e81123','#107c10')"
     }
}
2
  • Thank you for the code! It's super helpful in helping me find the answer. I think, though, that it's not allowing for blank date fields (which need to be ignored or flagged as "yes"). I'll fiddle around a bit more, but it's a start.
    – ichinap
    Jun 17 '20 at 3:09
  • You're welcome. For blank date fields it should flagged as 'yes' as all the condition will be false . Please accept the answer if it helped you with the solution. Jun 17 '20 at 3:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.