• User defined functions (UDFs) can now include behavior functions with side effects, such as Set, Collect, Reset, and Notify. Declarative is always best, so use this facility only when you must. When you do, wrap the formula in { } and you can then use the ; (or ;;) chaining operator.
• User defined types (UDTs) enable tables and records to be passed in and out of UDFs. UDTs also enable bulk conversion of JSON untyped objects to typed objects, particularly useful with web APIs. Welcome the new Type and RecordOf functions, an expanded role for the AsType and IsType functions, and a new parameter for ParseJSON.
• Enhanced component properties (ECPs) have moved to preview. With any remaining feedback, we plan to take them to general availability in the next few months. ECPs enable the ability to share logic across apps through a component library.

This is the last major update to UDFs planned before we start down the road to general availability. UDTs will be on the same timeline. Now is the time to take this functionality through its final paces and provide feedback before the design is locked; please leave feedback in the community experimental features forum. Both features are experimental and require turning on these switches in Settings > Updates > Experimental:

Behavior UDFs

Named formulas depend on being declarative, something the system can defer and recalc based on changes in the app. They can’t have side effects, such as incrementing a variable, or this wouldn’t be possible. UDFs to date have built on top of named formulas by adding parameters, but still had to be declarative.

Obviously, that is limiting for an app. We need buttons, buttons that do important things like updating a database. We’d like to be able to put that logic in a UDF too. And now you can, by wrapping the UDF in curly braces:

CountUp( increment : Number ) : Void = {
    Set( x, x+increment );
    Notify( $"Count: {x}" );
};

This simple example will increment the global variable x and display a notification with the result each time CountUp(1) is called from, say, the OnSelect formula of a Button control.

This is a huge step for reuse and manageability. Now you can extract and parameterize your action logic and reuse it throughout your app, having only one source of truth for that logic that is easier to maintain.

For more information and examples, see Behavior user defined functions.

User Defined Types

User defined types enable UDFs to take in and return records and tables. Until now, UDFs were limited to scalar data types, such as Number, Text, DateTime, etc.

The new Type function is the key. Use it to define a named formula for a type, using the same syntax you would use for a literal record or table value. For example, imagine you had a table of paper sizes:

PaperSizes =
[ { Name: "A4", Width: 210, Height: 297 },
  { Name: "Letter", Width: 216, Height: 279 },
  { Name: "Legal", Width: 216, Height: 356 } ];

We can define types for a single paper as a special kind of named formula that uses the := assignment operator and Type function. The syntax used within the Type function is the same as the literal record values in the PaperSizes definition, with specific values replaced by their data type names:

PaperType := Type( { Name: Text, Width: Number, Height: Number } );

We can now use this type to pass a single paper size into a UDF:

PaperArea( Paper: PaperType ): Number = Paper.Width * Paper.Height;

And we can call our UDF with:

PaperArea( First( PaperSizes ) ) // returns the number 62370

We can define a type for a table, matching the type of PaperSizes:

MultiPaperType := Type( [ PaperType ] );

And then define a function to filter a table of paper sizes:

LargePaperSizes( Papers: MultiPaperType ) : MultiPaperType =
    Filter( Papers, PaperArea( ThisRecord ) > 70000

And then we can call it with:

LargePaperSizes( PaperSizes )
// returns [ { Name: "Legal", Width: 216, Height: 356 } ]

For more information and examples, see User defined types.

JSON and Untyped ❤️ UDTs

UDTs are great for UDFs. But they have another great use case: converting an untyped object to a typed object. Imagine that JSON was returned from a web API for another set of paper sizes:

MorePaperSizes =
"[ { ""Name"": ""A0"", ""Width"": 841, ""Height"": 1189 },
{ ""Name"": ""A6"", ""Width"": 105, ""Height"": 148 } ]";

You probably know that you can use the ParseJSON function to convert this to an Untyped object.

MoreUntyped = ParseJSON( MorePaperSizes )

From which you can extract individual elements from the JSON by casting them explicitly or implicitly at the point of use. But since the structure is untyped and potentially heterogenous, it cannot be used with functions such as AddColumns which requires a homogenous Power Fx table:

AddColumns( MoreUntyped, InStock, true ) // error

With UDTs, we can now convert this JSON directly to a Power Fx typed object by providing the type as the second parameter to ParseJSON:

MoreTyped = ParseJSON( MorePaperSizes, MultiPaperType )

And now we can add a column:

AddColumns( MoreTyped, InStock, true ) // OK

The IsType and AsType functions have also been overloaded to take an untyped object and type arguments. Web APIs that return an untyped object can now be easily converted to a typed object.

Feedback, please!

These are experimental features, and for good reason, we are still making changes. There are a lot of nuanced details with UDFs and UDTs we want to make sure we get right. We’d love to hear your feedback on how it works, please let us know in the community experimental features forum.

The post User defined functions, user defined types, and enhanced component properties move forward appeared first on Microsoft Power Platform Blog.

With version 3.24063 we are making changes to how we do this in Canvas apps. We will now have four feature stages:

• New: These are new features that are generally available (GA), fully supported, and documented. These will generally be on by default for new apps, but may take time to deploy everywhere. Enable for existing apps on your own schedule.
• Preview: These features are almost done and will be New soon. But at this stage there still may be some breaking changes made. This is the last opportunity for feedback. These will generally be off by default and will be documented, although some exceptions will be made especially for fast moving AI related features. These features are not GA, should not be used in production, and are covered by our preview terms of service.
• Experimental: An early stage “Preview,” these truly are experiments and may never reach GA. We are assessing the value proposition and design of the feature, and the feature may radically change or be removed completely at any time. These features will be off by default and will generally not be documented. These features are not GA, should not be used in production, and are covered by our preview terms of service.
• Retired: These are GA features but are on their way to being removed from the product. They are still fully supported and document, but we either have a new, better way to do the same thing or usage is low. Disable for existing apps on your own schedule. Generally, these features are off by default.

Why did we add a fourth New category? We needed a new category that clearly indicates that features are GA and the long-term direction of the product, and yet still needed the flexibility of having a switch to control the feature. The switch allows us to slowly deploy the feature and watch for problems, and at the same time gives you control to enable the feature for your production workloads on your own schedule especially when a breaking change is involved.

GA of features moved to New

Practically what this means is that we won’t have features sit in Preview for as long as we have in the past. With this release, we are also announcing the GA of these features that have moved to New:

• Modern controls and themes (this feature set was already GA under General settings but the switch is more appropriate here)
• New analysis engine
• Expanded media support for SaveData on Power Apps mobile
• SQL Server stored procedures

GA of features with disable moved to Retired

The following features are also now officially GA and no longer have switches in Preview:

• Delayed load
• Formula-level error management
• Non-blocking OnStart rule
• Formula-level prefetching
• New data format in Microsoft Excel Online Business connector
• Keeping recently visited screens in memory
• SaveData, LoadData, and ClearData in the web player
• Simplified tab index
• Performance optimization for hidden controls

However, you won’t find switches for these in New. These features have been in the product and on by default for a while and no longer qualify as “new.” To control these features, we have added “Disable” versions of all of the above in Retired, as the old behavior without this feature is now retired and will eventually be removed from the product.

For example, “Delayed load” has been a Preview switch for ages and has now graduated to GA. Since this has been a feature for a long time, instead of New, we added “Disable delayed load” to Retired. Using the new behavior of “Delayed load”, without a switch being toggled, is the way most apps should operate. But until we fully remove the old behavior (without “Delayed load”), there is still a way to get it through the disabled “retired” switch.

These Retired behaviors will one day be removed from the product. The timelines and process for removal will vary based on the feature, usage, and alternatives available and will be well communicated ahead of time if it involves a breaking change.

Summary

A few other notes, the “Enable improved data table control selection and Value property” feature will remain in Preview but will be disabled by default. This long running feature has been superseded by the new modern control “Table (Preview)” and we recommend that new apps use this feature instead. This change does not impact existing use of the old table control; all we have changed is the default for new apps.

As that last example demonstrates, there are still “Preview” tags throughout the product, for which there is not a Settings switch. Despite the lack of a switch, these are still “Preview” features, are not GA, and should not be used in production apps.

Alot of change. But actually, if you didn’t open Settings, the only thing that has changed is that the old table control is now disabled by default for new apps. We have mostly just done some rearranging. No existing apps will change behavior.

