Bulk Patch all items from a gallery

One of the functions it seems difficult to get some decent guidance on is the bulk patch. I want to submit everything from a gallery back into a database, changing one or more fields for all the records shown.

It occured to me that PowerApps simplest app is one where you look at a gallery, tap on the record to edit, and all the details are available in a form. There’s a submit button at the bottom, on which the OnSelect property is Submit(MyForm). There’s also a OnSuccess property for the form itself, where you set actions (like a Notify() message) which happen when the data is successfully saved.

The scenario

But what if you have a situation where you need to edit a number of records all at the same time. Perhaps you have an update to the population data, and you just need to change one field in 10-20 records. Do you really want to go into each record separately, type your update, click save, get taken back to the record list, and do the same for each?

No, of course not.

There is a way to bulk patch, which is used through the ForAll command. The ForAll command lets you run a command on every item in a collection of some sort , like this:

ForAll(Gallery1.AllItems,
    Patch('[dbo].[table]', First(Filter('[dbo].[table]', ID = ThisItem.ID)),
)

This, in theory, runs the Patch command on every line in Gallery1. Except I’ve found it really hard to get this to work, as have others. And as usual the official documentation looks (to me at least) like they’re jumping into a more complicated example from the off).

How to Bulk Patch a gallery in PowerApps

If, like me, you’ve been unable to get this to work (no error messages, but no records are updated, for example) then the key is twofold:

  • include the ID (or other unique) field in the gallery. You can even set its Visible property to false if you don’t want it showing.
  • Use UpdateIf instead of Patch

Then the code to bulk patch is like this:

ForAll(Gallery1.AllItems,
    UpdateIf(
        '[dbo].[table]', ID = Value(ID-Field.Text),
        {Property1: Property1TextInput.Text, Property2: Property2TextInput.Text}
    )
);

Note: You will need to use the Value() around the ID field text, like I have, if the ID is a numberical value in the database, as it often is.

Other options to bulk patch data

This might come in useful in a few different situations, but I am usually combining it with one of these two things:

Edit multiple fields on the gallery screen

My example lets you edit fields on the gallery screen without going to the edit form. Just replace the gallery field Labels (e.g. Title, Subtitle, Body) with TextInputs. In my example I’ve called them Property1TextInput and Property2TextInput.

Just make sure you include them in your ForAll statement.

Checkboxes

Instead of editing the same field on all gallery items, you might want to set a single control (e.g. a calendar / date picker) and then update some of the records in the gallery with that date. Add a checkbox to the gallery so that it shows up next to each record, and use this kind of code:

ForAll(Filter(Gallery1.AllItems, Checkbox1.Value = true),
    UpdateIf(
        '[dbo].[table]', ID = Value(ID-Field.Text),
        {StartDate: StartDatePicker.SelectedDate}
    )
);

Text in bold is the code I’ve added. In this scenario you only need one data picker on the whole screen.

Conclusion

I hope this presents a clear way to bulk update items in a gallery in PowerApps. The documentation for this feels lacking so far.

In summary: use UpdateIf on a ForAll loop. You can use this in conjunction with editable fields in the gallery, and/or checkboxes with single input controls outside the gallery itself.

And of course, this can also be used with Collections.

Image: Guardians, by Scott Cunningham, released under a Creative Commons license on Behance.

Adding barcodes to PowerApps at the list item level

You can create a barcode to associate with every item in a Sharepoint list. You can also associate a barcode with every item in a Sharepoint folder. But you do two different things to get each feature to work.

Adding a barcode to Sharepoint list items

Just a short one today, to share something that it took me too long to find. In fact, this is one of those times when the wrong way looks so right, that you assume either it’s broken or you’re an idiot. In my base, the latter was true.

I can also blame the typical lack of quality tutorials, so hopefully this short guide will make things clear.

  1. Open the Sharepoint list that you want to add a barcode to.
  2. Click the cog in the top right and select ‘List settings’.
  3. In the second column, click on ‘Information management policy settings’.
  4. This is the bit I got tangled in. Click on the ‘Item’ content type (and not ‘Folder’)
  5. On this final screen, tick the box labelled ‘Enable Barcodes’. Then click OK at the bottom.
Find list settings in the menu
Sharepoint list settings – enable barcodes
Select ‘Information management policy settings’

If you’re not taken to another screen here, click on OK again until you are. I think there’s something iffy there, and this won’t work unless your ‘OK’ takes hold.

