Feb 01 2018 03:59 AM
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.
Feb 01 2018 04:49 AM
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
Feb 01 2018 10:23 AM
Feb 04 2018 09:53 AM
Feb 05 2018 06:12 AM
I was trying for a quick solution using the new Column Formatting feature. Not really wanting to write code unless I have to.
Alan
Feb 05 2018 07:21 AM
Hi @Alan Trafford,
That project is a web part that gives you a friendly interface to build the JSON for column formatting. It also contains a wizard :)
Give it a try :)
Joel
Feb 05 2018 07:39 AM
I saw a demo of it on PnP Monthly Community Call - January 2018. It looks really good. That said, I'm still trying to avoid coding unless I have to. I think I'll be using @Tomislav Karafilov workaround, as it's easy to apply.
Alan
Feb 05 2018 06:25 PM
Hey,
Really good write up of what you can achieve with Column Formatting here: https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting
Doesn't look like there is a "like" type operator to do partial string matching / substring matching, so I am guessing you would have to either:
- specify each of the possible "On Hold - <hold reason>" values in the JSON
- in your list have a field for the value "On Hold" and a separate field for the reason
Good luck!
Feb 06 2018 12:54 AM
That's what I did in the end. The owner of the list did not want a new columns added, so I could not use @Tomislav Karafilov solution. In case it will help others, here's my final code:
{ "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json", "debugMode": true, "elmType": "div", "attributes": { "class": { "operator": "?", "operands": [ { "operator": "&&", "operands": [ { "operator": "<=", "operands": [ "[$Finish]", "@now" ] }, { "operator": "||", "operands": [ { "operator": "==", "operands": [ "[$Status]", "Status1" ] }, { "operator": "==", "operands": [ "[$Status]", "Status2" ] }, { "operator": "==", "operands": [ "[$Status]", "On Hold - 1" ] }, { "operator": "==", "operands": [ "[$Status]", "On Hold - 2" ] }, { "operator": "==", "operands": [ "[$Status]", "On Hold - 3" ] }, { "operator": "==", "operands": [ "[$Status]", "On Hold" ] } ] } ] }, "sp-field-severity--blocked", "" ] } }, "children": [ { "elmType": "span", "style": { "display": "inline-block", "padding": "0 4px" }, "attributes": { "iconName": { "operator": "?", "operands": [ { "operator": "&&", "operands": [ { "operator": "<=", "operands": [ "[$Finish]", "@now" ] }, { "operator": "||", "operands": [ { "operator": "==", "operands": [ "[$Status]", "Status1" ] }, { "operator": "==", "operands": [ "[$Status]", "Status2" ] }, { "operator": "==", "operands": [ "[$Status]", "On Hold - 1" ] }, { "operator": "==", "operands": [ "[$Status]", "On Hold - 2" ] }, { "operator": "==", "operands": [ "[$Status]", "On Hold - 3" ] }, { "operator": "==", "operands": [ "[$Status]", "On Hold" ] } ] } ] }, "Error", "" ] } } }, { "elmType": "span", "txtContent": "@currentField" } ] }
Jan 27 2019 01:40 PM
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.
Mar 07 2019 07:31 PM - edited Mar 07 2019 07:47 PM
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