Hi PowerApps team,
Please set always a default to each column of a collection!
Oke, let me try to explain. I will first scetch the situation, then give the issue I'm experiencing and a last the current workaround needed.
Situation
Let's say we have a collection for employees with multiple columns:
[ID, FirstName, LastName, DisplayName, RoleID, RoleName]
I bring in the items with Flow(), so on app start I have items in my collection. Now I want to be able to update and create items in my collection.
For Update I use a Variable Record (I set this by selecting the record in a gallery with
UpdateContext({MySelectedItem:ThisItem})
This variable I use for my form. Till so far no problems.
Now I make changes and want to save them in my Collection and my DataSource. For that I use an other collection _EmployeePost. What I want to do is
Set(_EmployeePostID,MySelectedItem.ID) ;Collect(_EmployeePost, MySelectedItem) ;Patch(_EmployeePost, LookUp(_EmployeePost, ID = _EmployeePostID) ,{FirstName: "New FirstName"} ,{LastName: "New LastName"} ,{DisplayName: "New DisplayName"} ,{Role: "New Role"} )
For a new record I would like to do
Set(_EmployeePostID,Max(_Employee, ID) + 1) ;Collect(_EmployeePost, {ID:_EmployeePostID}) ;Patch(_EmployeePost, LookUp(_EmployeePost, ID = _EmployeePostID) ,{FirstName: "New FirstName"} ,{LastName: "New LastName"} ,{DisplayName: "New DisplayName"} ,{Role: "New Role"} )
Issue
The issue is that this won't work. This is because with an update if one field is blank()/Null this field is not set and I can't update the value here. On a new record you can't update all the fields besides the ID.
Workaround
What I have to do now is:
// Update Set(_EmployeePostID,MySelectedItem.ID) ;Collect(_EmployeePost , {ID: If(IsBlank(MySelectedItem.ID),"", MySelectedItem.ID)} ,{FirstName: If(IsBlank(MySelectedItem.FirstName),"", MySelectedItem.FirstName)} ,{LastName: If(IsBlank(MySelectedItem.LastName),"", MySelectedItem.LastName)} ,{DisplayName: If(IsBlank(MySelectedItem.DisplayName),"", MySelectedItem.DisplayName)} ,{Role: If(IsBlank(MySelectedItem.Role),"", MySelectedItem.Role)} ) ;Patch(_EmployeePost, LookUp(_EmployeePost, ID = _EmployeePostID) ,{FirstName: "New FirstName"} ,{LastName: "New LastName"} ,{DisplayName: "New DisplayName"} ,{Role: "New Role"} ) // Insert Set(_EmployeePostID,Max(_Employee, ID) + 1) ;Collect(_EmployeePost , {ID:_EmployeePostID} ,{FirstName: ""} ,{LastName: ""} ,{DisplayName: ""} ,{Role: ""} ) ;Patch(_EmployeePost, LookUp(_EmployeePost, ID = _EmployeePostID) ,{FirstName: "New FirstName"} ,{LastName: "New LastName"} ,{DisplayName: "New DisplayName"} ,{Role: "New Role"} )
You see the quantity of script and we only have a simple collection here. I hope you can imagine the time it takes to build this with a langer collection.
Hope you can fix this.
Paul