Forum Discussion
Column formatting using JSON
Hi
I'm trying to format a column based on a column containing certain text. This column can have multiple values starting with: "On Hold". Example: "On Hold - Awaiting customer" or "On Hold - Awaiting Vendor". We may add more in the future, and I'm sure I would forget to update the column format.
Is there a like operator, so that I can do:
{
"operator": "<like operator>",
"operands": [
"[$Status]",
"On Hold*"
]
}
Thanks.
My answer is late, but may be it could help others looking for similar solution.
We can use indexOf operator to check if string contains a particular substring.
Eg.
"txtContent": "=if(indexOf([$Status], 'On Hold') >= 0, 'On Hold', 'Released')"Or something like this:
"background-color": "=if(indexOf([$Status], 'On Hold') >= 0, 'Yellow', 'Green')"
Check the syntax once again if it is not working.
For more details:
https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting
Hi,
you can create a new calculated column [ColumnStatus7Characters] using a formula like =LEFT([ColumnStatus], 7) and than you can use the = operator in the JSON for column [Status], but checking column [ColumnStatus7Characters] for "On Hold". Not nice but a Workaround.
Regards
- Hi Alan,
I haven't played that much with column formatting so not sure, but give this solution a try as it will massively help you
https://github.com/SharePoint/sp-dev-solutions/tree/master/solutions/ColumnFormatter
Joel
Depending on how your data is structured, you may want to restructure your data so that you can use the "Choice" column type to assign metadata to each record. This will simplify your overall implementation. It will also prevent user error and variations within "Single Line of Text" data entries. i.e. "On Hold" vs "OnHold" vs "On-Hold".
More recently, there is also a no-code solution for Choice, Date/Time, and Boolean column types that does not require knowledge of complex JSON to assign background colors.
No Code Column Formatting – SharePoint Online
This approach assumes 1 choice option for each variation between "On Hold - Awaiting customer" or "On Hold - Awaiting Vendor" respectively. You can define up to 24 color options at the moment.
