Home » collections

Category: collections

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.

Screenshot of camera tool being selected in PowerApps

Empty columns in a PowerApps Collection

A Collection in PowerApps is a temporary table of data, that only exists in your app, and only while its open. When you use Collect you usually create all the data as you go. But you might need to leave some empty columns.

You can use the data in a Collection in the same way you might use an Excel spreadsheet, a SharePoint list or a SQL Server table: you can create Galleries, or Tables to show the data, or use the data to do calculations. Leaving empty columns means you can take your user back to add that data later.

Creating a Collection in PowerApps

You create a Collection using this kind of code:

Collect(CollectionName, {ColumnName1: Row1a, ColumnName2: Row1b}, {ColumnName1: Row2a, ColumnName2: Row2b})

This creates a Collection which looks like this kind of table:

ColumnName1 ColumnName2
Row1a Row1b
Row2a Row2b

Creating a Collection with empty columns

But I needed to create an app with some empty columns.

It would be a camera app, which lets you take a lot of photos quickly (by tapping on the Camera) and then displays those in a strip of small images along the bottom of the screen. You’re then taken to another screen where you can fill in some details (such as description and name) for each photo.

That second step involves creating the Name and Description columns in my Collection first, but leaving them empty.

If my Collection was to be called ‘Photos’ then the code would look like this:

Collect(Photos, {Image: Camera1.Photo, Name: "", Description: ""})

As simple as that: put all blank fields as quote marks. (‘Camera1.Photo’ is the photo that the camera takes when you tap or click it.)

Now we end up with a Collection called Photos like this:

Image Name Description
<photograph>
<photograph>

So that when I add my Gallery or Form on another page, I just make its Items attribute ‘Photos’. The app will create three fields in total, even though they have no data in them yet.