cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
najwakaa
Level: Power Up

Sharepoint list JSON column with two conditional

I have two column in sharepoint list. 

Column A is Date & Column B is text

I would like to create a logic where the row will be highlighted to red if Column A is past today date AND Column B is blank. 

I found a code that will help to highlight column A if column B is blank. 

Refer to the picture, and even the future date (the one I circled) is still highlighted. 

However, I could not figure out how add the code to not highlight the date if the due date is not past yet. 

Can anyone help me?

p/s: if possible, how do I color the whole row red instead only the date cell? 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Sharepoint list JSON column with two conditional

Hi @najwakaa ,

Could you please share a bit more about your scenario?

Do you want to highlight the SP List Row to Red if Column A is past today date AND Column B is blank within your SP list itself?

Further, could you please share a bit more about the formula you used to highlight? Do you use SharePoint column formatting formula to achieve your needs?

 

Based on the needs that you mentioned, I have made a test on my side, and don't have the issue that you mentioned. Please take a try with the following workaround:1.JPGUnder Column Settings, choose Format this column, then click "Advanced mode", type the following formula:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
            "background-color": "=if([$Response]=='' && [$Due_x0020_Date] < @now, '#ff0000', '')"
  }
}

On your side, you should type the following formula:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if([$Response] == '' && [$Date] < @now, '#ff0000', '')"
  }
}

In addition, the SharePoint Column formatting formula only apply to column level rather than row level. If you want to highlight the row to Red if Column A is past today date AND Column B is blank, I afraid that there is no direct way to achieve your needs.

As an alternative solution, you could consider take a try to put above formula within the Column Format Box of all columns in your SP list.

 

More details about the SharePoint column format formula, please check the following article:

https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Sharepoint list JSON column with two conditional

Hi @najwakaa ,

Could you please share a bit more about your scenario?

Do you want to highlight the SP List Row to Red if Column A is past today date AND Column B is blank within your SP list itself?

Further, could you please share a bit more about the formula you used to highlight? Do you use SharePoint column formatting formula to achieve your needs?

 

Based on the needs that you mentioned, I have made a test on my side, and don't have the issue that you mentioned. Please take a try with the following workaround:1.JPGUnder Column Settings, choose Format this column, then click "Advanced mode", type the following formula:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
            "background-color": "=if([$Response]=='' && [$Due_x0020_Date] < @now, '#ff0000', '')"
  }
}

On your side, you should type the following formula:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if([$Response] == '' && [$Date] < @now, '#ff0000', '')"
  }
}

In addition, the SharePoint Column formatting formula only apply to column level rather than row level. If you want to highlight the row to Red if Column A is past today date AND Column B is blank, I afraid that there is no direct way to achieve your needs.

As an alternative solution, you could consider take a try to put above formula within the Column Format Box of all columns in your SP list.

 

More details about the SharePoint column format formula, please check the following article:

https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

najwakaa
Level: Power Up

Re: Sharepoint list JSON column with two conditional

Thank you. It works. I didn't know that && can work on JSON. I was using the nested if and always get an error. I found that I was using the wrong field name.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 243 members 5,045 guests
Recent signins:
Please welcome our newest community members: