1

I have SPO list with a multi-value field, which is filled by a lookup list (other list on the same SharePoint). The values are the names of MS Fluent Icons. Now I want the names in that field to be converted to the corresponding icons.

Unformatted column

enter image description here

My JSON code so far (which just produces a blank field :( )

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",

"elmType": "div",
"style": {
  "flex-direction": "column",
  "align-items": "flex-start",
  "margin-top": "2px",
  "padding-right": "0px",
  "width": "100%",
  "overflow": "hidden"
},
"children": [
  {
"forEach": "IconIterator in @currentField", 
"elmType": "div",
"style": {
  "font-size": "24px",
  "color": "#333333",
  "vertical-align": "middle",
  "width": "24px"
},
"attributes": {
  "iconName": "[$IconIterator]",
  "title": ""
}}]}
1

Your JSON is largely correct, if you are talking about standard Lookup fields. This means the primary lookup field where the value comes from the "In this column" dropdown (shown in the red box below). However, SharePoint changes the format of the values when in a projected field (those extra columns you select as shown in the yellow box below):

Column Settings for Lookup Column

You can see how these types of fields are handled differently (for the exact same values) in the standard list view with no formatting applied:

enter image description here

In a multi-lookup field, the primary value will come back as an array and the forEach pattern you've employed works. Here is a slight modification (to reference the subproperty of lookupValue) to your JSON that will work in this case:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "flex-direction": "column",
    "align-items": "flex-start",
    "margin-top": "2px",
    "padding-right": "0px",
    "width": "100%",
    "overflow": "hidden"
  },
  "children": [
    {
      "forEach": "IconIterator in @currentField",
      "elmType": "div",
      "style": {
        "font-size": "24px",
        "color": "#333333",
        "vertical-align": "middle",
        "width": "24px"
      },
      "attributes": {
        "iconName": "[$IconIterator.lookupValue]"
      }
    }
  ]
}

In a multi-lookup projected field, the values will come back as a single string with multiple values separated by a space and a semi-colon. In this case, the forEach cannot be used. Even sadder, there isn't yet a split operation that would allow us to work with the values as expected. In this case, we have to get creative with a series of conditional elements that are extracting values using a complex combination of if, substring, and indexOf operators. This approach does work but has some drawbacks:

  • It's super complex to read/write
  • It can only handle a fixed set of values with each value having increasingly complex formulas to extract those values

Here is a reworked version of your JSON that accomplishes this for up to 5 values:

{
  "elmType": "div",
  "debugMode": true,
  "style": {
    "flex-direction": "column",
    "align-items": "flex-start",
    "margin-top": "2px",
    "padding-right": "0px",
    "width": "100%",
    "overflow": "hidden"
  },
  "children": [
    {
      "elmType": "div",
      "attributes": {
        "iconName": "=if(indexOf(@currentField,';')>=0,substring(@currentField,0,indexOf(@currentField,';')),@currentField)"
      },
      "style": {
        "display": "=if(@currentField,'inherit','none')",
        "font-size": "24px",
        "color": "#333333",
        "vertical-align": "middle",
        "width": "24px"
      }
    },
    {
      "elmType": "div",
      "attributes": {
        "iconName": "=if(indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')>=0,substring(substring(@currentField,indexOf(@currentField,';')+2,1000),0,indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')),substring(@currentField,indexOf(@currentField,';')+2,1000))"
      },
      "style": {
        "display": "=if(indexOf(@currentField,';')>=0,'inherit','none')",
        "font-size": "24px",
        "color": "#333333",
        "vertical-align": "middle",
        "width": "24px"
      }
    },
    {
      "elmType": "div",
      "attributes": {
        "iconName": "=if(indexOf(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),';')>=0,substring(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),0,indexOf(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),';')),substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000))"
      },
      "style": {
        "display": "=if(indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')>=0,'inherit','none')",
        "font-size": "24px",
        "color": "#333333",
        "vertical-align": "middle",
        "width": "24px"
      }
    },
    {
      "elmType": "div",
      "attributes": {
        "iconName": "=if(indexOf(substring(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),indexOf(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),';')+2,1000),';')>=0,substring(substring(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),indexOf(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),';')+2,1000),0,indexOf(substring(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),indexOf(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),';')+2,1000),';')),substring(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),indexOf(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),';')+2,1000))"
      },
      "style": {
        "display": "=if(indexOf(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),';')>=0,'inherit','none')",
        "font-size": "24px",
        "color": "#333333",
        "vertical-align": "middle",
        "width": "24px"
      }
    },
    {
      "elmType": "div",
      "attributes": {
        "iconName": "=if(indexOf(substring(substring(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),indexOf(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),';')+2,1000),indexOf(substring(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),indexOf(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),';')+2,1000),';')+2,1000),';')>=0,substring(substring(substring(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),indexOf(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),';')+2,1000),indexOf(substring(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),indexOf(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),';')+2,1000),';')+2,1000),0,indexOf(substring(substring(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),indexOf(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),';')+2,1000),indexOf(substring(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),indexOf(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),';')+2,1000),';')+2,1000),';')),substring(substring(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),indexOf(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),';')+2,1000),indexOf(substring(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),indexOf(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),';')+2,1000),';')+2,1000))"
      },
      "style": {
        "display": "=if(indexOf(substring(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),indexOf(substring(substring(@currentField,indexOf(@currentField,';')+2,1000),indexOf(substring(@currentField,indexOf(@currentField,';')+2,1000),';')+2,1000),';')+2,1000),';')>=0,'inherit','none')",
        "font-size": "24px",
        "color": "#333333",
        "vertical-align": "middle",
        "width": "24px"
      }
    }
  ]
}

Here are the formats side by side:

formatted columns with same display

2
  • Wow, thanks for your effort. I was afraid it had to do with these projected values... So I guess there's no other way to use the text in the lookup column in the form and show the icon values in the list? :-| – Der_Fab Mar 18 at 6:54
  • There's a PnP Sample that illustrates the above that may be worth looking at for anyone with similar requirements: github.com/pnp/List-Formatting/tree/master/column-samples/… – theChrisKent Apr 22 at 17:09
0

Try this:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "display": "block"
  },
  "children": [
    {
      "elmType": "span",
      "forEach": "myIcon in @currentField",
      "txtContent": "",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": "=[$myIcon.lookupValue]"
      }
    }
  ]
}

Before Applying JSON formatting

enter image description here

After applying JSON formatting

enter image description here

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.