It’s been awhile since I’ve posted but I had to share this info.

When working with SharePoint lists, we need to do our best to standardize data. One instance of this is if you are using an “Email” column as the primary key to match data between two different lists. To ensure that your users are entering the same data we need to add some extra function when submitting this data.

Normally when I am submitting text to a SharePoint list I would enter in something like this in my Patch statement:

Email: EmailColumn.Text

This is fine for the most part but for something like an email column you could potentially end up with entries like myEmail@email.com, MYEMAIL@EMAIL.COM, MyEmail@Email.Com, etc. As you can see these are all the same email address but PowerApps takes these entries literally so basically these are all different email address. Another issue I’ve encountered is that sometimes users will copy email address from other sources and will unknowingly copy a leading or trailing space. Upon troubleshooting you will not notice this until you dive into the list and actually open the line item itself and highlight the entry. This is an absolute pain in the butt and has caused me more grief than it should.

To prevent any of these scenarios, we need to add on to the original formula. It’s best to tackle one thing at a time just to make sure that portion of the function is working properly. Let’s start with make the entered text all lowercase. It’s actually pretty simple to do this. All you need to do is use the “Lower” function.

Email: Lower(EmailColumn.Text)

More info on Lower can be found by going to the following url:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-lower-upper-proper

This will convert any entered text to all lowercase:
MyEmail@Email.Com –> myemail@email.com

That’s step one and is a major win if you’ve ever had the pleasure of dealing with this issue. Now that we have the text portion taken care of we need to focus on removing any spaces from the text. To do this we will be taking advantage of the “Trim” function. Simply add this inline after the “Lower” function. Make sure that you are closing off your ().

Email: Lower(Trim(EmailColumn.Text))

More info on Trim can be found by going to the following url:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-trim

Make note that if you are using a collection in your app (highly recommended), the initial entry added to the “Email” field will not change until that collection is refreshed.

This issue has caused me more trouble than is needed. If we make it a practice to include additional functionality like this in our apps we can cut down on unnecessary headaches for not just ourselves but also for our customers.

Have a great day and remember to stay Salty!