Large SharePoint lists, Delegation details, Flexible height gallery, Validation with regular expressions, and more

Release 2.0.590 is now available and fully loaded with fresh enhancements and new functionality.  We’ll cover a few of the bigger items here, with the full list on the What’s New page.

Large SharePoint lists

500 record limit be gone!  In January, we announced that the SharePoint connector now supports delegation and more than 256 records.  In this release of PowerApps, we take advantage of this functionality to support large lists when creating apps from data.

For example, imagine we have a SharePoint list of Sales Orders, containing 5,000 records, numbered sequentially:

image

If we direct PowerApps to create a new app starting with this data, the result is an app that looks something like the below.  This screen has been modified slightly to make each row shorter and to show the “Sales Order Number” column on the left hand side of the “Customer Name”:

image

If we type “joe” in the Search box, we will filter this list to just those names that begin with “joe” (in a case insensitive manner):

image

Notice that we are seeing deeper into the SharePoint list than the first 500 records!  The search is being delegated to SharePoint where it can access all the records of the list.  Let’s go further: keep typing and add “ thompson”:

image

And we can scroll all the way to the bottom of the list.  You can’t experience it by reading this blog post, but these results are available quickly.  We do not need to wait for all 5,000 records to be retrieved by PowerApps, instead we are only downloading the records that match our criteria.

image

To accomplish this, we have introduced the StartsWith functionStartsWith tests if one text strings begins with another.  For example, StartsWith( “Joe Smith”, “joe” ) returns true because “Joe Smith” begins with “Joe”.  StartsWith( “Joe Smith”, “amy” ) and StartsWith( “Joe Smith”, “smith” ) both return false because “Joe Smith” neither starts with “amy” nor “smith”.   

When we created our app, the Items property of the Gallery control was set to the following which uses StartsWith to filter the Sales Orders data source.  Again one small modification, we changed the sort column to use the “Sales Order Number” column instead of “Title”.

SortByColumns( Filter( 'Sales Orders', StartsWith( Title, TextSearchBox1.Text ) ), "Sales_x0020_Order_x0020_Number", If(SortDescending1, Descending, Ascending) )

StartsWith is a delegable function that the SharePoint connector supportsStartsWith is similar to the In operator and Search function, but whereas they test if one text string occurs anywhere in another text string, StartsWith only looks at the beginning.  At this time, the SharePoint connector cannot delegate In and Search which is why must use StartsWith.  For an exact match, the SharePoint connector does also support delegation of the = (equals) operator but that is less useful for a search scenario (in our example, typing “joe” would have returned no results).

Delegation details

As we just saw, StartsWith can be delegated to SharePoint.  But how about SQL Server?  How is one to know what is delegable where?

To answer these questions, we have created a new delegable data source list which details which operations can be delegated to each of the delegable data sources

We know that working with delegation can be challenging.   If you have not already, we highly recommend reading the delegation overview.  In short, delegation refers to performing filter and sort operations at the data source.  Without delegation, all the records of a table must be loaded by PowerApps to operate on locally, which can be very time consuming and soaks up network bandwidth.  To avoid long delays, PowerApps caps the amount of data that it will process without delegation.  As a result, when possible you should stick to delegable operations to ensure your app scales as your data grows.  While authoring, PowerApps will flag non-delegable operations with a blue warning dot. 

We are committed to enabling more delegation on more data sources.  Ideally, it should be rare that you see a blue dot and need to think about this as the most common operations would be delegable for the most common data sources.  It will take us some time to get there but we won’t stop until we do.

Flexible height gallery

Since the beginning, the Gallery control has been a handy tool for displaying multiple items in a customizable template. The template for the individual item could be styled to suit the needs of what was shown to include images, text, buttons, etc. But it always had to have fixed boundaries and sizes for the template and its items.

This is not desirable in some cases. Sometimes you need one item to show more information than another and in general have the flexibility to control the height of each item in the Gallery. Flexible height gallery to the rescue!  With this new feature, your apps can look like the below, with different height items for “Sue Miller” and “Time Away”:

