Column formatting forEach property does not support spaces

[jensotto]

Target SharePoint environment

SharePoint Online

What SharePoint development model, framework, SDK or API is this about?

Declarative list formatting

Developer environment

Windows

What browser(s) / client(s) have you tested

• [ ] 💥 Internet Explorer
• [X] 💥 Microsoft Edge
• [ ] 💥 Google Chrome
• [ ] 💥 FireFox
• [ ] 💥 Safari
• [ ] mobile (iOS/iPadOS)
• [ ] mobile (Android)
• [ ] not applicable
• [ ] other (enter in the "Additional environment details" area below)

Additional environment details

• Edge 112.0.1722.39

Describe the bug / error

Its not possible to use operators in the forEach property of column formatting. The following definition fails with the error message:Failure: split(replaceAll(@currentField,'; is not an array, array expected in the expression value in split(replaceAll(@currentField,'; ',';'),';')

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "debugMode": true,
  "elmType": "div",
  "children": [
    {
      "elmType": "div",
      "forEach": "value in split(replaceAll(@currentField,'; ',';'),';')",
      "txtContent": "='*'+[$value]"
    }
  ]
}

Content in the field is a semicolon separated list: 1; 2; 3; 4

Spaces can be used in other properties like for example txtContent.

Steps to reproduce

1. Add a new list to SharePoint
2. Add the content "1; 2; 3; 4" to the title field (note the spaces after each semicolon)
3. Add the column formatting from the bug description above.
4. Rendering fails.
5. Remove the space in the second replaceAll argument.
6. Rendering works, but there is a space in front of every item except the first one.

Expected behavior

It should be possible to use spaces in operators in the forEach porperty

[ghost]

Thank you for reporting this issue. We will be triaging your incoming issue as soon as possible.

[waholayo]

Please add support of line break (\n, \r) and tab (\t) characters to the operator of forEach.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "debugMode": true,
  "elmType": "div",
  "__txtContent": "=replaceAll(@currentField,'\n',';')",
  "__txtContent": "=split(replaceAll(@currentField,'\n',';'),';')",
  "children": [
    {
      "elmType": "div",
      "forEach": "__INTERNAL__ in split(replaceAll(@currentField,'\n',';'),';')",
      "txtContent": "[$__INTERNAL__]"
    }
  ]
}

@currentField is a multiple line of text column, where each line represents a list item, e.g.:

item 1
item 2
item 3
item 4

