Home » Archives for November 2018

Month: November 2018

Dauerrotype landscape of Paris

Validate a field or form in PowerApps

A lot of people seem to be wondering how to validate a field in PowerApps. As usual, all the examples I’ve seen are either over-complicated or seem to be dodging the question about whether it’s even possible.

It’s possible! And so this post is going to be about collecting the ways in which a PowerApps field can be validated. It’s an incredibly important thing to check that your users are putting useful data into your apps. For one thing, if a user tries to put letters into a numerical field, the PowerApp is likely not to give useful feedback to the user by default.

You can try messing with error handling. Alternatively, nip that thing in the bud by validating fields while the user is right there.

Validate a field which should have numbers in

I’ll start with an easy one, which I’ve used myself already.

IsNumeric()

PowerApps has an IsNumeric() function which checks if some string of characters is only composed of numbers.

There are countless ways of using this to validate a numbers-only field, but one is to display an error message. The error message would likely be a Label, with its Visible property set to something like this:

If(IsNumeric(FieldToValidate.Text),false,true)

Which means that if the user types in something other than numbers into FieldToValidate the error message will appear. (To be specific: if the text in this Label is numeric, Visible is set to ‘false’. Otherwise, Visible is set to ‘true’)

You can go further, such as adding an ‘OR’ to the above If statement: if the user leaves the field blank, then the message is still hidden. We’d do that because, if the box is OK to be empty, we don’t want the error message to show.

You can adapt the IsNumeric() function to do other things. For example, if you have a Save button on your screen, you could use it on its DisplayMode property like this:

If(IsNumeric(FieldToValidate.Text),disabled,edit)

So the save button is disabled until the correct content is put in the box (or it’s empty). Pro tip: I’d still make sure an error message appeared as well. You should explain things to the user!

Image: Attributed: París Landscape. #agent_0b79a939-813b-040d-8c0d-dc168242cd1d. Colección Pau Maynés, Spain. CC BY.

Psalm manuscriupt drawing of a hedgehog

Rename a PowerApp: why is your app locked?

Microsoft really seem to have dropped the ball when you come to rename a PowerApp.

It’s a bizarre couple of hoops to jump through that don’t make sense, but if you want to rename a PowerApp it’s easy, even if it’s not intuitive.

The wrong (but obvious) way to rename a PowerApp

The problem with the obvious way to rename a PowerApp (File -> App settings -> Edit app name) is that you’ll end up with a message telling you your app is ‘locked’. And locked by your good self too!

Screenshot showing error message in PowerApps
Error message showing that this PowerApp is locked by me

The only way (that’s impossible at first glance)

Here’s the silly part. Why’s the app locked? Because you’re using it! But of course you’re using it, because you have to in order to get to the renaming screen… 🙁

The infuriating solution to this is that you must close the PowerApp in order to rename it. So: once you’ve got to the point of changing the name (I’m adding ‘test’ to the end of the existing name in the screenshot above), go to the PowerApp, which is still open in another tab or window.

Then click on ‘Close’ at the bottom of the File menu. It’s on the left.

Once you’ve closed the PowerApp, you might as well close the whole tab, as you won’t be needing it again.

Go back to the tab where you’ve been trying to rename the thing, and try again.

Success?!

I hope so. Then you just need to close the right hand sidebar by clicking on the X in the top right. Do that twice, because of course you should.

Now you’re at a screen where you’ll be able to re-open your PowerApp by clicking on ‘Edit’ next to its name.

Screenshot showing steps for renaming a PowerApp
Rename your app by following these two steps (nicely reversed to keep things as confusing as PowerApps itself)

So that’s it. To rename a PowerApp you need to open its Settings from within the open app. But you can’t rename an open app because it’s locked!

Just thank Microsoft that they made the settings open in a new tab so you could close your PowerApp easily!

Image: Hedgehog from BL Royal 2 B VII, f. 97v. The British Library. Public Domain.

Patch a NULL value

Patching a NULL value in PowerApps seems to be a fraught business. There are countless people wanting to patch NULL values, and yet Microsoft’s own documentation falls short.

