If you are using PowerApps to create apps that connect to SharePoint lists, you may encounter some issues when dealing with null values in numeric columns. In this post, I will explain what these issues are and how you can solve them by using app settings.

The Problem

A common scenario is that you have a SharePoint list that contains a numeric column, such as Order Value, and you want to use PowerApps to create or edit items in this list. You may want to allow users to leave this column blank if they don’t have a value to enter, or if the value is not applicable.

However, if you use the default settings of PowerApps, you may run into some problems:

  • If you leave the Order Value field, from the example above, blank in PowerApps and submit the form, it will not save as null or blank in SharePoint. Instead, it will save as 0 (zero), which may not be what you want.
  • If you have a calculated column in SharePoint that depends on the Order Value column, such as Total Value = Order Value * Tax Rate, it will also return 0 if the Order Value is blank. This may cause incorrect calculations or reports.
  • If you want to apply some formatting or validation rules based on the Order Value column in PowerApps, such as showing a warning icon if the value is too high or too low, it will not work properly if the value is blank. PowerApps will treat blank values as 0 (zero), which may not match your logic.

The solution

As I was researching solution to this problem when I got hung up on it, I came across this post from Microsoft: https://powerapps.microsoft.com/en-us/blog/new-feature-error-handling-and-writing-null-values-to-databases/

Here’s a summarized solution for you to apply in your PowerApp:

  1. Go to App settings > Upcoming Features.
  2. Enable Formula-level error management by turning the feature on. This will make your app more consistent with how SharePoint handles null values in numeric columns.
  3. Save and publish your app.

Now, when you use PowerApps to create or edit items in your SharePoint list with a numeric column:

  • If you leave the field blank and submit the form, it will save as null or blank in SharePoint. This way, you can distinguish between 0 (zero) and null values.
  • If you have a calculated column that depends on the numeric column, it will return null if the numeric column is blank. This way, you can avoid incorrect calculations or reports.
  • If you want to apply some formatting or validation rules based on the numeric column, it will work properly even if the value is blank. You can use functions like IsBlank() or IsEmpty() to check for null values.

If you have any questions or feedbacks about this topic , please feel free to leave a comment below or contact me through the contact form.

Cheers!