{"id":32430,"date":"2022-08-24T15:27:29","date_gmt":"2022-08-24T09:57:29","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=32430"},"modified":"2023-04-12T12:35:40","modified_gmt":"2023-04-12T07:05:40","slug":"adding-the-power-fx-formula-column-preview-into-dataverse","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2022\/08\/adding-the-power-fx-formula-column-preview-into-dataverse\/","title":{"rendered":"Adding the Power Fx Formula column (PREVIEW) into Dataverse"},"content":{"rendered":"<p>Recently when I was adding the new column in Microsoft Dataverse from the <strong>PREVIEW <\/strong>maker portal (<a href=\"https:\/\/make.preview.powerapps.com\/\" target=\"_blank\" rel=\"noopener\">https:\/\/make.preview.powerapps.com\/<\/a>) I was amazed by looking at the new option in the Datatype available named \u201c<strong>Formula<\/strong>\u201d.<\/p>\n<p>In this blog, we will focus on the introduction and related use cases where this \u201cFormula\u201d datatype can be useful.<\/p>\n<p><strong>Formula Column Features:<\/strong><\/p>\n<ul>\n<li>Formula columns use the Power Fx syntax similar to Office Excel. <a href=\"https:\/\/docs.microsoft.com\/en-us\/power-platform\/power-fx\/overview\" target=\"_blank\" rel=\"noopener\">Power Fx<\/a> is the low code language that is being used across the Microsoft Platform.<\/li>\n<li>Formula column expands the ability of existing calculated and rollup columns it adds the value in expression by performing the arithmetic calculation in real-time.<\/li>\n<li>The formula expression can be made available with Today ()\u00a0and\u00a0Now ()\u00a0functions for the first time.<\/li>\n<li>Many supported <a href=\"https:\/\/docs.microsoft.com\/en-us\/power-apps\/maker\/data-platform\/formula-columns#function-types\" target=\"_blank\" rel=\"noopener\">functions<\/a> are available for inbuilt arithmetic operations.<\/li>\n<li>As you enter the formula, Intellisense helps you with recommendations for formula, syntax, and errors.<\/li>\n<li>Making it easy to see and edit the column&#8217;s formula directly inline without needing to save or open another window as we do for calculated and rollup fields where another window opens up for entering the calculations.<\/li>\n<\/ul>\n<p><strong>Adding the \u201cFormula\u201d column in Dataverse:<\/strong><\/p>\n<p><strong>Pre-requisite:<\/strong><\/p>\n<ul>\n<li>You must navigate to the <strong>PREVIEW <\/strong>maker portal (<a href=\"https:\/\/make.preview.powerapps.com\/\" target=\"_blank\" rel=\"noopener\">https:\/\/make.preview.powerapps.com\/<\/a>) and under the appropriate environment try to add the column by using the steps explained in the below use cases.<\/li>\n<\/ul>\n<p><strong>Use Case1: <\/strong><\/p>\n<ul>\n<li>Suppose there is a requirement to compare the \u201cSprint Review Meeting Date\u201d (Time Zone Independent date) with today\u2019s date and if the difference between these two days is less than or equal to 7, then set the value in \u201cSprint Retrospective Decision\u201d field as either \u201cSprint Goal achieved\u201d or \u201cSprint Goal not achieved\u201d.<\/li>\n<li>Earlier to achieve this requirement we would have had to go with custom logic development (e.g. Power Automate etc.) but with the introduction of the \u201cFormula\u201d column in Dataverse, it is now easy to implement this logic by using appropriate functions.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/bit.ly\/41qgZPf\" target=\"_blank\" rel=\"noopener\"><img decoding=\"async\" class=\"alignnone  wp-image-34428\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/Map-My-Relationship-1.gif\" alt=\"Map My Relationship\" width=\"1000\" height=\"250\" \/><\/a><\/p>\n<p><strong>Implementation:<\/strong><\/p>\n<ul>\n<li>You can implement the above requirement using <a href=\"https:\/\/docs.microsoft.com\/en-us\/power-platform\/power-fx\/reference\/function-now-today-istoday\" target=\"_blank\" rel=\"noopener\"><strong>UTCTODAY<\/strong><\/a> and <a href=\"https:\/\/docs.microsoft.com\/en-us\/power-platform\/power-fx\/reference\/function-dateadd-datediff\" target=\"_blank\" rel=\"noopener\"><strong>DateDiff<\/strong><\/a> inbuilt functions.<\/li>\n<li>As you can see in the following screenshot, you can use the formula datatype to create a formula column that computes arithmetic operations by incorporating the inbuilt functions.<\/li>\n<\/ul>\n<p><strong>Steps:<\/strong><\/p>\n<p>1. Select the table where you want to add the column. For this demonstration, I have used the \u201cSprint Evaluation\u201d custom table.<\/p>\n<p>2. Click on \u201c+ New Column\u201d and in the Add new column pane, select \u201c<strong>Formula<\/strong>\u201d\u00a0as the\u00a0data type. As shown below I have added the \u201cSprint Retrospective Decision\u201d column:<\/p>\n<p><img decoding=\"async\" class=\"alignnone size-full wp-image-32437\" style=\"border: 1px solid #0a0a0a; padding: 1px; margin: 1px;\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/1Power-Fx-Formula-column.jpeg\" alt=\"Power Fx Formula column\" width=\"1155\" height=\"827\" srcset=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/1Power-Fx-Formula-column.jpeg 1155w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/1Power-Fx-Formula-column-300x215.jpeg 300w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/1Power-Fx-Formula-column-1024x733.jpeg 1024w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/1Power-Fx-Formula-column-768x550.jpeg 768w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/1Power-Fx-Formula-column-660x473.jpeg 660w\" sizes=\"(max-width: 1155px) 100vw, 1155px\" \/><\/p>\n<p>3. Upon selecting the \u201cFormula\u201d datatype, the expression box opens up where you can enter the desired formula.<\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-32436 aligncenter\" style=\"border: 1px solid #0a0a0a; padding: 1px; margin: 1px;\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/2Power-Fx-Formula-column.jpeg\" alt=\"Power Fx Formula column\" width=\"649\" height=\"785\" srcset=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/2Power-Fx-Formula-column.jpeg 649w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/2Power-Fx-Formula-column-248x300.jpeg 248w\" sizes=\"(max-width: 649px) 100vw, 649px\" \/><\/p>\n<p>4. For the demonstration, implemented formula expression is as shown:<\/p>\n<p><img decoding=\"async\" class=\"alignnone size-full wp-image-32435\" style=\"border: 1px solid #0a0a0a; padding: 1px; margin: 1px;\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/3Power-Fx-Formula-column.jpeg\" alt=\"Power Fx Formula column\" width=\"585\" height=\"795\" srcset=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/3Power-Fx-Formula-column.jpeg 585w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/3Power-Fx-Formula-column-221x300.jpeg 221w\" sizes=\"(max-width: 585px) 100vw, 585px\" \/><\/p>\n<p><strong>Formula Expression used:<\/strong><\/p>\n<ul>\n<li><em>If(DateDiff(&#8216;Sprint Review Meeting Date&#8217;,UTCToday())&lt;=7, &#8220;Sprint Goal achieved&#8221;,&#8221;Sprint Goal not achieved&#8221;)<\/em><\/li>\n<\/ul>\n<p><strong>UI Demonstration:<\/strong><\/p>\n<ul>\n<li>While creating \u201cSprint Evaluation\u201d record or on change of \u201cSprint Review Meeting Date\u201d the value in \u201cSprint Review Meeting Date\u201d is compared with the \u201ctoday\u2019s date\u201d. As difference between these dates is less than 7, \u201cSprint Retrospective Decision\u201d is set as \u201cSprint Goal achieved\u201d as shown in the below screenshot of the model-driven app:<\/li>\n<\/ul>\n<p><img decoding=\"async\" class=\"alignnone size-full wp-image-32434\" style=\"border: 1px solid #0a0a0a; padding: 1px; margin: 1px;\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/4Power-Fx-Formula-column.jpeg\" alt=\"Power Fx Formula column\" width=\"858\" height=\"569\" srcset=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/4Power-Fx-Formula-column.jpeg 858w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/4Power-Fx-Formula-column-300x199.jpeg 300w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/4Power-Fx-Formula-column-768x509.jpeg 768w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/4Power-Fx-Formula-column-660x438.jpeg 660w\" sizes=\"(max-width: 858px) 100vw, 858px\" \/><\/p>\n<p><strong>Use Case 2: <\/strong><\/p>\n<ul>\n<li>Suppose there is a requirement to check the value entered in the \u201cSprint Rating\u201d. Thus, depending upon the value entered, set \u201cSprint Overall Feedback\u201d as tabulated below:<\/li>\n<\/ul>\n<p><img decoding=\"async\" class=\"size-full wp-image-32438 aligncenter\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/6Power-Fx-formula.png\" alt=\"\" width=\"333\" height=\"127\" srcset=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/6Power-Fx-formula.png 333w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/6Power-Fx-formula-300x114.png 300w\" sizes=\"(max-width: 333px) 100vw, 333px\" \/><\/p>\n<ul>\n<li>Earlier to achieve this requirement we had to go with custom logic development (e.g. Power Automate etc.) but with the introduction of the \u201cFormula\u201d column in Dataverse, it is now easy to implement this logic by using appropriate inbuilt functions.<\/li>\n<\/ul>\n<p><strong>Implementation:<\/strong><\/p>\n<ul>\n<li>You can implement the above requirement using <a href=\"https:\/\/docs.microsoft.com\/en-us\/power-platform\/power-fx\/reference\/function-if\" target=\"_blank\" rel=\"noopener\"><strong>Switch<\/strong><\/a> inbuilt functions.<\/li>\n<li>As you can see in the following screenshot, you can use the formula datatype to create a formula column that computes arithmetic operations by incorporating the function.<\/li>\n<\/ul>\n<p><strong>Steps:<\/strong><\/p>\n<p>1. Select the table where you want to add the column. For this demonstration, I have used the \u201cSprint Evaluation\u201d custom table.<\/p>\n<p>2. Click on \u201c+ New Column\u201d and in the Add new column pane, select \u201c<strong>Formula<\/strong>\u201d\u00a0as the\u00a0data type. As shown below I have added the \u201cSprint Overall Feedback\u201d column:<\/p>\n<p><img decoding=\"async\" class=\"alignnone size-full wp-image-32433\" style=\"border: 1px solid #0a0a0a; padding: 1px; margin: 1px;\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/7Power-Fx-Formula-column.jpeg\" alt=\"Power Fx Formula column\" width=\"559\" height=\"787\" srcset=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/7Power-Fx-Formula-column.jpeg 559w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/7Power-Fx-Formula-column-213x300.jpeg 213w\" sizes=\"(max-width: 559px) 100vw, 559px\" \/><\/p>\n<p><strong>Formula Expression used:<\/strong><\/p>\n<ul>\n<li><em>Switch(&#8216;Sprint Rating&#8217;,0,&#8221;Very Poor&#8221;,1,&#8221;Poor&#8221;,2,&#8221;Average&#8221;,3,&#8221;Good&#8221;,4, &#8220;Very Good&#8221;,5, &#8220;Excellent&#8221;)<\/em><\/li>\n<\/ul>\n<p><strong>UI Demonstration:<\/strong><\/p>\n<ul>\n<li>While creating \u201cSprint Evaluation\u201d record or on change of \u201cSprint Rating\u201d the value in \u201cSprint Overall Feedback\u201d is set according to above explained logic. Let\u2019s say, the \u201cSprint Rating\u201d is entered as 5 and the \u201cSprint Overall Feedback\u201d is set as Excellent as shown in below screenshot of the model driven app:<\/li>\n<\/ul>\n<p><img decoding=\"async\" class=\"alignnone size-full wp-image-32432\" style=\"border: 1px solid #0a0a0a; padding: 1px; margin: 1px;\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/8Power-Fx-Formula-column.jpeg\" alt=\"Power Fx Formula column\" width=\"867\" height=\"545\" srcset=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/8Power-Fx-Formula-column.jpeg 867w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/8Power-Fx-Formula-column-300x189.jpeg 300w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/8Power-Fx-Formula-column-768x483.jpeg 768w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/8Power-Fx-Formula-column-660x415.jpeg 660w\" sizes=\"(max-width: 867px) 100vw, 867px\" \/><\/p>\n<p>While exploring I liked the real time troubleshooting and real time refresh that it offers, as explained below:<\/p>\n<ul>\n<li><strong>The formula editor supports IntelliSense to suggest formulas and errors in real time<\/strong>: For e.g. If while building the formula expression double quote (\u201c) missed in the real time then on hovering the mouse above it, it will show the error description as below:<\/li>\n<\/ul>\n<p><img decoding=\"async\" class=\"alignnone size-full wp-image-32431\" style=\"border: 1px solid #0a0a0a; padding: 1px; margin: 1px;\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/9Power-Fx-Formula-column.jpeg\" alt=\"Power Fx Formula column\" width=\"577\" height=\"755\" srcset=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/9Power-Fx-Formula-column.jpeg 577w, https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2022\/08\/9Power-Fx-Formula-column-229x300.jpeg 229w\" sizes=\"(max-width: 577px) 100vw, 577px\" \/><\/p>\n<ul>\n<li><strong>The formula editor supports instant refresh in real time:<\/strong> When you create a record within the model drive app, the formula column executes the defined formula expression and displays the data for the record instantly without refreshing the record.<\/li>\n<\/ul>\n<p>You can find more details in this <a href=\"https:\/\/docs.microsoft.com\/en-us\/power-apps\/teams\/formula-columns\">doc<\/a> where you can find the minimum limitations experienced as well, while working with the Formula column remember this feature is still in <strong>PREVIEW<\/strong>.<\/p>\n<h2><strong>Conclusion<\/strong><\/h2>\n<p>The Power Fx formula provides a richer and easier experience while using the column calculations in Microsoft Dataverse which is also progressing to the next level of adding columns seamlessly with supported inbuilt functions.<\/p>\n<h2 style=\"text-align: left;\"><div class=\"su-heading su-heading-style-default su-heading-align-center\" id=\"\" style=\"font-size:15px;margin-bottom:5px\"><div class=\"su-heading-inner\">Save 1-2 hours or $800 monthly on scheduling and managing business travel with a geo-mapping App!<\/div><\/div><\/h2>\n<p><em><strong><a href=\"https:\/\/bit.ly\/3ELX45j\" target=\"_blank\" rel=\"noopener noreferrer\">Maplytics<\/a> <\/strong>\u2013 Integrate Map with Dynamics 365 CRM and visualize data on the map, manage sales territories, auto-schedule appointments, get optimized travel routes, track field reps in real-time, and more<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Recently when I was adding the new column in Microsoft Dataverse from the PREVIEW maker portal (https:\/\/make.preview.powerapps.com\/) I was amazed by looking at the new option in the Datatype available named \u201cFormula\u201d. In this blog, we will focus on the introduction and related use cases where this \u201cFormula\u201d datatype can be useful. Formula Column Features:\u2026 <span class=\"read-more\"><a href=\"https:\/\/www.inogic.com\/blog\/2022\/08\/adding-the-power-fx-formula-column-preview-into-dataverse\/\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[18,44,2361],"tags":[2625],"class_list":["post-32430","post","type-post","status-publish","format-standard","hentry","category-dynamics-365-v9-2","category-power-apps","category-technical","tag-power-fx-formula-column"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/32430","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/comments?post=32430"}],"version-history":[{"count":0,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/32430\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media?parent=32430"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/categories?post=32430"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/tags?post=32430"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}