However, it’s quite simple to patch a NULL value – i.e. insert a NULL value into a data source – with a couple of simple steps.

Insert a NULL value into PowerApps data source

The first thing to do is to go into the File menu, where you’ll find all the PowerApps’ settings.

Screenshot of PowerApps settings screen
PowerApps settings screen

Then click or tap on ‘Advanced settings’ (you’ll see it highlighted in the screenshot above, as my mouse is hovering over it).

Then, you’ll see the list of Advanced settings. Scroll down to ‘Formula-level error management’.

Screenshot of PowerApps Advanced settings screen
PowerApps Advanced settings
Screenshot of some of the advanced settings in a PowerApp
Scroll down to here. Enable the error management by clicking or tapping on the ‘switch’.

I don’t really know why you need to turn on error handling, because Microsoft’s explanation was a bit flaky and I’m still learning. But you need to do this first.

Do that Patch

Now when you add a Patch command to button or other element on the screen, you can set it to patch an empty string by using double quotes: “”. This has worked for me on columns in my SQL Server database whatever the format. I’ve even been able to patch NULL values into date columns, which seemed particularly tricky.

So now, when your user has mistakenly added some data in, but wants to clear a field, you can let them do that. For example, in my app a person might record when they finished a task. Then, if they realise that they ‘finished’ the wrong task, they’d need to keep the task record, but empty the ‘TaskFinished’ column.

You can also, if you’re using an If statement, simply leave the ‘false’ case blank, as in:

If(SomeVariable = 20, Patch(...) )

Notice there’s no ‘false’ code to run. We just don’t Patch anything – or rather we Patch ‘nothing’.

You can code a normal NULL Patch like this:

Patch('[dbo].[TaskList]', First(Filter('[dbo].[TaskList]',
   TaskNo = Dropdown1.Selected.Value)),
   {
      Notes: TextInput1.Text,
      DateFinished: ""
   }
)

That works for Numeric fields. For text fields you might be better off using the Blank() function to replace that “”. So it would look like this:

Patch('[dbo].[TaskList]', First(Filter('[dbo].[TaskList]',
   TaskNo = Dropdown1.Selected.Value)),
   {
      Notes: TextInput1.Text,
      DateFinished: Blank()
   }
)
Screenshot showing a dropdown being populated in PowerApps

The column “Value” of the table wasn’t found

I’ve found an odd error message in PowerApps which I can’t fathom. I can stop it happening, and so get the app working, but it looks like a bug.

When you populate a drop-down menu, you can hard code the items in it like this:

["First item","Second item","Third item"]

Alternatively, you can populate a dropdown using items in a column in your data source. Simply set the Items attribute of the Dropdown to the table name, e.g. ‘[dbo].[UserRoles]’ for a hypothetical tool where a user must state what kind they are from a predetermined list.

In the right hand sidebar, under ‘Advanced’, find the Data section and you’ll see something like this:

Screenshot showing a dropdown being populated in PowerApps
Populating a dropdown from a database table

Here the Value attribute shows all the columns in the UserRoles table. I have just two, one of which is a unique ID.

The column “Value” of the table wasn’t found

I kept getting this error message when I ran the app in Android and iOS:

The column "Value" of the table wasn't found

It took me a while to realise the link between this ‘Value’ and the Value attribute under Data!

Of course, Value isn’t a column in this table, nor will it likely be in any other table I use for a Dropdown (it’s a bit vague).

Changing the name of the column ‘UserRoles’ in my database to Value, it started showing the same message but complaining that UserRoles was now missing. Of course it was! And I certainly didn’t want to rename my column as ‘Value’ permanently.

What worked was to add an empty column named ‘Value’ in my database, allow nulls, and leave it like that. Well, it worked!

Not an ideal situation but I can’t see this as anything other than a bug. It always worked when I ran the app on the laptop I was building it on (a Surface Pro in case you’re interested in testing), but not the mobile apps.

If you’ve got any insight into this, please do let me know!