{"id":1433,"date":"2019-01-13T18:29:14","date_gmt":"2019-01-13T18:29:14","guid":{"rendered":"http:\/\/www.myfatblog.co.uk\/?p=1433"},"modified":"2019-01-13T18:31:02","modified_gmt":"2019-01-13T18:31:02","slug":"injecting-text-using-custom-column-formats-in-sharepoint-online","status":"publish","type":"post","link":"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/","title":{"rendered":"Injecting text using Custom column formats in SharePoint Online"},"content":{"rendered":"<p>As I said in my post yesterday, I\u2019ve not played with column formats much since they first came out. I liked what you could do and could see where Microsoft were going, but at the time they lacked the power of JSLink display templates and I just didn\u2019t really have anything that I couldn\u2019t do in JSLink. Especially as a lot of my clients were resisting moving to the modern list and library experience. Over the last 6-10 months I\u2019ve seen that reticence drop off and most of my clients are now building in the modern experience. Whereas some time ago they were asking \u201cWhy do we have to use this modern experience?\u201d, I\u2019m now hearing questions like \u201cWhy haven\u2019t they updated document sets to modern yet?\u201d or \u201cWhy do I have to keep switching back to classic to do X?\u201d<\/p>\n<p>I think this shows that modern is certainly gaining more traction and whilst we still can\u2019t (and for some things won\u2019t) be able to do all that we could in a JSLink Display Template in classic, the feature parity between the two experiences is getting ever closer. Just recently I was working with a client on a PMO solution using modern lists and libraries using calculated columns for measuring Risk Priority based on a simple calculation of Risk Likelihood multiplied by Risk Impact. This was score as a simple 5 * 5 matrix, with a threshold of values for priority.<\/p>\n<ul>\n<li>Scores 1-7: Low Priority<\/li>\n<li>Scores 8-14: Medium Priority<\/li>\n<li>Scores 15-21: High Priority<\/li>\n<li>Scores 22-25: Critical Priority<\/li>\n<\/ul>\n<p>This number was used in the PowerBI reports which dictated the need for a numerical value even though the Project Managers were used to seeing the text of Low to Critical in their highlight reports. In order to make this easier for the PMs to consume in their project dashboards, the client wanted the numeric value replacing with a simple banner showing the priority, along with a coloured strip denoting the level.<\/p>\n<p><a href=\"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/image.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" src=\"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/image_thumb.png\" alt=\"image\" width=\"142\" height=\"171\" border=\"0\" \/><\/a><\/p>\n<p>In JSLink, this would have been a fairly simple switch statement to achieve the required HTML, but the Column Format equivalent needed a little bit of thought and some of the new Excel Expressions. With this in mind it\u2019s time to configure a quick custom list for testing, add a little bit of data and setup a static column format to achieve the layout that we wanted.<\/p>\n<p><a href=\"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/image-1.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" src=\"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/image_thumb-1.png\" alt=\"image\" width=\"320\" height=\"245\" border=\"0\" \/><\/a><\/p>\n<pre class=\"brush: js;\">{\r\n    \"$schema\": \"https:\/\/developer.microsoft.com\/json-schemas\/sp\/column-formatting.schema.json\",\r\n    \"elmType\": \"div\",\r\n    \"style\": {\r\n        \"width\":\"100%\",\r\n        \"display\":\"block\"\r\n    },\r\n    \"children\": [\r\n        {\r\n            \"elmType\": \"div\",\r\n            \"style\":{\r\n                \"height\": \"5px\",\r\n                \"background-color\":\"red\",\r\n                \"width\":\"100%\"\r\n            }\r\n        },\r\n        {\r\n        \"elmType\": \"div\",\r\n        \"txtContent\": \"Priority\",\r\n        \"style\": {\r\n            \"text-align\":\"center\",\r\n            \"width\":\"100%\"\r\n            }\r\n        },\r\n        {\r\n            \"elmType\": \"div\",\r\n            \"style\":{\r\n                \"height\": \"5px\",\r\n                \"background-color\":\"red\",\r\n                \"width\":\"100%\"\r\n            }\r\n        }\r\n    ]\r\n  }<\/pre>\n<p>The basic code above set out the initial layout which is basically a DIV wrapping around 3 other DIVs to create the colour header, text value and footer. You\u2019ll notice that the first DIV has had it\u2019s layout style set to Block, something that you wouldn\u2019t normally need to do as the DIV is a block style element, however the custom formatting engine wraps your HTML in it\u2019s own which resets the DIV display styles which you have to then set back yourself.<\/p>\n<p>With the static layout in place, I first wanted to focus on changing the colour of the bars in line with the numeric value and we achieve this using a nested IF statement as the expression for the background colour. The DIV statement for BOTH of our colour header divs is changed to the following:<\/p>\n<pre class=\"brush: js;\">            \"elmType\": \"div\",\r\n            \"style\":{\r\n                \"height\": \"5px\",\r\n                \"background-color\":\"=if(@currentField &lt;= 7,'green',if(@currentField &gt;7 &amp;&amp; @currentField &lt;= 14, 'yellow',if(@currentField &gt;14 &amp;&amp; @currentField &lt;= 21,'#ffc500','red' )))\",\r\n                \"width\":\"100%\"\r\n            }<\/pre>\n<p><a href=\"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/SNAGHTMLab07d13.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"SNAGHTMLab07d13\" src=\"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/SNAGHTMLab07d13_thumb.png\" alt=\"SNAGHTMLab07d13\" width=\"321\" height=\"244\" border=\"0\" \/><\/a><\/p>\n<p>This is one area where it would be nice to have the ability to use a reference variable to save having to recreate the logic in multiple places, especially on more complex layouts as we would have done in a JSLink display template. A word of warning on the troubleshooting here.. if the entire view fails to render when you test this, check that your JSON is valid (you should be editing these in Visual Studio Code which includes validation!).. if just the value fails to render or style, then check your nested IF statements, paying particular attention to the brackets!.<\/p>\n<p>If you\u2019re finding that IF statement hard to read, here\u2019s a quick reminder from yesterdays post\u2026the IF statement looks like this:<\/p>\n<blockquote><p><strong><em>IF (valueToTest &lt;operator&gt; valueToCompare, outputIfTrue, outputIfFalse)<\/em><\/strong><\/p><\/blockquote>\n<p>If we want to nest a second if statement, we replace the <strong><em>outputIfFalse <\/em><\/strong>with the second IF statement and so on\u2026<\/p>\n<blockquote><p><em><strong>IF(valueToTest &lt;operator&gt; valueToCompare, outputIfTrue, <\/strong><span style=\"color: #0000ff;\">IF (valueToTest &lt;operator&gt; valueToCompare, outputIfTrue, outputIfFalse)<\/span><strong>)<\/strong><\/em><\/p><\/blockquote>\n<p>Now we have our colours working, we can turn our attention to the text value, by doing almost the same IF statement, but this time in the txtContent attribute of the middle DIV.<\/p>\n<pre class=\"brush: js;\">        {\r\n        \"elmType\": \"div\",\r\n        \"txtContent\": \"=if(@currentField &lt;= 7,'Low',if(@currentField &gt;7 &amp;&amp; @currentField &lt;= 14, 'Medium',if(@currentField &gt;14 &amp;&amp; @currentField &lt;= 21,'High','Critical' )))\",\r\n        \"style\": {\r\n            \"text-align\":\"center\",\r\n            \"width\":\"100%\"\r\n            }\r\n        },<\/pre>\n<p><a href=\"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/image-2.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" src=\"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/image_thumb-2.png\" alt=\"image\" width=\"318\" height=\"224\" border=\"0\" \/><\/a><\/p>\n<p>And there we go, a quick visualisation with text and colour indicators based against an arbitrary score which would be difficult for users to remember the ranges for. Hopefully that\u2019s proved useful and I\u2019ll get this added to the<a href=\"https:\/\/github.com\/SharePoint\/sp-dev-list-formatting\"> PNP samples in github<\/a> soon, in the meantime you can <a href=\"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/Number-ToTextAndColour.json\">view the completed JSON file<\/a> here.<\/p>\n<p>Paul.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>As I said in my post yesterday, I\u2019ve not played with column formats much since they first came out. I liked what you could do and could see where Microsoft were going, but at the time they lacked the power of JSLink display templates and I just didn\u2019t really have anything that I couldn\u2019t do &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/\">Continue reading<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[257,256,96,126,258,16,251,139],"tags":[259],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Injecting text using Custom column formats in SharePoint Online - Blog of an overweight SharePoint addict<\/title>\n<meta name=\"description\" content=\"Custom column formats to make numerical range values easier to consume is a great example of using the new modern format options to inject colour and text.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Injecting text using Custom column formats in SharePoint Online - Blog of an overweight SharePoint addict\" \/>\n<meta property=\"og:description\" content=\"Custom column formats to make numerical range values easier to consume is a great example of using the new modern format options to inject colour and text.\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog of an overweight SharePoint addict\" \/>\n<meta property=\"article:published_time\" content=\"2019-01-13T18:29:14+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-01-13T18:31:02+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/image_thumb.png\" \/>\n<meta name=\"author\" content=\"Cimares\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@cimares\" \/>\n<meta name=\"twitter:site\" content=\"@cimares\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Cimares\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/\",\"url\":\"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/\",\"name\":\"Injecting text using Custom column formats in SharePoint Online - Blog of an overweight SharePoint addict\",\"isPartOf\":{\"@id\":\"http:\/\/www.myfatblog.co.uk\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/#primaryimage\"},\"image\":{\"@id\":\"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/image_thumb.png\",\"datePublished\":\"2019-01-13T18:29:14+00:00\",\"dateModified\":\"2019-01-13T18:31:02+00:00\",\"author\":{\"@id\":\"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/55ae8f6885bb5b8390dad001f3da83c6\"},\"description\":\"Custom column formats to make numerical range values easier to consume is a great example of using the new modern format options to inject colour and text.\",\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/#primaryimage\",\"url\":\"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/image_thumb.png\",\"contentUrl\":\"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/image_thumb.png\",\"width\":142,\"height\":171},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/www.myfatblog.co.uk\/#website\",\"url\":\"http:\/\/www.myfatblog.co.uk\/\",\"name\":\"Blog of an overweight SharePoint addict\",\"description\":\"The rantings of a (not so) food obsessed IT consultant!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/www.myfatblog.co.uk\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/55ae8f6885bb5b8390dad001f3da83c6\",\"name\":\"Cimares\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/About_D057\/TopOfTheWorld.jpg\",\"contentUrl\":\"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/About_D057\/TopOfTheWorld.jpg\",\"caption\":\"Cimares\"},\"sameAs\":[\"http:\/\/www.myfatblog.co.uk\"],\"url\":\"http:\/\/www.myfatblog.co.uk\/index.php\/author\/reginald\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Injecting text using Custom column formats in SharePoint Online - Blog of an overweight SharePoint addict","description":"Custom column formats to make numerical range values easier to consume is a great example of using the new modern format options to inject colour and text.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/","og_locale":"en_US","og_type":"article","og_title":"Injecting text using Custom column formats in SharePoint Online - Blog of an overweight SharePoint addict","og_description":"Custom column formats to make numerical range values easier to consume is a great example of using the new modern format options to inject colour and text.","og_url":"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/","og_site_name":"Blog of an overweight SharePoint addict","article_published_time":"2019-01-13T18:29:14+00:00","article_modified_time":"2019-01-13T18:31:02+00:00","og_image":[{"url":"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/image_thumb.png"}],"author":"Cimares","twitter_card":"summary_large_image","twitter_creator":"@cimares","twitter_site":"@cimares","twitter_misc":{"Written by":"Cimares","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/","url":"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/","name":"Injecting text using Custom column formats in SharePoint Online - Blog of an overweight SharePoint addict","isPartOf":{"@id":"http:\/\/www.myfatblog.co.uk\/#website"},"primaryImageOfPage":{"@id":"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/#primaryimage"},"image":{"@id":"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/#primaryimage"},"thumbnailUrl":"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/image_thumb.png","datePublished":"2019-01-13T18:29:14+00:00","dateModified":"2019-01-13T18:31:02+00:00","author":{"@id":"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/55ae8f6885bb5b8390dad001f3da83c6"},"description":"Custom column formats to make numerical range values easier to consume is a great example of using the new modern format options to inject colour and text.","inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/injecting-text-using-custom-column-formats-in-sharepoint-online\/#primaryimage","url":"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/image_thumb.png","contentUrl":"http:\/\/www.myfatblog.co.uk\/wp-content\/uploads\/2019\/01\/image_thumb.png","width":142,"height":171},{"@type":"WebSite","@id":"http:\/\/www.myfatblog.co.uk\/#website","url":"http:\/\/www.myfatblog.co.uk\/","name":"Blog of an overweight SharePoint addict","description":"The rantings of a (not so) food obsessed IT consultant!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.myfatblog.co.uk\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/55ae8f6885bb5b8390dad001f3da83c6","name":"Cimares","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/image\/","url":"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/About_D057\/TopOfTheWorld.jpg","contentUrl":"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/About_D057\/TopOfTheWorld.jpg","caption":"Cimares"},"sameAs":["http:\/\/www.myfatblog.co.uk"],"url":"http:\/\/www.myfatblog.co.uk\/index.php\/author\/reginald\/"}]}},"_links":{"self":[{"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/posts\/1433"}],"collection":[{"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/comments?post=1433"}],"version-history":[{"count":4,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/posts\/1433\/revisions"}],"predecessor-version":[{"id":1437,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/posts\/1433\/revisions\/1437"}],"wp:attachment":[{"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/media?parent=1433"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/categories?post=1433"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/tags?post=1433"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}