2

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"
    }
  ]
}

2 Answers 2

2

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. Commented 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? Commented Sep 21, 2021 at 7:12
0

When you have a lookup column, and then other columns associated with that list that you are bringing in, and you want to use them in JSON column formatting, you have to get their specific internal name from the column in the List Settings. It will be in the URL after clicking that column name. It will say "Field=" and then some name. It will not be Productgroep.Min but likely Productgroep_x003a_Min, for example.

So if your "List 1" is called "Productgroep", and you filled in the Title field with names of products, a "Min" column and a "Max" column, and then on "List 2" you have your lookup field called "Productgroep" that points to that Title field on your "Productgroep" list, and you included "Productgroep:Min" and "Productgroep:Max", and you have a column with the value you want, let's call it "Amount", and you want to format "Amount" with red (< Min), orange (> Max), or green (in between the two), it should look something like this:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "attributes": {
            "class": "=if([$Amount]<[$Products_x003a_Min],'sp-css-backgroundColor-BgRed sp-field-fontSizeSmall sp-css-color-WhiteFont',if([$Amount]>[$Products_x003a_Max],'ms-bgColor-orangeLight','sp-css-backgroundColor-BgGreen sp-field-fontSizeSmall sp-css-color-WhiteFont'))"
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": ""
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

The key part of this is the if statement:

=if([$Amount]<[$Products_x003a_Min],
    'sp-css-backgroundColor-BgRed sp-field-fontSizeSmall sp-css-color-WhiteFont',
    if([$Amount]>[$Products_x003a_Max],
        'ms-bgColor-orangeLight',
        'sp-css-backgroundColor-BgGreen sp-field-fontSizeSmall sp-css-color-WhiteFont')
)

You are telling it the background color and font color to use when below the minimum (red), when you are above the minimum (orange), and then the "else" condition just follows in the end of the 2nd if (green).

Your Answer

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

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