Blog

Formulas - Review of how to write formulas using natural language

During the Build conference in May 2021, Microsoft announced an innovative new feature - the ability to write formula using natural English words and sentences. This feature is now in preview, and this post takes an initial look at how this works.

For "no code" app builders, especially those who are new to Power Apps, the ability to write formulas using plain English is a great feature. For these users, it can be very difficult to know which functions to call, and to understand syntactically nuances, such as the differences between square, curly, and round brackets, and to understand the differences between commas and semi-colons.

During the Build conference, Microsoft announced a new AI-based feature that can suggest formulas based on natural language. The great news is that this feature is now in preview and we can now explore the capabilities of this feature.

What can we do with the preview version of this feature (the ideas panel)?

The initial preview enables us to set the Items property of gallery and data table controls based on plain English sentences that we enter into an ideas panel. For example, app builders can construct a sentence such as, "show top 10 records by date", or "search textbox with contacts fullname".

The initial preview works against Dataverse data sources only. Furthermore, it can suggest only formulas based on the limited set of functions that are shown beneath.

Date, DateAdd, DateDiff, DateTimeValue, DateValue, Day, Distinct,
EndsWith, Filter, FirstN, Hour, IsBlank, IsEmpty, LastN, Minute, 
Month, Now, Search, Second, Sort, SortByColumns, StartsWith, Time, 
TimeValue, Today, Weekday, Year

What are the pre-requisites and how to get started?

The preview feature works with the US English language only. Therefore, we must be connected to an Environment that's hosted in the US region with an available Dataverse instance in order to access this preview feature.

Non-US users can provision a new US environment to access this feature. Note that if we were to provision a new environment for this purpose, it's important not to create a 'Preview' environment because it does not support the creation of the new database (as shown beneath). Therefore, a 'trial' 30-day environment is the ideal environment type to test this feature.


Another crucial thing is that once we provision a US environment and Dataverse database, we must access the Maker portal through the preview URL:


If we access the Power Apps designer through the normal endpoint, the ideas panel will not appear.

How to use the Ideas Panel and how well does this feature perform?

To use this feature, we first add a gallery or data table control to a screen. An ideas pane will then appear in the properties window. Here, we can type our natural language query into a text box.

To test this feature, I connected a gallery control to a table helpdesk issues. My aim was to build a formula to return overdue issue records. I constructed the following sentence and typed that into the ideas panel:

"Show issues where the target end date is greater than today"


The idea panel struggled with this sentence and gave the main answer "We can't find a good answer for you". It suggested some alternate sentences and formulas in the 'Answers' section. Whilst these answers didn't provide a correct formula,it provided sentence patterns that would work more successfully. For example, writing a sentence that begins "show records of [table name]" yields more accurate results.

This initial experiment also highlights that if we want to express a condition that involves a field name, it's important to reference the actual field name enclosed in double quotes (for example 'TargetEndDate', Fortunately, the idea panel makes this simple by providing IntelliSense in the ideas text box.

To try to obtain my required formula, I modified my sentence as follows.

"Show records of issues where 'TargetEndDate' exceeds today's date plus 5 days"

Again, the ideas panel didn't produce the required formula.


I tried other variations of the sentence, but the Ideas panel still struggled to produce the required formula.


On a positive note, however, the Ideas panel successfully provided correct formulas for more simple queries. For example, it gave a correct suggestion when I asked how to return records where the issue description contains the text that the user enters into a textbox, ordered by the target end date in descending order. This specific example highlights one of the most common questions that new users ask - that is, how to combine the sort/filter/search functions.


Conclusion

The ability to write formulas using natural English words and sentences is very exciting. If Microsoft succeeds in developing a designer where app builders don't require a strict understanding of formula and functions, it will make Power Apps a very desirable proposition for "no-code" developers.

The initial preview does a great job of showcasing how this feature will work. Whilst the preview may not always offer the correct answers, it's important not to be too critical - after all, this is just the very first preview. But even with this limited first release, the ideas panel provides adequate guidance for app builders to intuit the correct formula. I look forward to seeing how this feature progresses in the months to come.
Previous
Next
Related posts