It is our hope that these changes will clarify that Preview features are not for production as they will generally be off by default. We will graduate features up to New and GA faster so that they can be on by default for everyone, and old behaviors that should no longer be used but are still GA will be easily identified in Retired.

You can read more about the feature stages at Understand New, Preview, Experimental, and Retired features in Canvas apps. To recap:

• New: Good for all workloads. Generally turned on for new apps; turn on for existing apps as appropriate.
• Preview: Almost ready to ship. Good for feedback, but not for production. Turn off by default except for what is being tested.
• Experimental: An experiment. Good for early adopters and early feedback, but not for production. Keep all off, except for specific features that you are helping us refine.
• Retired: Good for all workloads. But there is probably a better way and the old behavior will eventually be removed. Generally turned off for new apps; turn off for existing apps when you can.

As always, we love your feedback, please leave comments in the community forum.

The post GA of many long time Preview features appeared first on Microsoft Power Platform Blog.

Delegation refers to an app’s ability to “delegate” work to the data source instead of doing it locally, resulting in more scalable and higher performance apps.  In short, Delegation = Good.  For more details see Understand delegation in a canvas app.

We are far from done with delegation.  If you have suggestions on which queries and operations we should improve next please leave a comment or use the community forum.

Filter on lookup non-primary key fields

This one is specific to the Common Data Service.  Until now we have only supported filtering an entity based on the primary key of a Many-to-One lookup.  We can now filter based on other fields in the related entity, dramatically expanding the possibilities when working with lookups.

For example, consider the following screen, built on top of the standard Accounts and Contacts entities:

Note that there are no blue wavy lines or warning symbols – this formula is fully delagable.  Let’s type a value in the text input box to perform our filtering:

If we watch the network traffic from our app, and URL decode the query, we see:

$filter=(primarycontactid/address1_city eq 'Issaquah')&$expand=primarycontactid

This is OData speak for the formula we wrote with “primarycontactid” and “address1_city” being the logical names for our entity and column references in the formula.  The IsBlank test does not appear here because it does not depend on data in the records being filtered and was handled before talking with the data source.

We can also delegate the StartsWith and EndsWith functions for our search:

But we can’t yet delegate the in operator, it is on our to-do list.  Although definitely not the same, StartsWith can be used for many scenarios until we can delegate in.

Because these Filter calls can now be delegated, we can now search through millions of records and return the first page of results quickly with the ability to go back for more as the user scrolls through them in the gallery.

Today and Now

In the lookup filter example above, the IsBlank test didn’t need to be delegated because it could be evaluated locally before going to the data source.  We will be doing more of these optimizations over time.

Case in point: we just added the Today and Now functions to the list of things we can do locally.  For example, this filter of the Accounts entity:

expands the Today function call into a constant to send to the data source:

$filter=createdon ge 2019-04-01T16:16:03.013Z

Similarly, we already supported delegation for the Date function:

But, we don’t yet support constant folding with the DateAdd function:

We’re on it.

AddColumns

Lots of people use the AddColumns function to bring in data from across data source, effectively performing heterogeneous joins.

For example, imagine that you have a list of real estate agents in SharePoint and a table of home listings in SQL Server that contains tens of thousands of rows. You could combine this information with AddColumns:

AddColumns( RealEstateAgents, "Listings",  Filter(  '[dbo].[AllListings]', ListingAgentName = AgentName ) )

Until now, AddColumns would not delegate the Filter call to SQL Server in the third argument and thus only the first 500 listings would have been searched.  No longer, we can now delegate all the arguments of the AddColumns function.

But we aren’t done here yet. What we haven’t done is make the outside of the AddColumns delegable and pageable, the output is still limited by the non-delegation limit.  If for example you attach the result to a Gallery control through its Items property, it will be limited to 500 records.  Something for our next installment.

A word of caution on using AddColumns in this manner.  Each Filter call above is another round trip to the data source.   If performance is an issue, check the network activity in your browser to understand what is happening behind the scenes.  If the number of related rows is small enough, you may want to bring them into a collection and do the filtering locally.  You may also want to restructure your app to only grab the related records when a user specifically asks for them.   We can and will make this better in time, for example we could batch these calls for multiple rows to make this more efficient.

The post Delegation enhancements for lookups, Today/Now, and AddColumns appeared first on Microsoft Power Platform Blog.