0

Modern experience SharePoint List A has a LookUpColumn that retrieves values from NumberColumn in ListB. I need conditional formatting that applies a color across an entire row based on a value in the LookUpColumn.

Currently, lookup columns are not catered for OOTB. So far, I applied JSON but it formats only the column. How do I format an entire row when it is based on a LOOKUP column?

Update: Screenshot show current impact of formatting for row and column: enter image description here

2 Answers 2

1

You have to use view formatting to customize entire row in a SharePoint list.

enter image description here

If you want to just apply the colors to entire row, you should use additionalRowClass property to format the view.

Example: Considering internal name of your lookup column is LookUpColumn, you can use JSON like:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
  "additionalRowClass": "=if([$LookUpColumn.lookupValue] > 75, 'sp-field-severity--good', if([$LookUpColumn.lookupValue] < 75 && [$LookUpColumn.lookupValue] > 50, 'sp-field-severity--low', if([$LookUpColumn.lookupValue] < 50 && [$LookUpColumn.lookupValue] > 25,'sp-field-severity--warning', if([$LookUpColumn.lookupValue] < 25,'sp-field-severity--blocked', ''))))"
}

You can get the correct internal name of your column by following this article: How to find the Internal name of columns in SharePoint Online?

Similarly, you can apply any of the predefined classes given in below links as per your requirements:

  1. Predefined classes
  2. Reusable SharePoint Online Classes for Modern UI

Update from comments:

If [$lkpMile.lookupValue] is not working, try using [$lkpMile] instead of [$lkpMile.lookupValue].

Not sure why [$lkpMile.lookupValue] is not working for you. I have tried it my end & it is working fine for me.

Lookup column settings:

enter image description here

List view formatting output with simple JSON:

enter image description here


Microsoft documentation also says,

The lookup field object has the following properties (with example values):

{
   "lookupId": "100",
   "lookupValue": "North America",
}

Source: JSON formatting - Special string values - check "Lookup fields" section

17
  • Thank you, I tried the code but I keep getting "Incorrect type. Expected "object". And yes, source values are numbers as per the example you used. How do I clear the error? Dec 3, 2021 at 15:41
  • Did you use the correct internal name of your lookup column? Can you add a screenshot of list view or lookup column settings to your original question? Dec 3, 2021 at 16:35
  • 1
    Green squiggly lines are warnings of editor & not error. Not sure why .lookupValue is not working for you, maybe because of how you setup the lookup column. Check my updated answer to see how I have setup lookup column settings. Dec 6, 2021 at 13:19
  • 1
    I apologize, that was my mistake, please confirm, this is the first time I'm actually using this platform's features Dec 7, 2021 at 9:50
  • 1
    You can add one more if condition to check if column is blank. please create a new question & I will provide complete JSON and explanation in the separate answer. Dec 10, 2021 at 8:35
1

Please follow the steps:

1.In my test, this is the [NumberColumn] field and the [LookUpColumn] field

enter image description here

2.Go to your ListA, click All Items >> Format current view

enter image description here

3.On the Format View windows, click "Advanced mode" link

enter image description here

4.Paste the below JSON formatting and click Save

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
  "style": {
    "font-style": "ms-fontColor-alert"
  },
  "additionalRowClass": "=if([$LookUpColumn.lookupValue] > 7, 'ms-bgColor-white', if([$LookUpColumn.lookupValue] <= 7 && [$LookUpColumn.lookupValue] > 3, 'ms-bgColor-yellow',  if([$LookUpColumn.lookupValue] <= 3,'ms-bgColor-orangeLighter', '')))"
}

enter image description here

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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