Column formatting is a nice way to customize your modern lists views. But it requires that the columns used in the JSON are referenced in the view.

In this workaround you need a calculated column present in the view. It doesn’t need to be the column you are formating.

For this  example, create a column ‘Flow Status’ and a column ‘Color’. The purpose is to show the color in the column status.

Go to the formating panel of column Flow Status

and paste the folowing JSON

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"style": {
"border-top": "none",
"background-color": "=[$Color]"
},
"children": [
{
"elmType": "span",
"style": {
"font-weight": "bold",
"color": "black"
},
"txtContent": "@currentField"
}
]
}

If the column color is present in the view, the formating is correct

But, if you hide the column, it not shows the color correctly

So the trick is to make a reference to the Color column without showing it in the view. To do that, the easiest way is to make that reference in the calculated field with a condition that never happens.

The column Flow Status calculated field has a simple formula

=[_Hidden_FlowStatus]

what we need to include is a condition that never happens. For example, put in the formula the condition if 1 equal to 2 then show Color else, show Flow status.

IF(1=2;color;_Hidden_FlowStatus)

This way, the view have reference to the column color and show the format correctly


14 Comments

Erwin Koens · October 22, 2019 at 8:43 am

Hi. Cannot get this working. What is the name of the calculated column? Is the calculated column displayed?

Wim · November 7, 2019 at 8:42 am

Hi, Can’t get this working. Only works when you load loading the calculated view from a view where all required columns are present. Loading the trimmed view with only calculated column after hard refresh does not give the values from the referenced columns.

    Valter Lima · November 7, 2019 at 10:46 am

    Hi,
    You need to put the required columns in some of the calculated field.
    In my example, the required field is Color, and i put it in a calculated field in a condition that never happens:

    =If(1=2;Color; _Hidden_FlowStatus)

    did it helps? If not, please post your example whit the required and calculated fields.

Joseph · December 5, 2019 at 10:06 pm

I also could not get this solution to work. The workaround I ended up using was not hiding the columns I didn’t want to show, but instead using JSON formatting of those columns to make them appear blank.

    Valter Lima · December 6, 2019 at 11:59 am

    Its a way of getting things done, but did you try to put that collumn in a calculated field? That way you have the needed reference to the collumn and if you put it in a condicion that never happens it doesn´t appear in the view, for example 1 = 2. then you use this calculate field to show a collumn that you already need to show in the view.

Anthony White · April 29, 2020 at 1:42 pm

Like others posting here, I don’t think this works…

Tina · May 5, 2020 at 4:16 pm

I have a calculated field [Last Updated] that I am formatting. Formula: =TEXT(LastUpdatedDate,”d-mmm-yyyy”)

In my view, I am formatting the [Last Updated] column with:
{
“$schema”: “https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json”,
“elmType”: “div”,
“attributes”: {
“class”: “=if(toString([$LastUpdatedDate]) == ”, ”, if([$LastUpdatedDate] <= (@now +2592000000), 'sp-field-severity–warning'))"
},
"children": [
{
"elmType": "span",
"style": {
"display": "inline-block",
"padding": "0 4px"
}
},
{
"elmType": "span",
"txtContent": "@currentField"
}
]
}

This only works if I am showing [LastUpdatedDate] in the view. The field is there in the formula, but it doesn't work. I even tried the IF(1=2…). Any suggestions?

Detlef · February 2, 2021 at 7:27 pm

This is an exceptionally clever workaround for the modern pages limitation on column formatting conditionals. Readers may be getting stuck just picturing what is being rearranged.

In a nutshell – take ANY of your existing list fields and duplicate it by making a calculated copy. Example: Name and Name_Copy, or State and State_Copy. The field that you choose has to be one that you want to display. It doesn’t have to be one that you plan to format, but it is convenient.

The calculation for the calculated field [Name_Copy] starts as just [Name]
The IF statement is literally only there to mention -another- field [Color], that we never want to display at all. We just need it referenced so that it loads with the view.

I flipped the IF statement around a little, just so that it would be a little clearer in my own mind:
[Name_Copy] —> IF(true,[Name],[Color])

[Color] will never be used, since true will never equal false.

Remove [Name] from your list view and replace it with [Name_Copy]. Nothing should have changed, but we can now make references to [Color] in other conditional statement.

    Valter Lima · March 16, 2021 at 5:55 pm

    Thanks for the feedback! You resumed very well!!

Luis Montoya · February 10, 2021 at 11:07 pm

this workaround works no more, it seems MS has updated something in sharepoint and referenced additional columns values are not brought to the JSON formatting

    Valter Lima · March 16, 2021 at 5:52 pm

    I just checked in my tenants and is still working.

Jesper Simonsen · June 10, 2021 at 9:24 am

My 10 cent on this smart work around is to put the fields you want to use for column formatting in the condition part of the IF statement, this way it work more reliably and you can add as many fields in there as you want.
So if you need field1, field2 and field3 you do something like this:
=IF([field1]=”test”&[field2]=”test”&[field3]=”test”&1=2,[fieldNotDisplayed],[fieldDisplayed])

[Tips & Tricks] View-Formatting With a Column not present in the view – DevScope Ninjas · February 21, 2020 at 4:07 pm

[…] workaround used in this post is the same used in the Column-Formatting With a Column not present in the view, but with a different example that can be more easily […]

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: