1

I have two SharePoint lists.

List one has the following columns:

  • Productgroup (text)
  • Min (number, percentage)
  • Max (number, percentage)

List two has columns:

  • a lookup column for Productgroup
  • Added columns for Min and Max connected to the chosen productgroup
  • A column for the actual value (number, percentage)

I would like to have a conditional formatting for the column of the actual value so that

  • If below [Productgroup].[Min] Background turns red.
  • If above [Productgroup].[Max] Background turns orange.
  • If between [Productgroup].[Min] and [Productgroup].[Max] background turns green.

I have tried the following as a start, but that didn't do the job.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "[$Productgroep.Min]"
  "attributes": {
    "class": "=if(@currentField < [Productgroep.Min],'sp-field-severity--warning', '')"
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": "=if(@currentField < [Productgroep.Min],'Error', '')"
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

1 Answer 1

1

If your additional columns for the lookup are in the view, you can access them using their internal names. So your format would look something like this:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "[$Productgroup_x003a_Min]"
  "attributes": {
    "class": "=if(@currentField < [Productgroup_x003a_Min],'sp-field-severity--warning', '')"
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": "=if(@currentField < [Productgroup_x003a_Min],'Error', '')"
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

Generally, additional fields for lookup columns have internal names of LookupColumn:OtherColumn which will be escaped (the colon changes to _x003a_). But you'll need to verify for sure by going to list settings and clicking on the additional column and looking at the name used in the URL.

2
  • Thank you. I have tried to use it, but now the value has become invisible. Sep 20, 2021 at 14:28
  • It seems that my reply got deleted (don't know why). I cannot repeat it in a comment due to the limited number of characters. What should I do? Sep 21, 2021 at 7:12

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.