flex galery app

You can find the new Gallery control at the bottom of the Controls, Gallery menu:

flex galery menu

It works much like other Gallery controls with the caveat that each item inside the gallery will be resized dynamically to fit its controls, some of which can have variable heights (i.e. AutoHeight set to true).

This means that you can dynamically position and style elements inside without worrying about their overflow!

In the app shown above, we have a few controls inside our gallery template. The header and the title at the top have fixed heights, but the HTML text control in the middle has its AutoHeight property set to true.

Flex Gallery design angelo

HINT: If you have trouble adding items to the Gallery template, increase its height. It only affects the design experience to allow you to add more controls; at runtime the height will be set dynamically based on the content.

Since the height of the HTML text control will vary depending on the content inside, it will impact the layout of all the controls below it.

We can now configure the actionListBox control that follows (another Gallery in this case, but that is beside the point) to be dynamically positioned immediately below the HTML text by setting its Y property to (where margin is an optional constant):

htmlText.Y + htmlText.Height + margin

Likewise, if we had any additional items below, we would have the freedom to calculate their Y and height dynamically, without worrying how this fits inside the Gallery template.

This concept, along with the new gallery control opens the door to beautiful and dynamic feeds in your apps.

Validation with regular expressions

Clean, consistent data helps everything run more smoothly.  And the best way to catch and correct inconsistencies is to validate data as it is being entered. 

Numbers are relatively easy to validate.  You can provide a range of values that are acceptable as a formula.  For example:

OrderQuantity > 0 && OrderQuantity <= 100

You could use the result of this test to determine if there is a problem with the OrderQuantity field and display an error for the user with a Text box control:

OrderQuantityError.Text = “Order Quantity must be between 1 and 100”
OrderQuantityError.Visible = Not( OrderQuantity > 0 && OrderQuantity <= 100 )

Text strings are harder to validate.  Let’s say we are asking our users to enter a Washington State automobile license plate number.  Standard plates in use today can be in one of two formats:

  • ABC1234   (issued 2010 to present)

  • 123-ABC    (issued 1987 to 2010)

To validate this, we need to match what the user enters against the above patterns.  For the first case, 3 letters and then 4 numbers, no more and no less.  To accomplish this, we have added the new IsMatch function:

IsMatch( UserTextInput.Text, Letter & Letter & Letter & Digit & Digit & Digit & Digit )

This formula tests the Text property of the a Text input control named UserTextInput to determine if it contains exactly 3 letters (Letter & Letter & Letter) followed by 4 digits (Digit & Digit & Digit & Digit).  The return value is a Boolean true or false.  We can combine this with another formula for the older style license plates using the || (or) operator:

IsMatch( UserTextInput.Text, Letter & Letter & Letter & Digit & Digit & Digit & Digit ) ||
IsMatch( UserTextInput.Text, Digit & Digit & Digit & Hyphen & Letter & Letter & Letter )

We have provided a set of predefined patterns (Letter, Digit, Hyphen, etc) to make writing common patterns easy.  They can be easily chained together with the & operator.

For those times where a simple pattern isn’t enough, IsMatch also supports standard JavaScript regular expressions.  Although much more cryptic, the above can be rewritten as the more concise:

IsMatch( UserTextInput.Text, “([a-z]{3}\d{4}|\d{3}-[a-z]{3})”, IgnoreCase )

Regular expressions are very powerful, available in many programming languages, and used for a wide variety of purposes.  Although cryptic, those that are familiar with regular expressions can read the above syntax.  For those who are new to them, it takes some time to get to used to them.  Fortunately, there is a wealth of information, tutorials, and examples on how to use them on the web.

And so much more…

Check out the What’s New page for more details on:

  • Customize the order in which controls are activated when users press the Tab key.
  • Customize the thickness of a slider rail.
  • On Android devices, refresh the list of apps by tapping a button instead of swiping down.

As always, this release contains many bug fixes and performance improvements.