No record limit with SQL Azure and Salesforce

We continue to enhance our ability to work with large data sets.  We are happy to announce that with this release, apps created from SQL Azure and Salesforce now support more than 500 records.  As long as the author sticks to delegatable functions and operators and records are not unreasonably wide, PowerApps places no limit on the number of records that can be worked with.  SQL Azure and Salesforce are just a start – more data sources are on the way.

For illustration purposes, imagine you have a table in SQL Azure of 20,000 records, named “20,000 Rows”.  In this table, there are two columns:

• The Number of the record.
• The same value spelled out in a text string, named “NumberString”, separating out the thousands for illustration purposes.

If we direct PowerApps to create a phone app from data on this table using a SQL Azure connection, the result displays:

Notice that the rows are ordered 1, 10, 100 – because we are sorting based on the lexical order of text strings, not the numerical order. 

You can’t tell when reading this, but this screen is populated relatively quickly.  When the app starts, it pulls down the first page of results, but does not pull down the full 20,000 records.  As the user scrolls down past the end of the first page, the next page is brought down automatically.

Now let’s try some interesting searches.  Place “500” in the Search box:

The app has done a substring match for “500” and returned just those results across all 20,000 records.  And you can’t tell from reading this, but this is also done relatively quickly.  The app sent the search term to SQL Azure to process, and SQL Azure replied with only the 20 records that contain “500” in their name (20 records).

Let’s try another search: “10”.  This will result in 1,380 records, and again we will pull down just one page of results at a time so that this screen is displayed quickly: 

And if we scroll down, we can see some more “10” matches:

We can also sort this list in the opposite direction by pressing the sort up/down button at the top of the screen:

Computers have been searching and sorting text by pushing queries to servers for a very long time. Nonetheless, this is a milestone for PowerApps in supporting large data sets.  In addition, PowerApps is novel in some important ways:

• PowerApps brings these capabilities to mobile devices, making it possible to have good performance in low bandwidth and limited memory/storage situations.
• We will support a wide assortment of data sources. The first step is SQL Azure and Salesforce but more connectors will be added in the months ahead.  You can tell us which ones are most important to you on the Community pages at http://powerapps.com.
• We will make it easy to author an app that can handle large data sets and performs well.  In the above example, not one formula was touched  – this app was completely generated by solely looking at the schema of our 20,000 row table.  Even with great connector diversity, authors work with data in a consistent manner.

New Search function

If we look at the formula for the gallery’s Items property, we’ll find:

SortByColumns( Search( '[dbo].[20,000 Rows]', TextSearchBox1.Text, "NumberString"), "NumberString", If(SortDescending1, Descending, Ascending) )

We have introduced a new Search function to search one or more columns for a text string.  It automatically handles the case in which no search term is present, returning all records, a good match for a search Text input control.  As was shown above, the entire text string need not be matched (a substring match).  For more details and examples, please see the documentation.

Also in this release, the In operator now supports delegation making it possible to delegate the equivalent of the above formula using the Filter function instead.  We have decided to switch over to the Search function for apps created from data since it is tuned to this scenario and it is simpler to use.  Some caution will always be required with the Filter function as a complex formula could be introduced that cannot be delegated.

More to come

Although we have reached an important milestone, we are far from done.  Here are some of the important limitations to be aware of:

• With each keystroke in the Search box, the app will initiate a new search.  At present, we do not cancel the old ones and the queries stack up sequentially.  So although a single query may be fast, slowly typing a long search term will result in many queries being issued, one after another, and performance will suffer.
• The Search function operates only on text fields.  It will not find, for example, “10” in a number or date field.  This is why we needed to have a text field in our table in the above example.
• Delegation support in more functions and operators.
• And as mentioned above, delegation support in more connectors.

Other new features in release 2.0.450

As always, we continue to fix bugs and improve performance across the board. 

Thank you for your many questions, comments, suggestions, and bug reports on the Community pages at http://powerapps.com.  Even easier is the “Send feedback” button while authoring (upper right hand, next to your name).  We really want to hear from you!

Adding a data source to Form controls is easier

When working with form controls, you can now add a data source directly in the right hand pane.   Tap the data source icon or “No data source selected”.

Set shared password for protected PDF files

There is a new Password property on the PDF viewer control which an author can set so that all users of the app can view a password protected PDF.  Without this, each user will be prompted for the PDF’s password.

 

UTC now supported in the DatePicker control

To stay time zone agnostic, many data sources store date/times in Coordinated Universal Time (UTC).  There is a new DataTimeZone property on the Date picker control, which an author can set to Local or UTC.   If this difference is not taken into account, displaying and picking dates can be off by one, giving unexpected results.