Home » Patch

Tag: Patch

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.

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 of primary key being selected

The first argument of ‘Patch’ should be a collection

There’s an incredibly common – and infuriating – error message that I keep running into, so I’m making a note here so I don’t forget! If you see “The first argument of ‘Patch’ should be a collection”, and you’re stuck, I have a quick fix for you!

In PowerApps, a Patch command sends information to your database. So, for example, if your app is connected to a table called ‘[dbo].[FriendsList]’ and you want to change Monica’s phone number to 555-12345 (whether it’s blank at the moment or has an old number in there), you could add this code to the OnSelect function of a button:

Patch(‘[dbo].[FriendsList]’, First(Filter(‘[dbo].[FriendsList]’, Name = ‘Monica’)), {PhoneNo: “555-12345”})

In order, these commands:

  • Determine the ‘collection’ that a record is going into (in this case ‘[dbo].[FriendsList]’). A collection can be a database table, or a local collection built with the Collect() command, for example.
  • Find the First (assuming the only, but more of that another time) item in theĀ ‘[dbo].[FriendsList]’ database table where the ‘Name’ column has ‘Monica’ in it
  • Fill in the ‘PhoneNo’ column of the table with “555-12345”

The first argument of ‘Patch’ should be a collection

So we know that the first argument of this Patch is a collection. So why the error?!

If you swear you have this correct (and I’ve sworn a lot over this when making PowerApps), but you’re still getting the message “The first argument of ‘Patch’ should be a collection” head to your database. For example, if it’s a SQL Server database, you can open SQL Server Management Studio (SSMS) and find your table. Right click on the table name in the Object Explorer and select ‘Design’.

You need to make sure your table has an ID column with a primary key. Right click on the column where the little black arrow is, and select ‘Set Primary Key’.

Screenshot of primary key being selected
Set your primary key by right clicking on the column to the left

Next, make the column the ‘Identity’ column. Do this by going to the bottom of the screen and finding ‘Identity Specification’ and clicking the little black arrow next to it. Then to the right of ”(Is Identity)’ click on ‘No’, to bring down the drop-down menu. Now you can click on ‘Yes’. This means your ID number will increase by one automatically each time you add a new record.

Screenshot of Identity Column being created
Turn the Identity Specification of your column to ‘Yes’

These two measures make sure each of your records can be distinguished from the next one, and your error message will be no more! Why that has anything to do with collections being missing is, for now, beyond me.

Damn Microsoft for its idiotic error messages!