The above code also gives error: Failure: split(replaceAll(@currentField,' is not an array, array expected in the expression __INTERNAL__ in split(replaceAll(@currentField,' ',';'),';')

[djbrum]

we have the same issue

[adriangoszczynski]

I got info from Microsoft CSA, that foreach expect array and it can be passed by expression, but split and other methods they are not actually expressions but functions, that is why it is not working.

[waholayo]

There is a workaround if \s, \n, \r, \t is needed in the forEach expression:

1. Create a Calculated Column [varSpace], [varCRLF], [varTab] with formula

   =" "

   ="
   "

   ="  "

2. Then use these Calculated Columns as a variable in the List or View formatting JSON:

   {
   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
   "elmType": "div",
   "children": [
   {
     "elmType": "div",
     "forEach": "__INTERNAL__ in split(@currentField,[$varSpace])",
     "txtContent": "[$__INTERNAL__]"
   }
   ]
   }

   {
   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
   "elmType": "div",
   "children": [
   {
     "elmType": "div",
     "forEach": "__INTERNAL__ in split(@currentField,substring([$varCRLF],1,2)",
     "txtContent": "[$__INTERNAL__]"
   }
   ]
   }

[jensotto]

I got info from Microsoft CSA, that foreach expect array and it can be passed by expression, but split and other methods they are not actually expressions but functions, that is why it is not working.

This sounds like a made up diversion. Functions (and more) are used in expressions.

The expression in my example works just fine as long as there are no spaces in it (the space can be anywhere in the expression). Split retuns an array, and an expression using Split will therefore satisfy Microsoft's documented requirement for using foreach.

I would not take Microsoft's claim in this matter as a valid reason for this to not work.

[adriangoszczynski]

He explained, that expressions are processed based runtime and functions calculate something during processing, this is difference. That is why forEach allows expressions only. More about expressions see here https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/formatting-syntax-reference#expressions.

And he also said, that the fact it works without spaces it is rather coincidance than prove that something is wrong with processing.

This topic was reported to the MS Support and they (supposedly a product group) didn't want to take it.

[jensotto]

He explained, that expressions are processed based runtime and functions calculate something during processing, this is difference. That is why forEach allows expressions only. More about expressions see here https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/formatting-syntax-reference#expressions.

And he also said, that the fact it works without spaces it is rather coincidance than prove that something is wrong with processing.

This topic was reported to the MS Support and they (supposedly a product group) didn't want to take it.

What are expressions If they are not made up by functions? Microsoft states in the documentation that you refer too that split and other operators are supported in foreach as operators are the building blocks of expressions. My theory is that the value of the forEach property internally is processed by splitting on spaces. First section is the iterator variable, second is the mandatory word 'in', and the third is the expression or fieldname. This fits with the issue reported where if the expression contains a space it fails, because only part of the expression is considered.

Do you have an example of a "valid" expression according to Microsoft?

[adriangoszczynski]

I didn't find statement that forEach's value can be split or other function but can be multi-value field or an expression that returns an array. They states that function and expression are not the same and split and other operators are functions not expressions.

I was only directed to the article above where you will find examples of expressions.

[jensotto]

The article does not mention the word function one single time. I never said functions and expression are the same. Expressions contains functions. Split is a function/operator, and is used in an expression. Split returns an array. Therefore (in my example) the expression returns an array. This then satisfies the documented requirement of an forEach property value since the expression returns a value. Unless I have missed something I'm pretty sure Microsoft's reply to you is wrong.

In addition the workaround provided by @waholayo further strengthen my case as it removes the spaces from the expression written in the forEach property value. It proves that split is working just fine in the expression as long as it does not contain any spaces.

@adriangoszczynski Please provide an expression that you believe is valid for use in forEach. I'm pretty sure that if you can write that expression functionally equivalent with and without a space, then it will fail when you use the one with a space in it.

[adriangoszczynski]

Here https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/formatting-advanced#value-field-values-in-actioninput you will find a paragraph talking about multivalued fields and that there are functions that can be used there, there is no mention of expressions like for other fields.

The expression you can use is a condition that passes the value (i.e. array) of the multivalued field. Even if there are spaces in the field, the expression will work.

[jensotto]

That relates to customRowAction, actionInput, and multi value fields. None of this is relevant for this issue, as there are no multi value fields, customRowActions, nor actionInputs involved.

[adriangoszczynski]

I meant this section

Multi-Choice and Multi-Person:

Multi value fields are special, as they need an array value to save multiple values. appendTo, removeFrom, and replace, three functions that can operate on multivalue fields. appendTo([$MultiChoiceField], 'MyValue') removeFrom([$MultiUserField], @me): removes all occurrences that match the second parameter replace([$MultiChoiceField], 'Choice 1', 'Choice 3'): replaces all occurrences of second parameter with third.

there is no "expression" mentioned as at sections above it.

Bear in mind that in my opinion it doesn't make sense what MS said but I cannot do more with this. They base their arguments on the above so it can somehow be interpreted as a logical justification and therefore reject the application.

[jensotto]

I considered that section too and it is not relevant to the issue.

[XueSheng-GIT]

Actually, I also don't get the point about the discussion expression vs. functions. Aren't spaces valid in expressions? Aren't spaces the issue we have here?

[jensotto]

Yes, the issue is spaces. Unfortunately I've had too many personal experiences where Microsoft support references irrelevant documentation, or makes invalid arguments about issues just to avoid escalating to the product group. I've had far too many cases with Microsoft where I would have to make long arguments, and heated discussions with support engineers before they where willing to forward it to the product group. So far those that reached the PG all has been fixed, proving that Microsoft support has some serious issues working with these cases. I even had a chat with @VesaJuvonen at a recent conference where he seemingly knew that there are issues getting proper support for at least extension functionality like column formatting and view formatting. MS support seems to disregard such cases as being a developer issue, and not a product issue, and tries to redirect the caller to development communities when in reality it is product issues.

[XueSheng-GIT]

There is a workaround if \s, \n, \r, \t is needed in the forEach expression:

1. Create a Calculated Column [varSpace], [varCRLF], [varTab] with formula

=" "

Just tested varSpace and noticed that it doesn't work as expected. Once the calculated column is saved, it does work a couple of minutes. But after some time it replaces the space with an empty character. Maybe some magic in the background!? To work around that I'm now trying to use CHAR instead.

=CHAR(32)

Another aspect is that I need to add additional columns to the library for this workaround (in the past I was able to use calculated columns without adding them the each view but since a couple of months this doesn't seems to work anymore... could be another issue!?).

[XueSheng-GIT]

Just tested varSpace and noticed that it doesn't work as expected. Once the calculated column is saved, it does work a couple of minutes. But after some time it replaces the space with an empty character. Maybe some magic in the background!? To work around that I'm now trying to use CHAR instead.

=CHAR(32)

Another aspect is that I need to add additional columns to the library for this workaround (in the past I was able to use calculated columns without adding them the each view but since a couple of months this doesn't seems to work anymore... could be another issue!?).

After some more testing, I realized, that CHAR(32) behaves the same as the space itself. Once the calculated column is saved, it is working. But as soon as any field is updated, it stops working. SharePoint seems to do some magic in the background. Finally, not a workaround.

@waholayo any other idea? Was is working reliably for you?

[waholayo]

@XueSheng-GIT, the workaround has been working for me reliably, however I do add these Calculated Columns to the Views, as I was not aware that it is possible to reference Calculated Columns that are not visible in the View.

In this MS discussion, it mentions a way, but I need some time to digest this technique that I haven't yet learned.

I have also noticed that browser caching could kick in more frequently than I would like it to, so I am just used to do shift+F5. So between browser caching, new browser versions, and data center syncs/cloud CDN in general*, my applications can tolerant a delay of few hours or until the next day for the List Formatting JSON update to stabilize, if it is a bad cloudy day.

BTW, I really like your idea of using CHAR(). I will switch my wonky formulas from

="
"

substring([$varCRLF],1,2) to the neater CHAR(10) [$varLF]. :)

[XueSheng-GIT]

When using only a space for the calculated column, it's like using unsupported systemfields (e.g. ID). After saving, it works and then it stops working at some point.

Too make it even more complicated, I solved it now with the following calculated column varSpace:

="^ ^"

To get only the space, i need to do another replaceAll([$varSpace],'^',''). I know, not the nicest way, but this workaround seems to work.

[waholayo]

@XueSheng-GIT I have now seen inconsistency of single space char =CHAR(32) and =" "

It seems if the List Item is modified after the creation of the [varSpace] or [varChar32], then the single space char will disappear in the rendering of Column Formatting; but adding another char into the Calculated formula somehow will keep this string from being eaten up, and you will need the extra string formula substring([$varSpace],1,2) or replaceAll([$varSpace],'^','').

If Microsoft could just support \s \r \n \t in the forEach expression in the first place, we won't have to spend time on these workarounds for workarounds...