What a great last few days for PowerApps!  I hope you have been enjoying the announcements in the Spring Update Blog bundle.  Have you seen Satya’s video?

This blog doesn’t quite rise to the level of those others.  It is more a statement of direction then a cool demo, although I do have one “crowd goes wild” moment below.  Many of you will yawn.  But for some this will be a big deal because PowerApps can now write Null values to databases.

Experimental Features

But before I launch into the good stuff, let’s talk about Experimental features and what that means.

Today under the File menu, App settings, you will find “Experimental features.”  It states that these features might change, break, or disappear at any time.  We mark features in this manner when:

• We literally want to experiment.  We’d like the space to try out different designs, learn, iterate, and we’d like to do so with your feedback too.
• We want to roll out changes but we aren’t completely sure of our design and the impact it may have on existing apps.  Again your feedback is critical.
• We aren’t done with the feature but there is value in getting a partial solution to you.

All of these reasons apply in the case of Error handling.  Adding error handling is a large, pervasive change that could impact your apps in ways we can’t foresee, yet there is value in getting the ability to write Nulls to you now.  So we are going to keep the feature turned off for both old and new apps, and allow those of you who want to play with it and desire its benefits to opt-in.  We’ll keep improving the feature on our end and listening to your feedback.

We’ll let you know when we think we are done and are ready for more widespread usage.  The description says we will be turning this feature on for all apps in September.  We are actually going to remove that language as we aren’t sure about the timeline.  We do plan to eventually turn this on for everyone, without a switch, but only when the time is right.  This feature will not just disappear.

You will also notice that the “Data row limit for non-delegable queries” feature is here.  This feature is not experimental – we are done with it, it is here to stay, and you can you depend on it.  This was merely a convenient place to put the setting.  In the next few months we will be reorganizing these settings to better reflect the difference between Experimental and “finished” app settings.

Handling Errors

It is no secret that PowerApps is inspired by Excel.  But there is one aspect of Excel that PowerApps had not embraced: error handling.  In Excel, one can write IfError( <value>, <value if error> ) for which PowerApps had no equivalent.  Errors in PowerApps would result in a simple Blank (or Null) value.  Which caused issues because Blank values are legitimate non-error values in most databases.  As a result, you could not write a Null value to SQL Server.

I’m using past tense intentionally.  I’m pleased to announce that PowerApps can now differentiate an error from a Blank and can write Null values to databases.

We have a small number of sources for errors at present.  Division by zero, converting non-numbers to numbers, Text function usage with an invalid locale string.  We are retrofitting some of the other functions to also return errors, such as Patch.  This is partially why this is still an experimental feature – we aren’t done.

To ease the transition, errors operate like Blank values in most contexts.  For example, IsBlank will return true for them.  They do not display as #DIV/0!, #Error, or other representation as they do in Excel or other products, at least not yet.  To detect an error, you need to use the new PowerApps’ IfError function.

IfError( value, value-if-error, … )

This function is very similar to the Excel function of the same name, extended to support multiple fallback values.  IfError evaluates its arguments in order and returns the first one that is not an error.  IfError can be used to replace an error value with a valid value, such as in this example:

IfError( 1/0, 2 )

returns the value 2, since 1/0 results in a division by zero error.

ShowError( message )

If you detect an error, it is often nice to tell the user about it.  ShowError will display a notification across the top of the app’s screen, as a popup, just like system errors are displayed.

Combined with IfError, this function can be used to report a problem to the user:

IfError( Value( “not-a-number”), ShowError( “hey, that’s not a number” ) )

Writing Nulls

Being able to handle errors is great.  But here’s the real payoff.  Now that PowerApps can differentiate a Blank (Null) from an error, PowerApps can now write Null values to SQL Server.

Take for example the Product table from Adventure Works, with lovely Null values for Size and Weight for the helmets:

Let’s build an App from Data for this table and edit the size and weight for the Sport-100 Helmets, Red:

Sure enough, that works great:

But, you know on second thought, we’re really not sure about that Weight.  We should clear that information out.  Let’s make that change in PowerApps:

Without turning on the error handling feature, it will seem like this app doesn’t work at all.  You can’t seem to clear the Weight.  Internally, PowerApps thinks the empty Weight is a Null value, which it thinks is an error, and it doesn’t even attempt to write this to SQL Server.

But with error handling enabled it works.  The Null is properly differentiated from an error and is written to the database.  And the crowd goes wild when they see a Null value written to a SQL table by PowerApps:

Empty strings vs. Blank (Null) values

Now, let’s get tricky.  Clear the Size field as well and try to save that to the database:

Wait a minute, the Size isn’t yellow and doesn’t say Null?  Could it be that we stored an empty string in the SQL table?  For many of you a little voice just screamed “Noooo!!” inside your head.

Ah, this is an old argument.  There are two camps for how “empty” text fields should be stored:

• It is an empty string, an actual string with zero length that contains no characters. 
• It is a Blank (Null) value, distinct from a string.  Similar to a Boolean that can be only True or False, in addition a third possible value of Null is possible.

For the most part, the Text Input control will return an empty string if no characters are within it.  This is consistent with other PowerApps controls that return valid, non-Blank values if they can.  There are exceptions where it will return a Null today that we we will be fixing in the weeks ahead.  For those who favor empty strings, the output from the Text Input can be stored directly.

If you prefer to store Null values, you will need to convert the empty string returned by the Text Input control into a Null.  The easiest way to do this is by using the formula Coalesce( TextInput.Text, Blank() ).  Coalesce uses the same test as IsBlank: it is looking for either an empty string or a Null value.  A great place for this conversion is the Update property on data cards in the form which you can apply today by unlocking the card:

DataCard.Update = Coalesce( TextInput.Text, Blank() ).

In fact, that’s exactly the change I made to my app, resulting in the following and we are finally back to where we started:

Why didn’t we need do this for Weight?  Because it is a numeric field that only has one possible representation for the “empty” state: Null.

Since we believe most people prefer to store Null values in databases we will be changing the data cards for new apps to do this conversion by default.  Which moving forward puts the onus on those who prefer empty strings to remove this conversion if that is what they prefer.  This change hasn’t happened yet, it will be coming in the weeks ahead.

So, here’s a recap, for string fields only:

• If you prefer Nulls:
  • Text Input control will return an empty string “”.  You will need to convert this to a Null with DataCard.Update = Coalesce( TextInput.Text, Blank() ) before writing to the database.
  • In a few weeks, we will have done this conversion for you for new apps and fields.
• If you prefer empty strings:
  • You won’t want the conversion, now or in the future.  The Text Input control is doing what you want and can be written to the database directly.
  • In a few weeks, you will need to unlock the data cards and remove the conversion we will be inserting automatically.  If there are enough empty string people out there, we can look at adding an easier way to remove the conversion.

Wouldn’t it be great…

• Wouldn’t it be great if the Patch function would return an error instead of a Null when there is a problem writing data? 
• Wouldn’t it be great if there was an Error object that you could look at inside an IfError to get more details about the error?
• Wouldn’t it be great if there was an OnError event at the app level to catch errors across the app, so that you can globally suppress the ones you don’t care about and still show a message for the ones you do?

These are all on the drawing table and more.  We’re only beginning this journey.

Your feedback

As always, we deeply appreciate your feedback.  Adding error and Null handling to an existing product is not a small or isolated change – we need your help to get it right.  Please let us know what you think, offer any suggestions, and report any problems you run into.