Add a Barcode column to a Sharepoint list

You can now go back to your list and click on ‘Add column’ at the right end of the column headers. Click ‘Show/hide columns’ and, finally, tick the ‘Barcode’ box on the right side of your screen.

All new items will have a barcode, and I’ve found that editing a line will generate a barcode, even if you don’t change anything.

Image: Landscape. http://kansallisgalleria.fi/E39.Actor_3834BD59-6179-489D-AB48-8DF3F4C4FDE4. Finnish National Gallery. Public Domain

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.

Power Apps Portals: ‘Forms’ and ‘Entity Forms’

I’m just starting out learning about Power Apps Portals, and I’ve remained stumped on much of its details! Partly this is because it’s a deep and wide topic, and I’ve been trying to use it as a quick-to-build website tool. It is not one of these. So this first Portals post is my understanding of the relationship between Entities, Forms, and Entity Forms.

The CDS and Entities

A brief run-down of definitions to get us started:

CDS / Common Data Service: A data store for business information (users, orders, compaints etc.). Underpins Power Apps Portals.

Entities: the CDS equivalent of database tables. Users, orders and complaints can be entities. Each entity, like a table, has fields such as Name, Date, Owner, Description etc. Entities can also have relationships with other entities. For example, the ‘Owner’ field might in fact contain a cross-reference to a ‘User’ entity.

Power Apps Portal: a website where you can show CDS data and entities to the outside worls, and allow permitted users to view and even edit them.

Entities and forms

But what really confused me is the relationship between entities, their forms, and the ‘entity forms’ you put on a Portal, so users can fill them in.

Turns out that a form under an entity is not an ‘entity form’. 😫

Here’s how I eventually broke it down:

Constructing Entities and Forms (left) vs posting Entity Forms on a Power Apps Portal (right)

Entity Forms in the CDS

Entities can be found under Data in the Power Apps Portal (http://make.powerapps.com). Here you can edit their fields, their relationships, and their forms. So ‘forms’ are created under an entity just like fields and relationships.

The ‘Address’ entity, with its single ‘Information’ form, and tabs to edit its Fields, Relationships etc. This is in the CDS, no Power Apps Portal in sight (yet).

Each form can show things like text entry boxes and checkboxes, each one corresponding to a field in that entity. So one form can show ‘basic’ fields, another could be location-related fields etc.

In turn, any form can be divided across tabs, in any way you like, for whatever reason.

Entity Forms in a Power Apps Portal

But when you want to insert a ‘form’ into a Portal, you get this (see image, left):

  • Name (i.e. which form do you want?): fine, I’ve built one under an entity
  • Entity: but… I thought this form was already under a specific entity?
  • Form layout: what is this?!

You might also find that the ‘Name’ dropdown is empty. What’s going on?

Creating an Entity Form (aka the middle bit)

Turns out you’ve not created an Entity Form yet. You’ve created a form in an entity but… no, I’m not even going to try working out any more ‘why’s…

An Entity Form is a combination of the form you’ve already created, wrapped up with extra options and settings. So it might be more helpful to see it like this:

Structure of an entity form, with an entity wrapped in options

See your form in there, below Entity? A different beast altogether to an ‘Entity Form’.

And it’s the Entity Form that you insert in your portal, not your Form-under-the-entity. Yikes.

The other thing it took me a while to understand is that you can construct an Entity Form in the Portal (like in the screenshot above), or you can construct it in the Portal Management App (which was confusing to me in its own way – a separate blog post beckons…). Both methods are linked, so changing one also changes the other. The Portal Management App just has more options for you to set.

Entity form options

As a crucial example of how to use the options, you can create two identical Entity Forms. One could have the Mode option of ‘insert’ – creating a new record – while another has a Mode of ‘edit’ – changing existing records. In fact, it seems you have to do this, but it’s early days for me at the moment.

Then you can use the two versions like this:

  1. Put an ‘edit’ Entity Form on one page, and the ‘insert’ Entity Form on another;
  2. Give some users access to the ‘edit’ page, without giving it to others.

Entity Forms vs Forms-in-an-Entity

This whole website is really for me to write down all those odd things about the Power Platform that I don’t find easy. But hopefully this particular blog post can help you untangle the behemoth of the CDS and Power Apps Portals.

If anything’s not clear (or if I’ve got this wrong!), please do leave a comment below!

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!