{"id":109158,"date":"2017-02-13T08:45:12","date_gmt":"2017-02-13T16:45:12","guid":{"rendered":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/power-apps\/powerapps-update-large-sharepoint-lists-startswith-delegation-flexible-height-gallery-validation-ismatch-regular-expressions\/"},"modified":"2025-06-11T08:15:46","modified_gmt":"2025-06-11T15:15:46","slug":"powerapps-update-large-sharepoint-lists-startswith-delegation-flexible-height-gallery-validation-ismatch-regular-expressions","status":"publish","type":"post","link":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/power-apps\/powerapps-update-large-sharepoint-lists-startswith-delegation-flexible-height-gallery-validation-ismatch-regular-expressions\/","title":{"rendered":"Large SharePoint lists, Delegation details, Flexible height gallery, Validation with regular expressions, and more"},"content":{"rendered":"

Release 2.0.590 is now available and fully loaded with fresh enhancements and new functionality.\u00a0 We\u2019ll cover a few of the bigger items here, with the full list on the What\u2019s New<\/a> page.<\/p>\n

Large SharePoint lists<\/h1>\n

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

For example, imagine we have a SharePoint list of Sales Orders, containing 5,000 records, numbered sequentially:<\/p>\n

\"image\"<\/a><\/p>\n

If we direct PowerApps to create a new app starting with this data, the result is an app that looks something like the below.\u00a0 This screen has been modified slightly to make each row shorter and to show the \u201cSales Order Number\u201d column on the left hand side of the \u201cCustomer Name\u201d:<\/p>\n

\"image\"<\/a><\/p>\n

If we type \u201cjoe\u201d in the Search box, we will filter this list to just those names that begin with \u201cjoe\u201d (in a case insensitive manner):<\/p>\n

\"image\"<\/a><\/p>\n

Notice that we are seeing deeper into the SharePoint list than the first 500 records!\u00a0 The search is being delegated to SharePoint where it can access all the records of the list.\u00a0 Let\u2019s go further: keep typing and add \u201c thompson\u201d:<\/p>\n

\"image\"<\/a><\/p>\n

And we can scroll all the way to the bottom of the list.\u00a0 You can\u2019t experience it by reading this blog post, but these results are available quickly.\u00a0 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.<\/p>\n

\"image\"<\/a><\/p>\n

To accomplish this, we have introduced the StartsWith<\/strong> function<\/a>.\u00a0 StartsWith<\/strong> tests if one text strings begins with another.\u00a0 For example, StartsWith( \u201cJoe Smith\u201d, \u201cjoe\u201d )<\/strong> returns true<\/em> because \u201cJoe Smith\u201d begins with \u201cJoe\u201d.\u00a0 StartsWith( \u201cJoe Smith\u201d, \u201camy\u201d )<\/strong> and StartsWith( \u201cJoe Smith\u201d, \u201csmith\u201d )<\/strong> both return false<\/em> because \u201cJoe Smith\u201d neither starts with \u201camy\u201d nor \u201csmith\u201d.\u00a0\u00a0\u00a0<\/p>\n

When we created our app, the Items<\/strong> property<\/a> of the Gallery<\/strong> control<\/a> was set to the following which uses StartsWith<\/strong> to filter the Sales Orders data source.\u00a0 Again one small modification, we changed the sort column to use the \u201cSales Order Number\u201d column instead of \u201cTitle\u201d.<\/p>\n

\n

SortByColumns( Filter( ‘Sales Orders’, StartsWith( Title, TextSearchBox1.Text ) ), “Sales_x0020_Order_x0020_Number”, If(SortDescending1, Descending, Ascending) )<\/strong><\/p>\n<\/blockquote>\n

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

Delegation details<\/h1>\n

As we just saw, StartsWith<\/strong> can be delegated to SharePoint.\u00a0 But how about SQL Server?\u00a0 How is one to know what is delegable where?<\/p>\n

To answer these questions, we have created a new delegable data source list<\/strong><\/a> which details which operations can be delegated to each of the delegable data sources<\/p>\n

We know that working with delegation can be challenging.\u00a0\u00a0 If you have not already, we highly recommend reading the delegation overview<\/strong><\/a>.\u00a0 In short, delegation refers to performing filter and sort operations at the data source.\u00a0 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.\u00a0 To avoid long delays, PowerApps caps the amount of data that it will process without delegation.\u00a0 As a result, when possible you should stick to delegable operations to ensure your app scales as your data grows.\u00a0 While authoring, PowerApps will flag non-delegable operations with a blue warning dot.\u00a0<\/p>\n

We are committed to enabling more delegation on more data sources.\u00a0 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.\u00a0 It will take us some time to get there but we won\u2019t stop until we do.<\/p>\n

Flexible height gallery<\/h1>\n

Since the beginning, the Gallery<\/strong> control<\/a> has been a handy tool for displaying multiple items in a customizable template. The template for the individual item could be styled<\/a> 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.<\/p>\n

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<\/strong>. Flexible height gallery to the rescue!\u00a0 With this new feature, your apps can look like the below, with different height items for \u201cSue Miller\u201d and \u201cTime Away\u201d:<\/p>\n

\"flex<\/a><\/p>\n

You can find the new Gallery control at the bottom of the Controls, Gallery menu:<\/p>\n

\"flex<\/a><\/p>\n

It works much like other Gallery controls<\/a> 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<\/strong> set to true).<\/p>\n

This means that you can dynamically position and style elements inside without worrying about their overflow!<\/p>\n

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<\/strong> control<\/a> in the middle has its AutoHeight <\/strong>property set to true.<\/p>\n

\"Flex<\/a><\/p>\n

HINT: If you have trouble adding items to the Gallery<\/strong> 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.<\/em><\/p>\n

Since the height of the HTML text <\/strong>control will vary depending on the content inside, it will impact the layout of all the controls below it.<\/p>\n

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

\n

htmlText.Y + htmlText.Height + margin <\/strong><\/p>\n<\/blockquote>\n

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

This concept, along with the new gallery control opens the door to beautiful and dynamic feeds in your apps.<\/p>\n

Validation with regular expressions<\/h1>\n

Clean, consistent data helps everything run more smoothly.\u00a0 And the best way to catch and correct inconsistencies is to validate data as it is being entered.\u00a0<\/p>\n

Numbers are relatively easy to validate.\u00a0 You can provide a range of values that are acceptable as a formula.\u00a0 For example:<\/p>\n

\n

OrderQuantity > 0 && OrderQuantity <= 100<\/strong><\/p>\n<\/blockquote>\n

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

\n

OrderQuantityError.Text = \u201cOrder Quantity must be between 1 and 100\u201d<\/strong>
\nOrderQuantityError.Visible = Not( OrderQuantity > 0 && OrderQuantity <= 100 )<\/strong><\/p>\n<\/blockquote>\n

Text strings are harder to validate.\u00a0 Let\u2019s say we are asking our users to enter a Washington State automobile license plate number.\u00a0 Standard plates in use today can be in one of two formats:<\/p>\n