How to Use SharePoint Row Formatting to Make Your Lists Stand Out based on a LOOKUP column value using JSON

 2,127 total views,  1 views today

In this conditional SharePoint row formatting article, we will learn how to conditionally format SharePoint Online list items based on a LOOKUP column value using the JSON formatting code.

Introduction – conditional SharePoint row formatting

Generally, we could straightway apply the conditional row formatting with column data types like a single line of text, choice, etc. but it is quite challenging to apply the conditional row formatting with the lookup column value condition.

In this article, we will show the demo first, then we will show the implementation step by step.

Demo – conditional row formatting with lookup column value in SharePoint Online list

Here we have a list called “Project Management” with the below columns:

• Project Name (Title column renamed to this)
• Status type of Lookup, getting data (Title column value) from the “Project Status” list.
• Client – Single Line of Text

The row color condition is as below:

• If the project status is “In progress“, the color for that row will be Green.
• If the project status is “Close to starting“, the color for that row will be Light Green.
• If the project status is “In progress but pause“, the color for that row will be Red.
• If the project status is “In progress with pending action“, the color for that row will be Yellow.
• If the project status is “Not yet started“, the color for that row will be Gray.
• If the project status is “Successfully completed“, the color for that row will be Blue.

Lookup field definition for the conditional row formatting

Below is the “Project Status” parent list for the status column in the project management list below columns:

• The default Title column is renamed to “Project Status”
• Color – Single Line of Text column.

Sharepoint online list row formatting with JSON – conditional row formatting

Below is the JSON code which is actually doing the above row formatting conditionally.

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
"style": {
"font-style": "ms-fontColor-alert"
},
"additionalRowClass": "=if([$Status.lookupValue] =='Not yet started' , 'ms-bgColor-gray110', if([$Status.lookupValue] =='Close to starting', 'ms-bgColor-greenLight', if([$Status.lookupValue] =='In progress', 'ms-bgColor-green',if([$Status.lookupValue] =='In progress with pending action', 'ms-bgColor-yellow',if([$Status.lookupValue] =='In progress but pause', 'ms-bgColor-red', if([$Status.lookupValue] =='Successfully completed' ,'ms-bgColor-blue', '')))"
}

Notes:

• In the above code $Status the lookup column name from the child list i.e. “Project Management”
• For the lookup column, we need to write <YourLookupColumnName>.lookupValue
• For the string comparison, we need to write double equal (==).
• In the above example, we have shown if condition with the single value even we could and/or condition.

AND OR condition in conditional formatting using JSON -conditional row formatting

AND Condition:

if([$LookUpColumn.lookupValue] == 'Your condition check string value' && [$LookUpColumn.lookupValue] == 'Your condition check string value', 'ms-bgColor-blue'

OR Condition:

if([$LookUpColumn.lookupValue] == 'Your condition check string value' || [$LookUpColumn.lookupValue] == 'Your condition check string value', 'ms-bgColor-blue'

By now we have learned how to write code for the conditional row formatting in the SharePoint Online list. Now, we will learn how to apply the above conditional row formatting JSON code into the SharePoint list.

How to apply conditional row formatting in SharePoint online list

Go to your list.

Select the default view “All Items”.

Click on the “Format current view” link.

Format current view in SharePoint Online – conditional row formatting

There will be two radio buttons.

• Alternating row styles
• Conditional formatting

Select, the conditional formatting radio button. Then click on the “Advanced mode” link.

A default JSON box will be opened along with some predefined JSON codes.

Replace the default JSON code with the custom JSON code which we have shown in the above steps.

Click on the “Save” button, and that’s it – you are done with the row formatting. 🙂

SharePoint list conditional formatting entire row with the text column

In the above example, we have seen how to apply conditional formatting with the lookup column values, in the below example we will show how to work other types of columns like Single Line of Text, Choice, Date, etc.

The single line of Text, Choice Type

 "additionalRowClass": "=if([$ColumnName] == 'Your condition check string value' && [$ColumnNameText] == 'Your condition check string value', 'ms-bgColor-blue'

The Date Column Type

 "additionalRowClass": "=if([$DueDate] <= @now , 'ms-bgColor-red''')))"

In the above demo, we have used the Lookup column, so for the SharePoint beginner, we should let them know what is lookup column is in the SharePoint list and how to create it.

What is lookup column in SharePoint list?

Using the lookup column in SharePoint we can maintain the parent child relationship between the lists, the way we do in SQL database table parent-child relationship using the foreign key.

How to create lookup column in SharePoint list?

Let’s take our demo as an example. We have seen the “Project Status” list with the “Project Status” and color column – in the demo scenario, this is the parent list. We do have a “Project Management” list with the Project Title, Status, and Client column. In this demo scenario, this is the child list – here the “Status” column is the lookup to the project status column in the “Project Status” list.

Let’s get started on how to create the lookup type column in SharePoint Online step by step.

Click on the +Add column link. It will open the column type selection window.

Click on the Lookup link.

On the next screen pass the below parameters:

• Column Name – any name you could give.
• Select a list as a source – select your parent list
• Select a column from the list above – select the column to which you want to connect or map.

Notes:

• We have made the “Project Status” list name and “Project Status” the same name, so, let us not get confused.
• While creating the lookup column we could see the “Title” column, not the “Project Status” as the source column which is supposed to be the “Project Status” column – because we had just renamed the “Title” column to “Project Status” but the internal column name still the “Title”, so, we could see the “Title” column which is the value of “Project Status” column from the “Project Status” list. So, let us not get confused here too.

Conditional row formatting with lookup column – starts with condition in JSON SharePoint online

Let’s say we have a project list with the list of projects where “ProjectLookupColumn” is a lookup type of column, the values in the “ProjectLookupColumn” are as below:

• SharePoint-Project1
• SharePoint-Project2
• SharePoint-Project3
• Java-Project1
• Java-Project2
• Java-Project3
• DotNet-Project1
• DotNet-Project2
• DotNet-Project3

Here the requirement is for all the SharePoint projects the lines will be blue and the DotNet project lines will be in green and so on.

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
"style": {
"font-style": "ms-fontColor-alert"
},
"additionalRowClass": "=if([$ProjectLookupColumn.lookupValue] == 'SharePoint-Project1', 'ms-bgColor-blue',if([$ProjectLookupColumn.lookupValue] == 'SharePoint-Project2', 'ms-bgColor-blue',if([$ProjectLookupColumn.lookupValue] == 'SharePoint-Project1', 'ms-bgColor-blue','')))"
}

For this use case, we can write multiple “if conditions” and change color using JSON formatting, we have shown this in the above JSON code.

Now, think about the real-time problem – if you have hundred plus projects with a similar group or technology, it is very difficult to handle or not recommended going with the multiple “if conditions”, in this scenario, instead of writing multiple if conditions we can use the “startsWith” condition using the below code:

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
"style": {
"font-style": "ms-fontColor-alert"
},
"additionalRowClass": "=if(startsWith([$ProjectLookupColumn.lookupValue], 'SharePoint'), 'ms-bgColor-blue', if(startsWith([$ProjectLookupColumn.lookupValue], 'Java'), 'ms-bgColor-red', if(startsWith([$ProjectLookupColumn.lookupValue], 'DotNet'), 'ms-bgColor-green', '')))"
}

The above code will just check the text in the lookup column if the lookup column value starts with “SharePoint”, the line will be blue if the lookup column value starts with “Java” the line will be red and if the lookup column value starts with “DotNet” the line will be green.

Summary: Conditional row formatting (SharePoint row formatting examples)

Thus, in this article, we have learned the below with respect to conditional row formatting in SharePoint Online using the JSON:

• How to conditionally format the SharePoint list items row using JSON
• How to conditionally format the SharePoint list items row with the lookup column value using JSON
• What is a lookup column and how to create a lookup column in the SharePoint Online list

See Also: Conditional row formatting

You may also like the below JSON formatting articles:

• HTML Table in Power Automate: Learn HTML table formatting using Microsoft Power Automate step by step
• Column header formatting in SharePoint list Quick Edit or Datasheet View
• SharePoint Hyperlink in Text Field: Convert a single line of text column to hyperlink using SharePoint column formatting
• Conditionally show or hide columns in a SharePoint list
• SharePoint Online – O365 – How to create a Tile view in modern SharePoint online lists and configure it.
• Refer to this for the fluent-ui-core-color

If this article helps you, please appreciate our efforts by writing a comment below. If you have a better idea or solution to this topic, please write a comment; we will include that in this article. Thank you, and happy reading, learning, and sharing! 🙂

About Post Author

Global SharePoint

SharePoint Specialist (2019 | 2016 | 2013 | 2010 | 2007 | SharePoint Online | Microsoft 365)

See author's posts