A Few Best Practices in Data Validation

Data Validation within apps and business forms is critical to prevent errors, and to ensure data transactions occur without errors and uncomfortable bottlenecks during submission. In this blog I will be sharing my personal best practices for data validation in the PowerApps that I design. I hope these will help you to enhance your organization's PowerApps too.

Having spent many years designing business forms using technologies such as Adobe Acrobat, InfoPath, Nintex, Claysys, and simple javascript, I recognize that there are things we must do to protect the format of data entries, as well as to facilitate form entry. Any seasoned form designer knows that data validation is a big part of being able to design forms that people enjoy using. Poor data validation on the other hand can send consumers running for the hills, because they either end up unable to enter the data they want to enter, or bottlenecked by error messages that they rarely understand. Therefore, I spend some effort checking all my business apps for proper data validation. Questions you may want to use to challenge the effectiveness of your own forms include:

  • Is it very clear what type of data I'm looking to get from those filling out my forms?
  • Is there any possibility that the end user can enter something that is incompatible with my data source?
  • Can I do anything in behalf of my end user to help them to avoid making mistakes?
  • Is there something I can do to help my end users to fill out the form more completely and honestly?
    (this includes removing the risk of influencing the person filling out the form in a way which will skew results)
  • Can I include on-submission, as well as pre-submission, data validation messaging?

By running through these simple questions above, you'll be able to make a list of form enhancement targets. Although there is no "perfect" form designer tool, there is quite a lot you can do to improve the experience. You will definitely be able to use your list of targets to plan out data validation goals in PowerApps too. Here are my top tips regarding things you should know as you get started down this path:

1. APP FROM DATA BUILDS IT IN - Whenever you build an app from data, there is a layer of error management built in for you. It occurs whenever forms are submit, and is configured to report back to the end user whatever error messages are coming from the data source. These error messages are hard coded, as they rely on the data source for any verbiage used.

Example: If you build an app from data on SharePoint which has a column called "Title" which is a 'required' field, then if you attempt to submit a new item to SharePoint where the 'Title' field value is blank, then you'll see the error message provided by SharePoint beneath the text input control (and as a red banner at the top):

red text below blank field value

These error messages are considered on-submission error messages because there is no warning to the user before the submit icon is clicked. Take a look at this documentation on our community portal to learn more about the form error messages that are pre-configured for you when you build an app from data. You can use this information to configure those error messages yourself when you are patching data rather than using auto-generated apps.

What else can you do?....


2. EVALUATE ERRORS WHILE TYPING - In the animation above I demonstrate the user experience for evaluation of errors during typing. This relies on a property within the error message itself, rather than on the Text Input Control (named: TextInputRefund) used to enter the data. It is displayed based on a condition set in the 'Visible' property of the label containing the error text:
If(IsNumeric(TextInputRefund.Text) || IsBlank(TextInputRefund.Text),false,true)
Simply stated it means that if the refund text is completely numeric, or completely blank, then don't show the message, otherwise it will be shown. How you format the error message (text control named: errorMessageTxt) is entirely up to you.

3. EVALUATE ERRORS ON CHANGE - In the animation above I also demonstrate the user experience for evaluation of errors on change (or on focus loss as some form designers refer to this). It is displayed based on a variable, plus a condition set in the 'Visible' property of the label containing the error text. However, it is not evaluated until the end user leaves the text input control. Let's start with the variable first; I dynamically create a variable called VarRefund whenever the value changes in that control. I did this by setting an OnChange condition directly on the Text Input Control:
I choose to use the OnChange property so that the value of the variable is not evaluated while they are typing (that would be annoying). Lastly, I moved to the error message text label to set the condition on the "Visible" property like we did on the other one, but with this shorter formula (only a true statement is needed):
Simply stated it means that if the refund variable VarRefund is > 5000, then show the message, otherwise it is not visible. Again, how you format the error message (text control named: errorMessageAmt) is entirely up to you.

4. REMOVE NON-RELEVANT DATA - This is one that even new form designers think about; removing any data that could confuse the user, or enable them to make a bad selection. In this case, leverage PowerApp controls to do smart form design by:

  • defaulting values for the end user whenever contextual user profile information can be leveraged using the Office 365 Users connection. Remember that they can change default values, so prepare for that possibility
    (see this article on using the Office 365 Users connection)
  • setting up cascading dropdowns to ensure that the data that can be selected is the only data that should be selected, as it applies to the end user filling out the form (leverage PowerApp dropdown controls and any helpful data sources to achieve this)
    (see this blog entry on cascading dropdowns using SharePoint data)
  • removing spaces from input data for data sources, or analytics, that may be affected by extra spaces in the response data by using the Trim() function
    (see this article on the Trim() Function)
  • reformatting data to all upper, lower, or proper case before submission, if that is best for consistency within the data source receiving the submission. This is easy to do using the Upper(), Lower() and Proper() functions.
    (see this article on the Upper(), Lower(), and Proper() Functions)

5. BE CAREFUL WITH DEFAULT VALUES - Text input boxes, drop down lists, check boxes etc can all have default values configured. However, be careful that you are not influencing your end users inappropriately, and/or setting your data up for failure. For example, statistics indicate that American survey respondents will often allow a form default value to remain as-is more often than not, just to expedite the completion of the form. This means that they may not entirely agree with the selection they submit, as they may not have stopped to review the other choices, or to think about their response. In other cases, default values can provoke error, rather than prevent it. Imagine a gender field that defaults to "male". Since it is possible that a female filling out the form will entirely miss the field (especially since no data validation error will probably occur since it's no longer a 'blank' answer), I bet that some females will be recorded as males (and I would attribute that fault primarily to the form designer). However, if this form was for the YMCA, I might agree with the default, but only if it is possible that female instructors might fill out the form too (otherwise it's not needed at all). To clarify, I'm suggesting that you think of things like this carefully....I promise you'll be glad you did once you plug in Power BI and start analyzing the data being collected.

HEADS UP: We currently have a connector issue logged where the SharePoint default field value for choice lists is only recognized by PowerApps if it is the very first option in the list of choices on SharePoint. Because of this, if you want to use a default value for that SharePoint choice field within your PowerApp; make sure it's at the top of the choice list. You can see this setting in the screen shot below. (in that case, we set this default because 99% of the end users will be lodging in the "Seattle" area). In addition to this note, I will sometimes enter "Select One" at the top of the choice list whenever I want my PowerApp to default to a blank value....then I'll set a pre-submission data validation (like the OnChange one in this article) to trap that "Select One" value if left unchanged, and if I don't want blanks in my data. (We hope to have this SP connector issue resolved very soon, but I wanted to be transparent on it so you'd know -smiles.)



Remember that in all the cases listed above, you may also want to edit your OnSelect statement for your submit icon... especially if you want to actually 'prevent' something from being submitted incorrectly (messages don't prevent, they alert). For example, you could use an If function on your submit icon that checks that all the values are accurate, and does not attempt to submit before errors are corrected.

In closing, we all will want to include data validation with strong 'design empathy', in order to facilitate form data entry, and to protect data quality! I hope this helps you to achieve that ultimate goal. Please feel free to reach out to me with questions or comments, or even if you need help in planning your next app!

Thank you for your time today! We want your PowerApps engagement to be enjoyable and scalable with best practices like these. I'll be posting more tips for you later this month, so please come back and visit again!