Every good programming language has many ways to do the same task, from all the different loops to manipulating arrays, there is no one version of the truth. Admittedly they all have the features and strengths, but they overlap so much that they become interchangeable. And though in one case one solution might be optimum, developer style will always carry more.
The Power Platform is pushing Power FX as its own code language, and to keep in step it has multiple ways of doing the same thing. The most obvious is dealing with data sources and collections (arrays). As with most low code developers, I learned from doing not from training, so was surprised to learn there was not just Patch to update data sources. So I thought I would dive into the different ways and find the best.
Below are all the different ways to modify arrays, there are actually 3 different arrays in Power Apps, datasources, collections and variables. That's right, you can save an array in a variable, why? I have no idea.
| Formula | Description | Scope |
|---|---|---|
| Patch | Modify / Create | Data/Coll |
| Update | Replace (like PUT) | Data/Coll |
| UpdateIf | Modify by condition | Data/Coll |
| SubmitForm | Modify / Create | Data/Coll |
| Collect | Create | All |
| ClearCollect | Clear & Create | Coll/Var |
| Remove | Remove record | Data/Coll |
| RemoveIf | Remove by condition | Data/Coll |
| table | Create | Var |
Note that if you Collect or ClearCollect a var it works but also creates an empty collection with same name
So as you can see, they all have their own features and reasons for existing, but there is a lot of overlap, especially for the main use cases:
1. Modify Record
So if you wanted to update a record in either a datasource or collection (it isn't possible to update variable) you have 4 options.
Patch(dummyData,
LookUp(dummyData,ID=1)
,
{Title:"Num1",number:1}
)
UpdateIf(dummyData,{Title:"Num1",number:1})
Update(dummyData,
LookUp(dummyData,ID=1)
,
{Title:"Num1",number:1}
,
first
)
SubmitForm(dummyDataForm)
The most simple way has to be SubmitForm (that's why it is pushed in templates), but for me the limitations of forms layouts make it not suitable for most cases (especially now as we have error handling outside of forms). So lets ignore it and look at the rest.
As you can see, Update and Patch are almost the same, with the difference the same difference between PATCH and PUT (Update replaces the record, so all fields need to be included, else they will be null). So I would say Patch supersedes Update in most cases.
But then there is UpdateIf, which doesn't require a record (saving a lookup/filter). So to me it is the most simple to use, as long as you have a GUID field you can use (unless you want to do bulk updates to same condition, which UpdateIf for is great for).
Except that's not exactly true, see if you have a GUID you can use Patch without a lookup, as we don't need a full record to identify it. Check this out:
Patch(dummyData,{ID:1},{Title:"Num1",number:1})
works too, so we don't need that lookup.
Bulk updates
Patch can also do bulk updates through a collection. So if I had pulled a datasource to a collection (colData in below example), updated the collection, then patched the datasource Patch is the way to go
Patch(dataSource,ShowColumns(colData,"ID","Title"))
vs
ForAll(colData,
UpdateIf(dataSource,
ID=ThisRecord.ID
,
{Title:ThisRecord.Title,number:ThisRecord.number}
)
)
I use ShowColumns because we cant Patch the readOnly fields we get from SharePoint Lists, though would be better to move that to the Collect.
Patch can also mix and match, so if you have a collection or Table with Blank GUID, it will create a record for the record, but modify the other records
colData=
{ID:Blank(),number:34,Title:"Num34"}
,
{ID:1, number:1,Title:"Num1.1"}
Patch(dummyData,colData)
Update can bulk, but only if the matches is set to All and there is no GUID in the array. UpdateIf will if there are multiple matches to the condition.
So each have their own bulk use, but Patch is the most useful.
2. Create Record
To create a record we have 3 main ways (ClearCollect will be represented as Collect):
Collect(dummyData,{Title:"Num1",number:1})
Patch(dummyData,
Defaults(dummyData)
,
{Title:"Num1",number:1}
)
Set(varDummyData,
Table({Title:"Num1",number:1},{Title:"Num2",number:2})
)
I'm still at a loss why anyone would store an array in a variable, it can't be modified, record deleted and adding causes bugs. So I'm going to ignore it too.
So Patch requires the Defaults(dummyData) to show its a create instead of modify, giving Collect the advantage in simplicity. So win for Collect, or is it?
Did you ever wonder what the point of the Table function was, it seems it is only useful for saving arrays in variables. But used with Patch it can incredibly useful, using Table we can simplify Patch, by creating a single row array
Patch(dummyData,Table({Title:"Num1",number:1}))
So we can drop the Defaults(dummyData) and make it a lot simpler. Although Collect is probably best, Patch is very close, and it has the advantage of not creating a Collection if it doesn't exist, a kind of explicit check to make sure you don't accidentally create a new collection.
Bulk updates
Shown by the Table function, you can Patch bulk create data (as above you can even mix and match with modify).
Patch(dummyData,
Table(
{Title:"Num1",number:1}
,
{Title:"Num2",number:2}
)
)
Creates 2 new records (you can also change to another collection instead of using table, just make sure it doesn't have a GUID field or at least Blank GUID).
Patch(dummyData,
Table(
{ID:Blank(),Title:"Num1",number:1}
,
{ID:Blank(),Title:"Num2",number:2}
)
)
Collect by its essence is bulk, as it requires 2 arrays, so it is again simpler, but its interesting to see Patch is very similar.
3. Delete Record
Good news, removing/deleting record is a lot more simple, with just options, Remove and RemoveIf. Which kind of mirror Update and UpdateIf.
Remove(dummyData,LookUp(dummyData,ID=1))
and
RemoveIf(dummyData,ID=1)
So again RemoveIf looks like the best option, but you can use the Patch trick to make Remove a little simpler:
Remove(dummyData,{ID:1})
Also I would say Remove is useful with Gallerys/DataTables, as you can use the Selected record for the record
Remove(dummyData,Gallery.Selected)
Bulk updates
Both can bulk remove, by using Table or another collection for Remove:
Remove(dummyData,Table({ID:1},{ID:2}))
and by all the values that match the condition. Though as with Update, the ability to selectively bulk is more useful.
So now looking back it feels like there really isn't a best way, just a right way for you. From a performance side they are all pretty much equal and even the length of the code is marginal once optimised.
For me I've got into the habit of Collect for collections, and Patch for all modify and create in data sources. Remove for everything. Though I think the strengths of the Ifs (Update and Remove) mean I will look at using them more in the future.
But as always, that's just my way, and I want to be careful I don't make my code hard to follow with inconsistencies.
Top comments (4)
I use variables for arrays, if the arrays are readonly. This is great for lookup tables. It's great to have a restricted version of array variables to reduce bugs.
I generally try to avoid collections if I'm not doing CRUD operations on the data, which I find to be a lot more common than I thought at first.
If I realise that an array needs the other CRUD operations I just simply update it to a collection.
Fair point, I just like consistency of collections for array, makes development easier. I've never had an issue with the need for consts, but might make sense in large complex apps
Speakimg of consts, it also makes it easier to transition to named formulas for better performance.
If only named formula tables were not so buggy π«
That's a great point, as there is no collections in named formulas it would be worth it for performance improvements (like you said when not so buggy). Might have to update the blog now π